Differences between PostgreSQL and MySQL for PHP developers

Question

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.

1. Is PostgreSQL support integrated just as well with PHP as MySQL is?
2. Is writing PostgreSQL queries (SQL) very similar to MySQL?
3. Are there any caveats to consider when choosing PostgreSQL instead of MySQL?
4. Is PostgreSQL per definition the better solution when using GIS data?

Hope you guys can help me clarify some of this.

Answer 1

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:

• SQL Server 2008/Oracle 11G/PostGIS
• SQL Server 2008/MySQL/PostGIS

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.

Answer 2

MySQL MySQL is one of the most popular and widely used database management systems. MySQL is an RDBMS (Relational Database Management System) database system that makes database administration easier and more flexible.

MySQL can support various features that developers need in developing their projects such as multithreaded, multi-user, and SQL Database. Not only that, MySQL and the PHP programming language are an integrated system, so developers can create databases using PHP syntax.

Following are some of the other features offered by MySQL.

Compliant with ANSI SQL standard Allows Log-based and trigger-based replication SSL Object-oriented and compatible with ANSI-SQL2008 Multi-layered design with Independent modules Offers built-in tools for query analysis and space analysis Can handle any amount of data, up to 50 million rows or more MySQL runs on many types of UNIX, as well as on other non-UNIX systems such as Windows and OS/2

PostgreSQL PostgreSQL is an object relational database management system (ORDBMS). PostgreSQL as an enterprise-class relational database system offers easy setup and deployment into existing systems. This DBMS offers support for SQL and NoSQL, and has a great community that can help developers troubleshoot problems using PostgreSQL.

Following are some of the features offered by PostgreSQL.

Runs on all major OS platforms owned by the developer. MVCC supports a large number of concurrent users Extensive indexing for high performance reporting Support for modern applications (XML and JSON) ANSI SQL support for movable skills/code Foreign key support for efficient data storage Join tables and views for flexible data retrieval Triggers/Saved Procedures for complex programs and transactions Replication for data backup and read scalability