I want to store thousands of latitude / longitude points in MySQL db. I managed to set up the tables and add data using geospatial extensions, where the column coordinate is the point (lat, lng).
Problem:
I want to quickly find the “N” nearest records for latitude “X” degrees and longitude “Y” degrees. Since the Distance () function has not yet been implemented, I used the GLength () function to calculate the distance between (X, Y) and each of the records, sort by ascending distance, and limit the results to "N". The problem is that this is not calculating the shortest distance with spherical geometry. This means that if Y = 179.9 degrees, the list of nearest records will include only longitudes from 179.9 and decrease, although closer records exist with an increase in longitude from -179.9.
How is the longitude gap typically handled when working with spherical geometries in databases? There should be an easy solution to this, but I should just look for the wrong thing because I did not find anything useful.
Should I just forget the GLength () function and create my own function to calculate the angular split? If I do, will it still be fast and take advantage of geospatial extensions?
Thank!
Josh
UPDATE:
This is what I describe above. However, this is only for SQL Server. SQL Server appears to have both Geometry and Geographic Data Types. Geography does exactly what I need. Is there something similar in MySQL?
source
share