So I'm recently starting at a new company and have a lot of ArcGIS users who seem really keen on going forwards with a PostGIS instance to serve some data to our customers. While I don't have an issue with this, we are a 95% SQL Server and 5% Oracle shop. Our current internal GIS runs off SQL Server and I have yet to hear any complaints.
I know SQL Server has a lot of improved spatial / geometric capabilities as of 2012, but are there any killer features in PostGIS that are worth breaking into the new platform for? I've tried to research it but can't find anything truly in depth or that isn't completely bias.
I want to give them the best tools to get their work done, but also have to weigh the fact that I'll be learning Postgres/GIS from the start and that's an entire journey in and of itself.
Best Answer
I've worked with both Postgres and SQL Server. I found Postgres to be superior in GIS functionality. And while I'm going to briefly detail my findings below, I'd suggest this: Give yourself a brief but reasonable time period to review the unfamiliar solution over the one you know, with specific goals in mind. For example, maybe a 2 week time period to install and learn some specific functionality that is currently in use. If you find that you are stuck or lack functionality within that time period, then you know it's not for you. It's a investment in research that broadens your view and helps you realize you may have been missing something that you were unaware of before, or simply confirm your current course is the right now.
As far as the database goes, I found Postgres to have a shorter, and more shallow, learning curve. The documentation is just incredible. SQL Server does have quite a bit of documentation, but I find a lot of it hard to read, with not enough examples and tutorials.
PostGIS vs SQL Server Spatial is similar to the above regarding documentation, but PostGIS beats the pants off SQL Server Spatial in functionality. For example, Google Maps, and to a lesser degree Bing Maps, has recently added full geoJSON support to their maps API. Well, PostGIS can easily return a geoJSON result directly from a database query using ST_AsGeoJSON(). This geoJSON result can then be passed directly to whatever can understand geoJSON. SQL Server requires you to use additional library and processing, or use ogr2ogr. In addition PostGIS has over 300 functions available for data conversion into and out of the database, compared to SQL Server which has around 70-100.