Retrieve coordinates from MySQL Geometry column
geospatial, google-maps, mysql, sql
Solution
If you use `AsText` you will get your object in WKT format. To extract point coordinates 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 result of `AsText` as BLOB because it can get really long for complex objects. It's just that whatever you are using to display results can't cope with that, but the text is there.
Problem
I just started using the MySQL Geospatial extension in hope of speeding up lat/lng range searches in my database of 500K points. A new `GEOMETRY` column `p` is created. Problem:`p` & `AsText(p)` values returned are simply `(BLOB)`, not in decimal degrees. No values are returned by `GeomFromText(p)`. Because I had the redundant `lat` and `lng` columns, I still manage to get the lat lng values that I need. But I'm thinking of removing the `lat` and `lng` cols and just rely on `p`. Converted existing Lat/Lng values to Points ``` UPDATE listings SET p = GeomFromText('POINT(39.948177 -75.174324)') WHERE listing_id = '585221'; ``` Attempt to Retrieve 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) ```