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.
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...
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.
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.
