CODEWITHSUNDEEP
mysqlsqlgeospatial
jskidd3
jskidd3

Reputation: 4783

SQL selecting spatial points

I am trying to select a spatial point in SQL so that I can receive the latitude and coordinate of the point in the most simple form possible.

Currently my query looks like this:

SELECT AsText(`coordinates`) FROM table

This returns something along the lines of:

POINT(53.432985 -1.357258)

Are there any other spatial functions that will allow me to return these as two seperate values or at least make them a little easier to perform something like substr on them?

Ideally I'd like it to return two values, a latitude value and a longitude value

Upvotes: 8

Views: 7076

Answers (2)

jport
jport

Reputation: 170

I assume your geometry field is called coordinates? 

SELECT location.STY as Lat, location.STX as Lon from yourTableName

If it's a geography datatype try 

SELECT location.Lat as Lat, location.Long as Lon from yourTablename

Upvotes: 0

Andrew - OpenGeoCode
Andrew - OpenGeoCode

Reputation: 2287

This is the geospatial way to retrieve latitude/longitude from a POINT type, assuming that coordinates is stored as a POINT type. X will return the latitude and Y the longitude.

SELECT X(coordinates),Y(coordinates) FROM table

Upvotes: 17

Related Questions