MySQL Spatial POINT Search for nearest location

Hi all,
I started playing around with Spatial POINT this weekend to hopefully come up with a more efficient way of handling location searches for a project I’m working on. I have a couple of test tables setup and have points in them but can’t seem to get any type of calculation to work on the fields.

I have two tables (both are just temp tables to teach myself this so they are generic and the naming conventions might not be the best :))

Tables


zip_codes
  zip
  latitude
  longitude
  location POINT

locations
  name
  zip
  latitude
  longitude
  location POINT

All of the points have been populated from the lat/long fields and I can retrieve a value from a location field as such:


SELECT 
    name
  , latitude
  , longitude
  , AsText(location)

FROM locations

WHERE zip = '31602'

This query yields the expected results as I get a proper point back.
POINT(30.9169943097106 -83.3525955677032)

Now… my next step is to find the ‘nearest location’ from any particular zip code. The zip_codes table to find the ‘location’ POINT of that zip code, then compare it to our locations table and find the closest location POINT to it.

Here is basically where I am, I have tried multiple variations of this query and always seem to get NULL as the value for distance, it doesn’t seem to be calculating it at all?


SELECT
    c.name
  , c.zip
  , round(glength(linestringfromwkb(linestring(asbinary(c.location), asbinary(a.location)))))

    AS distance

FROM 
     locations c
   , zip_codes a

WHERE 
    a.zip = '31602'

ORDER BY distance ASC 

LIMIT 10;


This query returns as many results as I LIMIT to but all distances are NULL and the results are not in proper order.

I’m open to any suggestions or guidance, this is my first stab at Spatial fields so I may be doing something really dumb :smiley: Thanks in advance for any guidance or suggestion you may be able to provide.

Think I ‘may’ have figured it out. The above query was based off the docs on the MySQL site, but… on a hunch I removed the ‘AsBinary(…)’ function, now it all seems to be working and calculating the distance properly.

Not sure why… but it now seems to work.

Nevermind, thought it was working but it’s giving weird values for the distance. They don’t seem to be miles or kilometers… hmmm…

I’m open to input on this. I’m very confused at the moment :smiley: