It’s pretty common to want to find the distance from a certain location to a location stored in your database. For example, in the Home Depot or Walmart website you might want to find the closest store. The locations of all of the stores will be stored in a database, and then given your location the distance to each store can be calculated and ordered by the closest stores.
The following example SQL Server queries shows you how to do just that:
--Create the table. Use the 'geography' data type to store spatial data
CREATE TABLE [dbo].[places](
[address] [varchar](max) NULL,
[coords] [geography] NULL
)
--Add locations to the table. You use the geography::Point notation to enter set the latitude/longitude for the location.
insert into places (address, coords)
values ('Shell - 4685 Central Pkwy E Mississauga, ON L4Z 2E4',geography::Point(43.616597, -79.641492,4326))
insert into places (address, coords)
values ('Mcdonalds - 44 Bristol Rd E, Mississauga, ON L4Z 3K8',geography::Point(43.614680, -79.660373,4326))
insert into places (address, coords)
values ('Tim Hortons - 3 Robert Speck Pkwy, Mississauga, ON L4Z 2G5',geography::Point(43.598257, -79.636555,4326))
--Set a variable equal to the point to search from, also by specifying the latitude/longitude
DECLARE @mylocation geography = geography::Point(43.616600, -79.638489,4326)
--Query, calculating the distance from @mylocation to each point using the STDistance function of the geography datatype. Order the results by that calculated distance
SELECT TOP(7) *, coords.STDistance(@mylocation) distanceinmeters FROM places
ORDER BY coords.STDistance(@mylocation);
A few notes:
The number 4326 is used several times in the queries. This specifies the coordinate system to use. 4326 refers to WGS84 which is the common standard used: https://en.wikipedia.org/wiki/World_Geodetic_System. Distance calculation results will be in meters.
To get the latitude/longitude of locations to store in your database, it is probably easiest to get them from Google Maps. Simply right-click the location you would like to get the values for, and select ‘What’s here?’ from the popup menu. The latitude and longitude of the location will be displayed.
To get the latitude/longitude of the From point, you can use the Geolocation API from the browser: https://developer.mozilla.org/en-US/docs/Web/API/Geolocation_API. Alternatively you can convert an address or postal code to a latitude/longitude using a geocoding api, like the Google Maps geocoding api: https://developers.google.com/maps/documentation/geocoding/intro
Leave a Reply