Heeeeee’s back!! Dave made a cameo appearance at our very own Sandbox, and started an impromptu “Dave’s Corner”, just like old times.
The topic he chose today was to teach us how MySQL’s built in spatial data types work, in all its quirks and intricacies…
For this session, we looked at an existing MySQL database called “ZIG”, and looked at a table called “location”, that stores the geo-coordinates in separate latitude and longitude fields. The idea would be to create a single spatial “point” type column that would store the geometry for each location in the table. After creating a “point” type column for the location table, we use the following update sql to populate it:
UPDATE `location` SET `latlng`= geomFromText(concat('POINT(',`latitude`,' ',`longitude`,')'))
Now that the spatial column has been created, how do we query for it? Assume that we want to look up all the locations that fall within a given bounding box, we can create the following SQL to do so (it’s ugly and painful according to Dave…)
SELECT x(latlng), y(latlng)
FROM zig
WHERE MBRContains(GeomFromText('POLYGON((-118.7 33.4,-118.7 33.2,-118.5 33.4,-118.5 33.4,-118.7 33.4))'),latlng)
Thanks Dave! Another corner may just be around the corner…
So I’m also working with geolocations in a MySQL setting using a C# API. I’ve come across a few interesting facts about how MySQL handles distance calculations using point types. One huge thing I overlooked is the fact that Points are stored at X and Y locations. Pretty simple, but if you think about how Lat and Lon are used on the globe, if you were to lay a cartesian grid over the map, the X axis would be the Longitude and the Y axis would be Latitude. This means that when using Point types in a MySQL database, it should be set as Point(Lon, Lat).
I don’t take credit for this “Gotcha” moment. The blog I read is here:
https://tighten.co/blog/a-mysql-distance-function-you-should-know-about
I know this helped me out a lot along with spatial indexing as well so I figured I would pass it on.