[GIS] Implementing custom (Vincenty/Haversine formula) functions for MySQL vs Using PostGIS

distanceMySQLpostgispostgresqlspherical-geometry

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?

Best Answer

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.

Related Question