9

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
preyz
  • 3,029
  • 5
  • 29
  • 36

3 Answers3

17

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.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • 1
    Thanks a lot for your feedback! Could you please clarify "MySQL is a joke when you need GIS queries"? – preyz Jul 22 '12 at 01:43
  • @preyz for GIS there is a big big list of missing features for MySQL. It doesn't have a projection engine, only [does a bounding box rather than a point-in-polygon analysis](http://stackoverflow.com/questions/1078386/see-if-lat-long-falls-within-a-polygon-using-mysql) (unless you use the latest version), there are fewer GIS tools to link to MySQL, etc. PostGIS is much more mature with respect to GIS than anything MySQL Spatial has to offer. – Mike T Jul 22 '12 at 23:10
1

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

Kurnia Hayati
  • 11
  • 1
-1

From a purely PHP perspective, if you use PDO then - the constructor aside - your code can be pretty much database agnostic.

Nev Stokes
  • 9,051
  • 5
  • 42
  • 44
  • 2
    But than you loose all benefits of a specific database. Performance and functionality will suffer. – Frank Heikens Jul 21 '12 at 21:15
  • 1
    PDO only gives structure for working with main SQL syntax and helps to escape and add correct quotes to expressions. It doesn't deny using specific functions and it's not a purpose to keep only essential SQL opportunities. A serious project cannot be based on universal SQL. But if you develop an open source product (e.g. extension for a framework) you need to avoid platform-specific functions. There's no problem in both approaches. By the way, I made several real migrations between MySQL, Postgres, SQL Server. It's OK on some start stages. – Alexander Palamarchuk Jul 24 '12 at 20:44