Brent, On Tue, May 7, 2019 at 12:42 PM Brent Wood <pcr...@yahoo.com> wrote:
> I have not used SS for spatial data, but I don't have a Postgres database > without Postgis installed. The OSGEO ecosystem and synergies with other > FOSS GIS tools is fantastic. > > And it does not stop with the Postgis extension. For time series data > (anything from fleet management to sensor data) Postgres has the (new) > TimescaleDB extension. I ran this very effectively with a 600,000,000 > record database of sensor readings from a research vessel - on a $400 > laptop (with an SSD) for testing/prototyping. The sensor data was stored in > Timescaledb Hypertables & the location data in Postgis geometry columns in > those tables. Significantly better performance than native Postgres. > Excellent information with impressive numbers. > > Also consider language support for database functions... pl/R supports > some very nice capabilities, especially supporting websites. Instead if > running a Postgres query to return the data to plot via the web page, or > storing static plots in your CMS that need updating when you get new data, > you can use Postgres functions in pl/R to render the plot of the data in a > file, and return the name of the file. The web site does no rendering, just > invokes the SQL & displays the file that is returned. So the DB can return > the data and/or the graphic. Back up your database & back up your > functions. This paradigm can work very effectively... > Is there any tutorial/example code for rendering pl/R images on a website? Cool feature > > Generally, the FOSS ecosystem around Postgres offers an incredible array > of tools and capabilities that I don't think any other db - FOSS or not - > can provide. I have had limited exposure to Oracle, SQL Server, Sybase, > Empress, Teradata, Netezza, DB2, Sqlite/Spatialite, Interbase & Informix. > Of these, Postgres & Sqlite3 (which one depends on use cases) are all I use > these days. > Yep. agreed. Thanks, Igal > > > On Tuesday, May 7, 2019, 5:36:00 PM GMT+12, Tony Shelver < > tshel...@gmail.com> wrote: > > > I have to agree on the geospatial (GIS) features. > I converted from SQL Server to Postgresql for our extended tracking > database. The SS geospatial feature set doesn't seem nearly as robust or > complete or perfoirmant as that supplied by PostGIS. > The PostGIS ecosystem of open source / 3rd party tools is also far bigger, > for anything to do with mapping. Openstreetmaps.org stores their world > dataset on Postgresql / PostGIS, and there a ton of mostly open > source-based tools and organizations that work with it or any other PostGIS > data to provide a complete GIS solution. > > My first sS implementation had me backing out of storing geographic points > in the relevant SQL Server datatype as the performance hit during loading > was just too big. Doing the same thing in Postgresql / PostGIS is nardly > noticeable. > > Another feature in Postgres is that you are not restricted to just plpgsql > as an internal procedural language. > > I am not an expert, but it also seems far easier to create, install and > work with major extensions to Postgresql than SQL Server. I found > installing the GIS featureset in SS to be a bit of a pain back oin the > day.. > > On Tue, 7 May 2019 at 00:53, Michel Pelletier <pelletier.mic...@gmail.com> > wrote: > > On Mon, May 6, 2019 at 2:49 PM Adam Brusselback <adambrusselb...@gmail.com> > wrote: > > I think the main "gotcha" when I moved from SQL Server to Postgres was I > didn't even realize the amount of in-line t-sql I would use to just get > stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate > this. DO blocks cannot return resultsets, so short of creating a function > and dropping it, it's not possible to get the same workflow. > > > Just ruminating here, and this has probably already been discussed in the > past, but I've always wanted something like a 'SELECT DO [LANGUAGE ...] > RETURNS rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't > had any serious problem with creating/dropping functions like you mentioned. > > -Michel > > > The lack of GUI tooling was also a huge "whoa" moment for me, which I > still grapple with. > >
