Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I currently am using MySQL for all of my relational data. I have hit a crossroads with geo calculations. Since, MySQL does not have good support for spatial functions, I am contemplating on using Postgres's PostGIS extention.

But, for this I will have top run 2 DB servers (MySQL and Postgres) as I do not want to move away from InnoDb(for all the goods that it offers).

  • Does implementing the functions cause any performance issues(lag)?
  • Is the trouble worth it or should I move ahead with PostGIS?
  • Is moving the whole DB from MySQL to Postgres any good (Although I am not exactly considering this)?


  • Also, is implementing Haversine or Vincenty better? I do understand that Vincenty formula provides much better accuracy. But is there too much cost on CPU?

share|improve this question
2  
What features in InnoDb do you like that you find lacking in PostgreSQL? Most people choos InnoDb because of the transactional support, but PostgreSQL has that too and you have spatial geodetic indexes with PostGIS which makes things much faster. The only issue I see with porting the whole thing is having to rewrite some application logic though if you have a database abstraction layer, that is probably minimal. –  LR1234567 Jul 19 '12 at 17:10
1  
The last question is answered in terms of accuracy at gis.stackexchange.com/q/25494. In terms of CPU time, the Vincenty formula is roughly an order of magnitude slower. Whether that matters or not depends on the system, its loads, etc. –  whuber Jul 19 '12 at 18:42
    
@LR1234567 Went through the features of PostgreSQL and I agree that it offers equal amount of features. But at the stage my project is in, I would not like to switch to another DB, learn to use it/tools associated with it etc.But, what's making me rethink this is the geodetic spatial indexing which is not yet available for InnoDb.I do have a database abstraction layer and as you say, the application logic rewriting part is not much of a problem. I still have to give this some thought. –  ThinkingMonkey Jul 19 '12 at 21:44
    
@whuber Thanks for the link. Regarding CPU, that is what I had anticipated. –  ThinkingMonkey Jul 19 '12 at 21:49
3  
I did a simple test as follows: in PostGIS I created a table of 1,000,000 points with random long, lat values, and made it spatial. Then I ran two queries using the built in ST_Distance_Sphere() and ST_Distance_Spheroid() from the first point to all the others. I ran these queries under "EXPLAIN ANALYZE". The ST_Distance_Sphere (haversine?) took 1.6 seconds. THe ST_Distance_Spheroid (vincenty?) took 3.7 secs. Maybe that will help you to weigh the cost of the distance calculations. –  Micha Jul 20 '12 at 10:35
show 1 more comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.