Anyone using MySQL spatial data types for geolocation applications?

[sneakyimp]

My collaborators and I are working on defining some database structures and expect we'll need to be able to locate items for sale within some distance from a particular address. We'll probably be using the google maps API to geolocate these addresses (i.e., determine the lat/long of an address) and then we'll either need to store the lat/long in the db table OR create a MySQL POINT column in the table. The docs:
https://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html

I'm wondering if anyone is using this? It seems a bit awkward to insert and select the POINT values into the db table. Not a super huge pain but you apparently have to put the coordinates into a string and insert that:

INSERT INTO mytable VALUES (ST_PointFromText('POINT(1 1)'));

or alternatively you can create some kind of "well understood binary data" and insert that.

Also, I wonder if there's any real advantage to the SPATIAL INDEX if I'm just looking for points inside some rectangle.

[jeepin81]

Sounds pretty interesting that it will give the calculation between 2 points. We have an app that calculates the distance but it's planar coordinates so it's pretty wonky and produces the wrong distance. From what I read Spatial data type uses planar math as well. You could probably just store the lat/long and do the equation yourself using haversine method. Just a thought.

Another thing, I heard google was going to start limiting their free mapping API query request. Depending on your traffic/budget might be something to look into. I think it's like 25k request per day.

Here's our current func to get the distance.

function distance($lat1, $lon1, $lat2, $lon2, $unit){

  $convr = $lon1 - $lon2;
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($convr));
  $dist = acos($dist);
  $dist = rad2deg($dist);
  $miles = $dist * 60 * 1.1515;
  $unit = strtoupper($unit);

  if($unit == "K"){
    return ($miles * 1.609344);
  }
  elseif ($unit == "N"){
    return ($miles * 0.8684);
  }
  else{
    return $miles;
  }
}

[dalecosp]

jeepin81;11053045 wrote:

do the equation yourself using haversine method.

Is that the best algorithm available? Someone should implement Vincenty or Karney ....

[cretaceous]

I store lat and long values as floats
I source those from some other map service which is easier to use than Google, when inputting post codes.
But once the initial 100 were done, any additions are only a few here and there.

Damn, Google's docs are confusing about what's allowed and what isn't - and no one replies if you send a question.

[sneakyimp]

jeepin81;11053045 wrote:

Sounds pretty interesting that it will give the calculation between 2 points. We have an app that calculates the distance but it's planar coordinates so it's pretty wonky and produces the wrong distance. From what I read Spatial data type uses planar math as well. You could probably just store the lat/long and do the equation yourself using haversine method. Just a thought.

I'm not really concerned about calculating the distance between two points. There are a variety of formulas that can do this. I'm more concerned about efficiency of the search when querying a db based on coordinates. In my experience, this is a heftier computational consideration. More specifically, if you formulate your query wrong, it has to calculate the distance on every single record in your table which results in a table scan every single time you want to do some kind of distance-based search.

jeepin81;11053045 wrote:

Another thing, I heard google was going to start limiting their free mapping API query request. Depending on your traffic/budget might be something to look into. I think it's like 25k request per day.

Good to know. This could be a significant consideration considering the volume of geolocated product data we import each night.

jeepin81;11053045 wrote:

Here's our current func to get the distance.

Thanks for this. I'm familiar with great circle distance calculations thanks to some posts ten years -- JESUS CHRIST ten years ago?
http://board.phpbuilder.com/showthread.php?10329774-selecting-5-random-events-within-X-miles

dalecosp wrote:

Is that the best algorithm available? Someone should implement Vincenty or Karney ....

Lolwut? Never heard of those.

cretaceous;11053055 wrote:

I store lat and long values as floats

This sounds like what I've been doing recently. Queries to find matching records within some bounding rectangle can be made efficiently because you can calcuate upper/lower bounds for lat and long that are specified as scalar values in your query:

SELECT * FROM table WHERE lat >= $minimum_lat and lat <= $maximum_lat AND long >= $minimum_long AND long <= $maximum_long;

This eliminates the need to calculate some distance value for each record in your table so you can make proper use of the indexes you define on lat and long. I do wonder, though, whether one should have a distinct index on lat and long or whether one should define a composite index on lat and long together. Sadly, my understanding of composite indexes (indices) is insufficient to understand the performance implications. Any thoughts on this would be most welcome.

cretaceous;11053055 wrote:

I source those from some other map service which is easier to use than Google, when inputting post codes.

Any recommendations you have for other geolocating service? It's pretty easy to get lat/long for all the postal codes in the US, but I'd like to geolocate a specific address. We are highly likely to run into problems with the Google Maps daily limit mentioned by jeepin81.

cretaceous;11053055 wrote:

Damn, Google's docs are confusing about what's allowed and what isn't - and no one replies if you send a question.

Yep. No help at all for anything from google. Audio disappeared from a youtube video I posted like a decade ago. I asked them repeatedly if they could fix it or tell me how I might fix it and never heard anything at all from any living human at google. I felt like the apes in 2001 freaking out as they stare at the weird black monolith....

[dalecosp]

sneakyimp wrote:

Lolwut? Never heard of those.

Vincenty's algorithms (c. 1975) were much more accurate for geodesics (earth distance calculations) than Haversine's.

https://en.wikipedia.org/wiki/Vincenty%27s_formulae
https://www.researchgate.net/post/Vincenty_vs_Haversine_Distance_Calculations

The main problem with Vincenty is that is fails in antipodal cases; Karney has issued improved algorithms designed to abate this issue:

http://geographiclib.sourceforge.net/geod.html