Postgresql vs sql server manual#

Postgresql vs sql server manual#

The Postgis reference manual is also excellent. The Postgis irc channel and mailing list are excellent resources. Rapid pace of development - raster, topology, 3D support being recent examples of this.ģ). No data corruption in 5 years and we now have around 25 Postgres/GIS boxes on centos virtual machines, under varying degrees of load.Ģ). The key points of this experience have been.ġ). Given the issues we had with MySQL, we ultimately converted to Postgis. On the downside, data corruption was an issue, and by being forced to use MyISAM tables you are giving up a lot of the benefits of an RDBMS. Overall, to be fair to MySQL it supported our website, WMS and general spatial processing for several years, and was easy to set up. Since acquisistion by Oracle, spatial has really been put on hold. No support for conversion from one spatial reference system to another.Ħ). Being able to do combined vector-raster analysis within a db is very useful GIS functionality.ĥ). Select ST_Union(geom1, const_geom) from some_table, ie, one of the geometries is a hard-coded constant geometry is a bit limiting in comparison.Ĥ). There is still no ST_Union function that acts on an entire geometry field, one of the queries I run most often, ie, you can't write: select attribute, ST_Union(geom) from some_table group by some_attribute The spatial functionality is extremely limited compared to both Postgis and SQL Server spatial. EDIT: In the upcoming 5.7.5 release InnoDB will finally support indexes on spatial columns, meaning that ACID, foreign keys and spatial indexes will finally be available in the same engine.ģ). See My experience from going to conferences was that spatial was very much an afterthought - we've implemented replication, partitioning, etc, but it doesn't work with spatial. InnoDB table type does now support spatial types, but not indexes on them, which given the typical sizes of spatial data sets, isn't terribly useful. This means if you want transaction support you are out of luck. Until recently only MyISAM tables supported the spatial data type. Over the course of 5 years, we had several database corruptions issues, which could only be fixed by running myismachk on the index file, a process than can take well over 24 hours on a 450 million row table.Ģ). I have subsequently been involved with migrating our spatial data to Postgis and our corporate data (with spatial elements) to SQL Server. I subsequently was involved in MySQL, I attended/spoke at a couple of conferences and was heavily involved in the beta testing of the more GIS-compliant functions in MySQL that was finally released with version 5.5. I decided to try out MySQL and Postgis, at the time there was no spatial in SQL Server and we had a small startup atmosphere, so MySQL seemed a good fit.

Ten years ago I was tasked with putting a largish - 450 million spatial objects - dataset from GML to a spatial database. I have worked with all three databases and done migrations between them, so hopefully I can still add something to an old post.