current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 4 down vote favorite

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.

share|improve this question
    
Yes for the two first questions. –  Samy Dindane Jul 21 '12 at 15:54

2 Answers 2

up vote 9 down vote accepted

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.

share|improve this answer
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 1: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 (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
up vote 0 down vote

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

share|improve this answer
1  
But than you loose all benefits of a specific database. Performance and functionality will suffer. –  Frank Heikens Jul 21 '12 at 21:15
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.