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 )
All of the points have been populated from the lat/long fields and I can retrieve a value from a location field as such:
This query yields the expected results as I get a proper point back.
WHERE zip = '31602'
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?
This query returns as many results as I LIMIT to but all distances are NULL and the results are not in proper order.
, round(glength(linestringfromwkb(linestring(asbinary(c.location), asbinary(a.location)))))
, zip_codes a
a.zip = '31602'
ORDER BY distance ASC
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.