PostgreSQL vs. MySQL: spatial feature comparison

Question

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping spatial polygons.

That being said, our database has many other components that include all the typical things you would find in your general relational database.

We are at the point in our project where we must choose which database solution to use.

All project members are more familiar with the implementation and administration of MySQL, yet all research suggests that PostgreSQL is the better solution - especially in regards to spatial data using postGIS.

We expect (hope) our application will experience a lot of action with a lot of concurrent users.

Does anyone with experience using MySQL as their RDBMS with a spatial data component have any long term advice / experiences?

Are there any disadvantages of using PostGIS with the exception of familiarity?

Answer 1

I can't speak to advantages/disadvantages vis-a-vis MySQL, but the PostGIS code is pretty widely regarded as one of the best (in terms of speed/functionality) and most mature (in terms of testing/real-world exposure) available.

By way of example, there was a talk at PGEast 2010 by some folks from the FAA on their converting their airport database (used by AeroNav and others to compile charts) to Postgres/PostGIS from Oracle.
The avationDB site is also built on top of Postgres (8.0).

If GIS-related queries are at the heart of what you're doing my suggestion would be to go with Postgres. It can certainly handle everything else you would normally do in a relational database as well.

---

In terms of making the switch from MySQL, the documentation behind Postgres is first-rate, and there's also a section of the Oostgres Wiki about switching from MySQL to Postgres.
The initial learning curve may be a bit steep and you may need to tweak your database and any stored procedures (if you've written them for MySQL already), but it is not an insurmountable task.

You should be able to pick up enough to make the switch in a couple of weeks time, and if you set up a development database you can probably be well versed in routine tasks within a month, and confident you know where to look in the manual for the not-so-routine ones.

Answer 2

Speaking of some very major things. Here is a list of things PostGIS supports that are totally absent in MySQL and MariaDB.

• SRID in calculations, give your points a different SRID and you'll get different values back. This is prop Aggregate functions: to the best of my knowledge MySQL offers no spatial aggregates functions

K nearest neighbor: Only PostGIS supports KNN. Find the nearest point to any point using just an index: no need to calculate the distance from all points!er. MySQL breaks spec and only checks that two values have the same SRID. PostGIS comes with a database of pro4j definitions to enable seamless SRID awareness. Setting an SRID and calling ST_Transform (a function MySQL lacks) will reproject your coordinates..

In MySQL, all computations are done assuming SRID 0, regardless of the actual SRID value. SRID 0 represents an infinite flat Cartesian plane with no units assigned to its axes. In the future, computations may use the specified SRID values. To ensure SRID 0 behavior, create geometry values using SRID 0. SRID 0 is the default for new geometry values if no SRID is specified.

• Rasters: there are a ton of features here from raster generation to extraction. You can generate heatmaps and the like.

• Geography, PostGIS supports an unprojected geography type that doesn't use Cartesian math at all. It has a whole slow of associated functions that operate on oblate sphereoids. MySQL conversely can not even creating a bounding box in a geographical SRS from two points.

• Topology, distinct from vector geometries, topo geoms store nodes and relations. Move a node, the edge moves too and you get a new face. This also forces edges to be directed which makes them ideal for routing. As a subpoint 100% of what PgRouting does is unavailable to MySQL -- so you just can't create a Google Maps or the like on top of it.

• Geocoding: there is geocoder exstension in the contrib directory that works off census data, and a loader to install that data.

• Address standardization: there is an extension that handles normalizing addresses for easy parsing, storage, and comparison.

• SQL-MM features, you simply won't find CIRCULARSTRING COMPOUNDCURVE CURVEPOLYGON MULTICURVE or MULTISURFACE in MySQL.

• n-d cords: PostGIS can support 3dm, 3dz, and 4d shapes and points MySQL simply can't

• MySQL only supports r-tree indexes. PostGIS supports r-tree (gist/gin) and BRIN (for large geometry tables)

• Aggregate functions: to the best of my knowledge MySQL offers no spatial aggregates functions

• K nearest neighbor: Only PostGIS supports KNN. Find the nearest point to any point using just an index: no need to calculate the distance from all points!

• Indexing. PostgreSQL allows you to store any data on your spatial index (which is a gist/gin index). For example, you can store the year (or other non-spatial data) and the geom on the same index. See btree_gin and btree_gist for more information on how to do this.

Also, there are probably 200 or so more functions supported by PostGIS.

In short, MySQL doesn't hold its own to PostGIS and it knows it. PostGIS is a beast. Just wanted to explain some of this stuff.

Answer 3

I totally agree with all the statements of the first answer, but sharing my own experience -I’ve made this on my country's National Roads Administration: production critic, high traffic site. I suggest a web app be feed by both MySQL and PostgreSQL/PostGIS.

For all the "typical" stuff, the web app works flawlessly with a MySQL based CMS. For all the spatial tasks, the same web app works -also flawlessly ;) with PostgreSQL/PostGIS grounded custom development. The first component was developed and is maintained- effortlessly with normal MySQL skills. The second component involved a bit more research effort at the beginning.

You don't have to force a costly entire implementation of typical stuff in the not-so-familiar PostgreSQL/PostGIS and you don't have to force a suboptimal implementation of the geospatial stuff in the MySQL neither. Let every player play where it can hit.