Can I store WGS84 latitude / longitude directly as spatial data in MySQL?

2
votes

I have a set of WGS84 coordinates. The conservative approach is to store them as number ( What datatype to use when storing latitude and longitude data in SQL databases? ) and calculate distances as here ( latitude/longitude find nearest latitude/longitude - complex sql or complex calculation).

A little more advanced approach is to create a user defined function for "in range" ( MySQL User Defined Function for Latitude Longitude Syntax )

MySQL latitude and Longitude table setup uses a spatial index . But I do not quite get if it is possible to directly store WGS coordinates, and then query a distance based on the Point data type?

Example: Data look like this: "lat" 40.7142298, "lng" -73.9614669, and this is what I'd like to import. Then query like "select all tupel within 10km of lat / lng".

2
mysqlspatial-indexwgs84
I could be totally missing the point of your question, but what is the problem in storing a coordinate like "lat=40.7, lng=-73.9" as a POINT(40.7 -73.9)? Or is it that you want to avoid the (string) conversion when importing your data? – RandomSeed
Or I miss a point here - I have never used MySQL spatial. How does the DB know 40.7 is a WGS coordinate and not a coordinate in a normal X/Y system, ie. metrical coordinates vs. polar (latitude and longitude). And how do I make obvious that something like 40.30 does not mean 30 minutes but 0.3. – Horst Walter
Now I understand what I missed :) Yes, to calculate a distance from coordinates, you would have to convert linear distances to polar coordinates. This is not trivial, but it could work. Regarding your second question, the POINT() datatype is decimal, so "40.5" must not mean anything else but "forty and a half". – RandomSeed

2 Answers

1
votes

Have you read the Manual section on the Spatial Extensions?

To query, define a query region (e.g. rectangle) and then filter by within

0
votes

SQL server 2008 has Geography data type you can use and to query it back use open query. once that is done, geo spatial your database and start doing stuff.