0

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?

Improve this question
7
  • 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, 2012 at 17:10
  • 2
    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, 2012 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, 2012 at 21:44
  • @whuber Thanks for the link. Regarding CPU, that is what I had anticipated.
    – ThinkingMonkey
    Jul 19, 2012 at 21:49
  • 4
    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, 2012 at 10:35

1 Answer 1

Reset to default
1

Alternatively to (partially) porting data to PostgreSQL/PostGIS, you can now use Foreign data wrappers (FDW).

From https://wiki.postgresql.org/wiki/Foreign_data_wrappers

In 2003, a new specification called SQL/MED ("SQL Management of External Data") was added to the SQL standard. It is a standardized way of handling access to remote objects from SQL databases. In 2011, PostgreSQL 9.1 was released with read-only support of this standard, and in 2013 write support was added with PostgreSQL 9.3.

With FDW, from PostgreSQL, you can read tables from MySQL, create the necessary geometries using PostGIS functions and run the available formulas. then, you can push back the result to MySQL.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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