geospatial - Storing Lat Lng values in MySQL using Spatial Point Type

Question

Tech used: MySQL 5.1 and PHP 5.3

I am just designing a new database for a site I am writing. I am looking at the best way of now storing Lat and Lng values.

In the past I have been using DECIMAL and using a PHP/MySQL select in the form:

SQRT(POW(69.1 * (fld_lat - ( $lat )), 2) + POW(69.1 * (($lon) - fld_lon) * COS(fld_lat / 57.3 ), 2 )) AS distance

to find nearest matching places.

Starting to read up more on new technologies I am wondering if I should use Spatial Extensions. http://dev.mysql.com/doc/refman/5.1/en/geometry-property-functions.html

Information is quite thin on the ground though and had a question on how to store the data. Instead of using DECIMAL, would I now use POINT as a Datatype?

Also, once stored as a POINT is it easy just to get the Lat Lng values from it in case I want to plot it on a map or should I additionally store the lat lngs as DECIMALS again as well?

I know I should prob use PostGIS as most posts on here say I just don't want to learn a new DB though!

Follow up

I have been playing with the new POINT type. I have been able to add Lat Lng values using the following:

INSERT INTO spatialTable (placeName, geoPoint) VALUES( "London School of Economics", GeomFromText( 'POINT(51.514 -0.1167)' ));

I can then get the Lat and Lng values back from the Db using:

SELECT X(geoPoint), Y(geoPoint) FROM spatialTable;

This all looks good, however the calculation for distance is the bit I need to solve. Apparently MySQL has a place-holder for a distance function but won't be released for a while. In a few posts I have found I need to do something like the below, however I think my code is slightly wrong:

SELECT
 placeName,
 ROUND(GLength(
  LineStringFromWKB(
   LineString(
    geoPoint, 
    GeomFromText('POINT(52.5177, -0.0968)')
  )
  )
))
AS distance
FROM spatialTable
ORDER BY distance ASC;

In this example geoPoint is a POINT entered into the DB using the INSERT above.

GeomFromText('POINT(52.5177, -0.0968)' is a Lat Lng value I want to calculate a distance from.

More Follow-up

Rather stupidly I had just put in the ROUND part of the SQL without really thinking. Taking this out gives me:

SELECT
placeName,
(GLength(
LineStringFromWKB(
  LineString(
    geoPoint, 
    GeomFromText('POINT(51.5177 -0.0968)')
  )
 )
))
AS distance
FROM spatialTable
ORDER BY distance ASC

Which seems to give me the correct distances I need.

I suppose the only thing currently that needs answering is any thoughts on whether I am just making life difficult for myself by using Spatial now or future-proofing myself...

Answer 1

I think you should always use the highest level abstraction easily available. If your data is geospatial, then use geospatial objects.

But be careful. Mysql is the worst geospatial database there is. Its OK for points but all its polygon functions are completely broken - they change the polygon to its bounding rectangle and then do the answer on that.

The worst example that hit me is that if you have a polygon representing Japan and you ask what places are in Japan, Vladivostok gets into the list!

Oracle and PostGIS don't have this problem. I expect MSSQL doesn't and any Java database using JTS as its engine doesn't. Geospatial Good. MySQL Geospatial Bad.

Just read here How do you use MySQL spatial queries to find all records in X radius? that its fixed in 5.6.1.

Hoorah!