• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 111
  • Last Modified:

Calculate Distance in SQL

Can you please help me write an sql query for an access database where I have a table with the name of a facility, its id and its latitude and longitude. I want to enter the name of the facility and want it to read the whole table, calculate the distance between the entered facility and all the facilities in the table and find me the nearest 3 names of facilities and the distance between the entered facility and the nearest facility listed? I believe this can be done using a Haversine formula but I need help. Thanks
0
gd060
Asked:
gd060
  • 4
  • 3
  • 2
2 Solutions
 
armchair_scouseCommented:
Hi gd60,

This sounds a little like a question that I answered some time ago, at least the bit about calculating distances when you have latitudes and longitudes.  Have a look at:

Calculating distances using latitude and longitude in Microsoft Access

It might set you in the right direction!!  (Sorry, terrible navigation pun, I know!!)

Regards

armchair_scouse
0
 
gd060Author Commented:
Thanks armchair_scouse for your response. I looked at that link before I posted this. I get the formula. As I am not very technical, I need help with the syntax in trying to select the latitude and longitude of the facility input so that it can go in and calculate the distance with each facility and give me the distance and name of the corresponding facility. I hope I am making sense.
0
 
sdstuberCommented:
if you want to eliminate the vba you can do all of the calculations in sql


SELECT   *
    FROM (SELECT name1,
                 name2,
                   iif(
                       x <> 0,
                       atn(y / x) + iif(x < 0, 3.1415926535897932384626433832795028842, 0),
                       iif(y < 0,
                           3 * 3.1415926535897932384626433832795028842 / 2,
                           3.1415926535897932384626433832795028842 / 2
                          ))
                 * 2
                 * 6371
                     AS distance
            FROM (SELECT name1, name2, sqr(temp) AS y, sqr(temp2) AS x
                    FROM (SELECT name1, name2, temp, 1.0 - temp AS temp2
                            FROM (SELECT name1,
                                         name2,
                                         sin1 * sin1 + sin2 * sin2 * cos1 * cos2 AS temp
                                    FROM (SELECT a.name AS name1,
                                                 b.name AS name2,
                                                 SIN(
                                                       (b.latitude - a.latitude)
                                                     / (180 / 3.1415926535897932384626433832795028842)
                                                     / 2)
                                                     AS sin1,
                                                 SIN(
                                                       (b.longitude - a.longitude)
                                                     / (180 / 3.1415926535897932384626433832795028842)
                                                     / 2)
                                                     AS sin2,
                                                 COS(
                                                       b.latitude
                                                     / (180 / 3.1415926535897932384626433832795028842))
                                                     AS cos1,
                                                 COS(
                                                       a.latitude
                                                     / (180 / 3.1415926535897932384626433832795028842))
                                                     AS cos2
                                            FROM facilities a, facilities b
                                           WHERE a.id <> b.id)))))
ORDER BY name1, distance DESC;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sdstuberCommented:
Here's a little more compact query, using spherical law of cosines instead of Haversine
both assume spherical Earth  and hence introduce errors of approximation

S ELECT   *
    FROM (SELECT name1, name2, (atn(-x / sqr(-x * x + 1)) + 2 * atn(1)) * 6371 AS distance
            FROM (SELECT name1, name2, iif(ABS(t) > 1, t - fix(t), t) AS x
                    FROM (SELECT name1, name2, sin1 * sin2 + cos1 * cos2 * cos3 AS t
                            FROM (SELECT a.name AS name1,
                                         b.name AS name2,
                                         SIN(b.latitude / (180 / 3.141592653589793)) AS sin1,
                                         SIN(a.latitude / (180 / 3.141592653589793)) AS sin2,
                                         COS(a.latitude / (180 / 3.141592653589793)) AS cos1,
                                         COS(b.latitude / (180 / 3.141592653589793)) AS cos2,
                                         COS((b.longitude - a.longitude) / (180 / 3.141592653589793))
                                             AS cos3
                                    FROM facilities a, facilities b
                                   WHERE a.id <> b.id))))
ORDER BY name1, distance DESC;
0
 
armchair_scouseCommented:
Hi gd060,

Assuming you want to use an Access database, I have taken the 'postcode' database that was linked in the article I referred to above and modified it slightly.  Where you say you need help with the syntax of the latitude and longitude of each facility, I presumed that you meant that you had a latitude/longitude made up of degrees, minutes and seconds, as opposed to the 'decimal degrees' listed in the 'postcode' example.

I wrote a DecimalDegrees function to convert a standard latitude or longitude into the equivalent decimal degrees value, which can then be passed into the 'GreatCircleDistance' function which calculates the distance between start co-ordinates and end co-ordinates.

For your purposes in the example, the start co-ordinates are the latitude and longitude of the location of the facility from where you are finding the three nearest facilities, and the end co-ordinates are the latitude and longitude of the location of the other facilities for which you are trying to calculate the distance away from your start point.

The table 'Facilities' has a number of facilities listed, with City, Country, Latitude Degrees, Latitude Minutes, North/South Orientation, Longitude Degrees, Longitude Minutes, and East/West Orientation.  So for example, London has the co-ordinates 51 degrees 32 minutes N, 0 degrees 5 minutes W.

The query 'Distance' prompts you to type in a city name, which is your 'start' facility.  It then calculates the distance from your start facility to all of the other facilities.  The second query 'NearestFacilities' builds on Distance and selects the three nearest facilites (it selects 4, but one of the facilities is the start point!!).

So to run this, run the 'NearestFacility' query and enter the city of the start location.  Look at the values in the Facilities table and change them to suit your needs.
FacilitiesDistance.mdb
0
 
gd060Author Commented:
Excellent armchair_souse. It has given me exactly what I was looking for. I will try and integrate it with my table now. Thank you very much.
0
 
gd060Author Commented:
Quick question armchair_souse, I only have latitude and longitude in degrees with a decimal point and the following forumla

ACOS(COS(RADIANS(90-latitude1)) *COS(RADIANS(90-latitude2)) +SIN(RADIANS(90-latitude1)) *SIN(RADIANS(90-latitude2)) *COS(RADIANS(longitude1-longitude2))) *6371

How do i get it to work in access? I guess I would be missing a lot of steps that are converting  latitude and longitude in decimal. I will really appreciate your help.  Thanks.
0
 
armchair_scouseCommented:
Hi gd060, the formula you have got above is almost exactly the same as the one in the database in my first post above, where I added the link 'Calculating distances using latitude and longitude in Microsoft Access'.  If you are using this method, then have a look at my original post, and the database attached there uses the formula you have described above.

Regards

armchair_sCouse :o)
0
 
armchair_scouseCommented:
Did this answer your question?
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now