16

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.

Improve this question
  • 1
    If are serving up data to clients from it using ArcGIS For Server, then the only consideration would be performance. While it does have a larger range of spatial functionality, I don't think any of this would be a killer feature or likely to be required by ArcGIS. Unfortunately I have no benchmarks on performance. – MickyT Aug 28 '14 at 21:41
  • The age old mantra of use what you know certainly applies here. Switching to a different platform always seems like a great idea before embarking on the change. Not so much later, when you're 6 months into it and realize you have only just started to gain the necessary knowledge. Ever heard of opportunity cost? – Max Vernon♦ Aug 28 '14 at 22:42
  • 2
    No experience wiht ARC products , but when talking only databases. PostGIS is way more mature implementation of spatial database than MSSQL server, more examples , more free content. If you need to do something spatial related in db, PostGIS is has more options. PostGIS is free , MS SQL is not , spatial databases seem to have tendency to grow bigger than expected. So there is headaches from licensing etc... Of course Linux+PostGIS has it own problems if admins are more used to windows enviroment. – simplexio Aug 29 '14 at 9:13
  • While you might be able to use a tool to do everything sometimes there are better tools for the job that are more supported. If you don't know about something like GIS how can you provide MSSQL as a solution because it says it has those features and because you already use it? Every tool has some features but not every tool is as good or mature at those features as others. – adrian.coroian Oct 1 '20 at 18:12
22

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.

Improve this answer
  • As soon as you need polygons, use PostGIS - you will save yourselfs a lot of trouble. If you only need points, maybe SQL-Server is sufficient, but then you can just use two decimal columns as well (2 columns not recommended if you need to do distance calculations - use GeoPoint). GeoPoint not recommended if you use EntityFramwork/LINQ2SQL/AverageCrappyORM. – Quandary Feb 6 '19 at 10:32
3

Having worked with the GIS capabilities of both systems, I'd suggest the following:

  • If you are primarily looking to store and query geometries, their capabilities are pretty comparable.
  • If you want to perform raster operations, PostGIS is your only option without 3rd party plugins such as ArcGIS's ST_Raster. SQL Server will store an image, but forget about it if you want to do raster manipulation or queries.
  • Some would dispute this, but in general I find SQL Server to be faster and more consistent. If all other things are equal, for a production runtime, I prefer it.
  • PostgreSQL has a far richer and more elegant SQL dialect and a query processor less riddled with arbitrary restrictions. In terms of language power and elegance, there is no comparison, pgSQL makes TSQL look like child's scrawl. I've considered writing a book about how bad Transact SQL is as a means to learn good language design. :-)
Improve this answer
1

It seems to me that which db is better is not your primary concern here and instead you have two different considerations which cut against eachother, namely business knowledge vs customer desires. Ultimately that will be a business decision, not a technical decision.

Obviously there is opportunity cost as Max has noted in a comment. There is no way around that. If you are going the Postgres route, please consider getting some help, in the form of a good consulting agreement, a seasoned dba, or both.

If your users want PostGIS, that may be a net win however. How much more of your services will you sell by making the switch? Will it be worth it opportunity-cost-wise? Those are not decisions that are going to be made based on which db is better in your eyes or in terms of technical specs, but in terms of the learning curve and the marketing.

Improve this answer
  • Great insight on the choice at hand - thanks Chris. – LowlyDBA - John McCall Sep 2 '14 at 13:43
  • 1
    Which db is better is of primary concern here. SQL-server is not able to handle that size of data (planet.osm). Also, it misses a lot of features, which you actually need, if you are going to do more than academic research (vector tiles, geojson, etc). Also, it can't handle polygons that go from one side of the equator to the other (stupid), which is a concern if you need brazil, equador, colombia, drc, gabon, Kenya, Somalia, malaysia, indonesia, Singapore, Papua or the Indian, Pacific or Atlantic Ocean etc. Also, errors if the polygon has the wrong direction - instead of auto-convert... – Quandary Feb 6 '19 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.