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 Thanks in advance for any guidance or suggestion you may be able to provide.