Wednesday, July 20, 2011

Geolocation with SQL Server 2008 and geocoding an address

Let's say you have a mobile app / website and you want to allow the user to search for whatever within X miles. To find the distance between two points, you first need the coordinates of the two points.

If you're using a device that has GPS, this is easy. It's just a call to the system API or whatever. If you're using a website that asks the user for their address then you need to do something called geocoding. Luckily there are several web services available to do this for you. So on your website, if you were using Google's web service, you would use this javascript:
        function btnGeocode_onclick() {
            var geocoder = new google.maps.Geocoder();
            var address = document.getElementById("txtAddress").value;
            geocoder.geocode({ 'address': address },
            function(results, status) {
                if (status == google.maps.GeocoderStatus.OK) {

                    document.getElementById("txtGeolocation").value = results[0].geometry.location;
                }
                else {
                    document.getElementById("lblResults").value = status;
                }
            }

            );
        }

What this does is takes the address in the txtAddress TextBox, and then puts the coordinates of that address in the txtGeolocation TextBox.

So now you have the points, now you need a way to determine the distance between two points. Well, this is really easy with SQL Server 2008, using the new geography data type.

So first, here's how you would insert the coordinates into the database:
latlong is type geography

INSERT INTO [test]
           ([latlong])
     VALUES
(geography::Point(37.7926969,  -122.405512, 4326))


Next, get all whatevers that are within 2 miles of (37.7926969, -122.405512)
For this you use the STDistance function, creating a geography point from a passed in value (37.7926969, -122.405512)

SELECT latlong.STDistance(geography::Point(37.7926969,  -122.405512, 4326))/1609.344 as [miles] FROM test
WHERE latlong.STDistance(geography::Point(37.7926969,  -122.405512, 4326))/1609.344 < 2
ORDER BY lat.STDistance(geography::Point(37.7926969,  -122.405512, 4326))


No comments:

Post a Comment

There was an error in this gadget