How to Use GeoGraphy DataType of SQL in Asp.Net MVC Development

We are sharing this tutorial to let the developers know how to use Geography DataType of SQL to locate places within certain radius. You can read this post and learn finding Geocode of any address or zip code.

This application is used to find the Locations within Radius. The user will select existing Address from drop-down and enter a radius in mile(s), and then click a button to get the Customer locations within a given radius of the selected address.

Step 1 : Find Geocode of an Address

For achieving this we have to find the Geocode of the selected Address first. Geocoding is the process of finding associated geographic coordinates (Latitude and Longitude) of that given location. For finding the Geocoding we can use Location services.

There are many Geocoding APIs to find locations like google, Yahoo, Bing and many more. We are using Google map to find the Geocoding using asp.net. Using GoogleMaps.LocationServices.dll we can find the latitude and longitude. This service is designed for Geocoding static (known) addresses using a REST interface, for dynamic Geocoding of user-defined addresses. After give the reference to this googlemaps.locationservice.dll we can use that to find the location as per below code.

var locationService = new GoogleLocationService();
var point = locationService.GetLatLongFromAddress(addrs);

var latitude = point.Latitude;
var longitude = point.Longitude;

Step 2 : Update Geography field in SQL database with the geocode we found.

For finding geographic location we have to convert that latitude and longitude to Geography point. With the support of seven spatial data objects in Geography Data type, i.e. GeometryCollection, Multipoint, MultiLineString, LineString, MultiPolygon, Point, and Polygon, you can develop and work accordingly. Out of these objects, we are using Point in below instance, where Geography data type is named as LatLong.

UPDATE Customer SET LatLong = GEOGRAPHY::Point (@Latitude, @Longitude, 4326) Where Pincode = selected address pincode

*Note here 4326 is declares a geographic spatial reference system that the SRID ( Spatial Reference Identifier ).

Step 3: Set that Geographical field as Starting Point and query to SQL for finding records.

Now we set LatLong as starting point and from that starting point to the x-mile we have to find locations of stores. We give the mile and find the location using store procedure, here in Below Fig. sp_DistanceBetweenLocation we give the Parameter as mile and clientID of the client whose address we selected above. Based on that we will get the Locations within given mile.

In above store procedure, we can find the distance using STDistance () from starting point.

STDistance () is use for find distance between two geography point. For compare with given mile from parameter we have to convert the result distance that we got using STDistance (). After executing this store procedure we will get the locations within given Radius.

So, using Google Location service we can get the Geocode (latitude longitude) of any address or zipcode. Use of Geography datatype of SQL database will make the things much easier to find records using STDistance() function on Geography column by specified radius. The Geography datatype will be much helpful when you require any geographical calculation in system.

We shared this tutorial for the benefit of entire development community. By following the above code and steps, developers can get the Geocode (latitude, longitude) of particular zip-code or address within a certain radius.

This article is written by Ethan Millar. He is intense about coding and everything related to .Net MVC Development. , He has been involved in a wide range of projects and best practices to develop high quality software that meets project requirements, budget, and schedule. You can hire him as a asp.net programmers. Follow him in Google+.