Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2003
    Posts
    43

    Unanswered: Nearest distance

    Hi
    How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
    Help will be appreciated

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    They talk about something like that here, in their database section...

    http://skyserver.fnal.gov/en/

    As far as understanding it.....I have enough trouble tieing my shoes....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2003
    Posts
    43
    Hi
    I looked through the site but is not of much help..can you elaborate
    thanks



    Originally posted by Brett Kaiser
    They talk about something like that here, in their database section...

    http://skyserver.fnal.gov/en/

    As far as understanding it.....I have enough trouble tieing my shoes....

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Brett Kaiser
    As far as understanding it.....I have enough trouble tieing my shoes....
    You didn't get this part, huh....

    What are you trying to do...maybe it's a simple answer....


    "Distance" is a relative thing...

    Got indexes?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2003
    Posts
    43

    Re: Nearest distance

    Hi
    I need the nearest distance in miles of one point from the other.



    Originally posted by namitao
    Hi
    How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
    Help will be appreciated

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nearest distance between two values? A distance can only be "nearest" to one point (in the general case), because you can't optimize for more than one criteria. Are you talking about some kind of linear regression between the points?

    I think you need to post some sample data and an example of the result you are looking for.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm confused...I originally thought you wanted the distance between rows of data...


    Do you want the distance between points on a map?

    I did this once for a delivery system...

    You want to google the "Great Circle" trig function...I can't find the math....but here's someone who built something...

    http://williams.best.vwh.net/gccalc.htm


    But you need longitude and latitude of the addresses...


    Is that what you're looking for?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2003
    Posts
    43
    Hi
    For Example I have 2 tables
    Table A
    Number Latitude Longitude Time total
    1 48.2951 -122.276 1:22:49 -87
    2 48.2952 -122.292 1:17:35 -92
    3 48.2952 -122.292 1:16:35 -91
    4 48.2952 -122.276 1:21:59 -86
    5 48.2952 -122.276 1:22:48 -91
    6 48.2953 -122.292 1:17:34 -87
    Table B
    Number Latitude Longitude Time total_c
    1 48.2904 -122.271 1:24:30 -87
    2 48.2904 -122.271 1:23:51 -88
    3 48.2904 -122.271 1:24:29 -87
    4 48.2904 -122.271 1:23:52 -85
    5 48.2904 -122.271 1:24:28 -86.5
    6 48.2904 -122.271 1:23:53 -86

    I need to find the shortest distance between the points of the 2 tables.
    I need to find the shortest distance of points in Table B to point in Table A.
    I hope this can help someone answer my question

    Thanks







    Originally posted by Brett Kaiser
    I'm confused...I originally thought you wanted the distance between rows of data...


    Do you want the distance between points on a map?

    I did this once for a delivery system...

    You want to google the "Great Circle" trig function...I can't find the math....but here's someone who built something...

    http://williams.best.vwh.net/gccalc.htm


    But you need longitude and latitude of the addresses...


    Is that what you're looking for?

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I did this in Access once...


    You need the formual

    http://en2.wikipedia.org/wiki/Great_circle_distance

    Then create it as a udf and join the tables passing the 2 ponts in...

    Have to udf return the distance....

    I should rewrite this in sql server...I'll have to dig it up....

    It was actually a lot of fun building it (...geez what a geek)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Dec 2010
    Posts
    2

    Similar Issue

    I am having a similar issue.

    I have a table populated with the locations of my stores.

    I have a second table populated with the addresses of prospects.

    Both tables contain lat/long.

    I would like to append a location to the list of prospects based on the closest distance.

    For example:

    John Doe from Table 2 is 3.6 miles from location 1, and 4.5 miles from location 2.
    I would like to append location 1 and 3.6 to John Doe's row.

    I can calculate distance. What I need is the ability to select the location with the lowest distance and append that information to Table 2 without building a temp table for each location and selecting the row with the lowest distance.

    Any Ideas?

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    MS SQL Server :: Distance Between Two Points Lat/long

    SQL 2k8 also has new spatial data types but I have barely played with them.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Dec 2010
    Posts
    2

    Nice code, but...

    Nice code, but...

    How would I use this to determine the shortest distance from each of my prospects (in the previously mentioned example) to all of my locations?

    I only want to append the shortest distance to a location and that location's key.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I though you already had the distances? In which case you just need to use min() in a subquery to isolate the nearest point and append it to the table.

    You need to be a lot more clear on your requirements. We are all confused.

    I also suggest that you start a new thread for this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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