Get coordinates from MySQL Geometry column
I just started using the MySQL Geospatial extension in the hopes of speeding up finding lat / lng range in my 500k points database. A new column is created GEOMETRY
p
.
Problem: p
and the AsText(p)
return values are simple (BLOB)
, not in decimal degrees. GeomFromText(p)
no values are returned. Since I had redundant columns lat
and lng
, I still manage to get the lat lng values that I need. But I am thinking about dropping columns lat
and lng
and just relying on p
.
Convert existing Lat / Lng values to Points
UPDATE listings SET p = GeomFromText('POINT(39.948177 -75.174324)') WHERE listing_id = '585221';
Trying to get Lat Lng from GEOMTRY col p
SELECT listing_id, lat, lng, GeomFromText(p), AsText(p), p from listings WHERE MBRContains( GeomFromText('Polygon((39 -76, 40 -76, 40 -74, 39 -74, 39 -76))'), p)
source to share
If you use AsText
, you will get your object in WKT format. To extract the coordinates of a point use:
SELECT listing_id X(p), Y(p)
FROM listings
WHERE MBRContains( GeomFromText('Polygon((39 -76, 40 -76, 40 -74, 39 -74, 39 -76))'), p)
MySQL probably returns the result AsText
as a BLOB because it can take a very long time for complex objects. Just what you are using to display the results cannot handle it, but the text is there.
source to share