For a project where most queries on the database will include requirements like “within an polygon” I have come to think about choosing PostgreSQL with PostGIS instead of MySQL, which I have previously mainly used. Mainly because PostgreSQL has more GIS functions. However, I am uncertain how to evaluate this, and also can’t really see the difference from a PHP developer perspective.
- Is PostgreSQL support integrated just as well with PHP as MySQL is?
- Is writing PostgreSQL queries (SQL) very similar to MySQL?
- Are there any caveats to consider when choosing PostgreSQL instead of MySQL?
- Is PostgreSQL per definition the better solution when using GIS data?
Hope you guys can help me clarify some of this.
Advertisement
Answer
Is PostgreSQL support integrated just as well with PHP as MySQL is?
Yes. And maybe even better, more PHP functions available to make things very simple.
Is writing PostgreSQL queries (SQL) very similar to MySQL?
Yes, it’s SQL. But you do have more advanced functionality available, like Oracle and SQL Server.
Are there any caveats to consider when choosing PostgreSQL instead of MySQL?
PostgreSQL is not yet widespread on cheap hosting providers. But it is available.
Is PostgreSQL per definition the better solution when using GIS data?
MySQL is a joke when you need GIS queries and PostGIS (functions and datatypes in PostgreSQL) is one of the best GIS databases available in the market.
Edit: Check these two comparisons:
PostGIS has moved on to version 2.0 and PostgreSQL to version 9.1, with 9.2 upcoming.
MySQL has just a few GIS functions and lacks performance, it’s not a serious option for GIS.