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...

Kalila

Future Proofing: You Aren't Gonna Need It – Mindamindanao

Very helpful and useful info. – Vulgarize

No, you are indeed going to need it. If you want to use MySQL and search (fast) for places near a certain point, the spatial index is going to be needed. – Detain

Which unit the distance is been calculated? – Mme

yeah, in which unit the distance is been calculated? – Panpsychist

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!

Fairtrade

things have been changed. mysql bad poly queries are happen when one us MBR functions which relay on calculating the minimum bounding rectangle. since mysql 5.6. new functions came along. function starts with "st_*" performs much better. for example contains(g1, g2) vs st_contains(g1, g2). more info can be found on percona.com/blog/2013/10/21/… – Regen

Yes, I think OpenGIS decided the st_ names were more portable across database vendors. They should definitely be used. I didn't think they were accurate in 5.6 though. – Fairtrade

note that on ` 5.6.23-72.1 - Percona Server (GPL)` it seems the st_contains works like expected. Tested only for near by (i.e :up to 800km distance ) coordinates. Maybe for long distance coordinates it perform differently – Regen

searching more deeply #7783373 says 5.6.1 which matches your experience. – Fairtrade

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.

Graybeard

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.

Fourpence

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

Maniemanifest