Search code examples
mysqlgeolocationdistancespatialhaving

ST_Length measure unit type

I have MySQL table and request to get distance in the given radius:

    SELECT 
        *,
(ST_Length(LineStringFromWKB(LineString(location, GeomFromText("POINT(55.525211 30.492340)"))))) AS distance                            
    FROM 
        `#__positions` 
    HAVING 
        distance < radius

For example I wan't to get all records in 2 miles radius. But my problem as I don't know the unit returned by ST_Length. If it is degree how can I convert this to miles?

-location table column contains a lot of spatial points all over the globe. Thanks.

Solution

  • I'm not a MYSQL user, but from what I can tell, it is similar to MSSQL in this regard.

    I beleive GeomFromText is cartesian, and if so, then ST_Length will not give you what you want.

    Try this to be sure:

    SELECT
ST_Length(
  LineStringFromWKB(
    LineString(
      GeomFromText("POINT(0 0)"), 
      GeomFromText("POINT(1 0)")
    )
  )
)

    If that gives you a value of 1, then you are operation in Cartesian, or flat-plane, coordinates.

    In your question you asked if the unit returned is degree, and that is almost true.

    The problem with this is that not all degrees are equal. Pretend you are a few feet away from the north pole; you can walk between 0 degrees longitude and 90 degrees longitude in a few steps. If you ware on the equator, you would have to cross an ocean. This means that degree is not convertible to length.

    From the (admittedly small) amount of googling I did on this problem, it appears that there is no inbuilt function in MYSQL to give you a surface-of-the-globe measurement. I think your best bet is to write a function to use the haversine formula to determine your distances.