1. Registered User
Join Date
Apr 2003
Posts
43

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. Window Washer
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....

3. Registered User
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. Window Washer
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?

5. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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.

7. Window Washer
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?

8. Registered User
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. Window Washer
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)

10. Registered User
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. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
MS SQL Server :: Distance Between Two Points Lat/long

SQL 2k8 also has new spatial data types but I have barely played with them.

12. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595