Blog Details

In mobile application development it's a common requirement for a device to know or detect where the user is located and match that location to a list of the nearest entities the site has (e.g.  stores, office locations, branches, etc.)  Spatial data is quite useful in the case where a business has several business entities where location is represented by an address (street, city, state, country, zip), that can be converted to a latitude/longitude pair and vice-versa.

Spatial data support has been built into Microsoft SQL Server 2008 to drive analysis of location data that can be stored and manipulated in a SQL Server database.  In a few steps you can retrofit a database table of addresses to utilize spatial data, so that knowing where a customer is via a latitude/longitude allows you to answer questions such as:

  • What are the closest office branches for a specific customer?
  • Which salesperson works closest to the customer?

The following example illustrates how to add geography data, using a new table for business locations with an Identity column and geography datatype column.

IF OBJECT_ID ( 'dbo.GeoSpatial', 'U' ) IS NOT NULL
DROP TABLE dbo.GeoSpatial;
GO

CREATE TABLE GeoSpatial
( id int IDENTITY (1,1),
GeogCol1 geography,
GeogCol2 AS GeogCol1.STAsText() );
GO

Add your business entity location info:
INSERT INTO GeoSpatial (GeogCol1) VALUES (geography::Point(37.7918148, -122.420860899999 , 4326));
INSERT INTO GeoSpatial (GeogCol1) VALUES (geography::Point(47.6139774, -122.322950499999 , 4326));
INSERT INTO GeoSpatial (GeogCol1) VALUES (geography::Point(47.6670515, -122.2810999 , 4326));
INSERT INTO GeoSpatial (GeogCol1) VALUES (geography::Point(37.7992722, -122.266454899999 , 4326));
INSERT INTO GeoSpatial (GeogCol1) VALUES (geography::Point(40.166201, -122.224715 , 4326));
INSERT INTO GeoSpatial (GeogCol1) VALUES (geography::Point(47.0378741, -122.9006951 , 4326));
INSERT INTO GeoSpatial (GeogCol1) VALUES (geography::Point(42.3187039, -122.859956 , 4326));

...

Now your device or application can detect your location (longitude and longitude) and query the database to answer the first question listed above. 

Example: The device used is in downtown Seattle, WA and we want to display the nearest 3 branch locations:

DECLARE @Seattle geography;
SET @Seattle = geography::Point(47.6, -122.33 , 4326)

SELECT TOP(3) b.id [BranchID],
  @Seattle.STDistance(b.GeogCol1)/1000 [Distance in km], b.GeogCol1.STAsText() AS [LongLatitude]
FROM GeoSpatial b
ORDER BY @Seattle.STDistance(b.GeogCol1)

The output from the query:

BranchID  Distance in km LongLatitude
23  1.641953932  POINT (-122.322950499999 47.6139774)
24  8.311577308  POINT (-122.2810999 47.6670515)
14  75.9419116  POINT (-122.9006951 47.0378741)

 

Now on your device you can utilize one of the many publicly available services  (e.g. Mapquest, Google Maps, Bing Maps, etc.)  to represent your ‘nearest’ locations along with imagery and routing information.

Conclusion

As location-sensitive applications and devices become more ubiquitous, the need arises for a fast and efficient storage system to manipulate spatial data.  The use of the geography datatype in SQL Server 2008 provides an efficient data storage and analysis solution for geospatial data.

Stay Tuned

If you want to augment a database table with geospatial data, you can use one of many publicly available services for mapping.  The Bing Maps SOAP Services provides a simple mechanism for mobile applications to geocode locations, reverse geocode, get maps and generate routes.

In the next post I’ll show you one way of retrofitting an existing customer table with geospatial information for analysis.

Response to Comment Below: what the heck is GeogCol1.STAsText() ??

Regarding the “Insert” statement:
The insert to the geography instance (column GeogCol1) represents a Point instance from its x and y values and a spatial reference ID (SRID).
 
The syntax:
Point ( Lat, Long, SRID )
 
The SRID param identifies the spatial reference system used for either a round-earth or flat-earth mapping.   E.g. the MapPoint geocoder Web service uses GPS coordinates that correspond to SRID 4326 so we reference that by the following:
 
INSERT INTO GeoSpatial (GeogCol1) VALUES (geography::Point(37.7918148, -122.420860899999 , 4326));
 
Regarding the STAsText():
 
The STAsText() method returns the Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation of a SqlGeometry instance.
No action required for this field… it’s just for displaying the geography sql type as text.
 
e.g.
SELECT GeogCol1, GeogCol2 AS [LongLatitude]
FROM GeoSpatial

 
Results:

GeogCol1                                                                                                      LongLatitude
0xE6100000010CBD5296218E513540A6AF1F6203BC63C0          POINT (-157.875412999999 21.318575)
0xE6100000010C8D614ED0264B354016F9F5436CBA63C0         POINT (-157.825716 21.293561)

Recommended For You

Review of the underrated mobile development framework.

A high level look at Eclipse as a development platform.