So here's the situation: I've got a table for customers and a table for stores, each has their address. When a customer is searching for a store I would like to get the stores sorted by their distance from his address. I get the distance between addresses using the google maps API. any ideas? Thanks! * I use microsoft SQL server with asp.net.
Asked
Active
Viewed 375 times
0
Martin Smith
- 438,706
- 87
- 741
- 845
WhiteBlue
- 313
- 1
- 2
- 7
-
1Do you have longitude/latitude for the stores and customers? Are you using the geography type? – Martin Smith May 03 '16 at 16:25
-
Yes, both address and longitude/latitude coordinates. – WhiteBlue May 03 '16 at 16:30
1 Answers
0
I've used inherent SQL Server DbGeography math to do things like this in the past.
Assuming you've already Imports System.Data.Entity.Spatial, then:
Dim dblDistance As Double
Dim geoHouseLocation As DbGeography
Dim geoStoreLocation As DbGeography ' = appropriate table value
geoHouseLocation = DbGeogrpahy.FromText("POINT(" & stLong & " " & stLat & ")")
dblDistance = geoHouseLocation.Distance(geoStoreLocation)
If you're looking to compute this using SQL instead, then we would need to know what format your Lat/Long fields are in (are they stored as the DbGeography type, or as strings, or as numbers, etc?) For instance, if you have Geography data types saving the point locations of both houses and stores in their tables, then the following query would give you the stores closest to the given house in ascending order:
SELECT tblHouses.HouseID, tblStores.StoreName, tblHouses.GeoPoint.STDistance(tblStores.GeoPoint) AS GeoDistance
FROM tblHouses, tblStores
WHERE tblHouses.HouseID = 1
ORDER BY tblHouses.GeoPoint.STDistance(tblStores.GeoPoint) ASC
Sturgus
- 666
- 4
- 18