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!

Answer 2

Mysql GIS yagni:

If you have no experience with GIS, learning spatial extensions is practically like learning a new database, plus a little math, and a lot of acronyms. Maps, projections, srids, formats... Do you have to learn all that to calculate distances between points given a certain lat/long: probably not, will you be integrating 3rd party GIS data or working with anything more complex than points, what coordinate system will you be using?

Going back to yagni: do things as simple as posible, in this case implement your code in php or with simple SQL. Once you reach a barrier and decide you need spatial, read up on GIS system, coordinate systems, projects, and conventions.

By then, you will probably want PostGIS.

Answer 3

It's a good thing, because then you get to use spatial indexes on your queries. Limit to a bounding box, for example, to limit how many rows to compare against.

Answer 4

If you can affor placing some extra code into your backend, use Geohash.

It encodes a coordinate into a string in a way that prefixes denote a broader area. The longer your string is, the more precision you have.

And it has bindings for many languages.

http://en.wikipedia.org/wiki/Geohash https://www.elastic.co/guide/en/elasticsearch/guide/current/geohashes.html