SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Code:
    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:

    Code:
    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?

    Code:
    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.

  2. #2
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •