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…