Part VII - WiE Goes Spatial: Using SQL Server 2008 as a Spatial Rules Engine for Location-based Services.

  • Article

In this second to last chapter in the series I’ll discuss leveraging SQL Server 2008’s support for spatial data types to implement the “spatial rules” for the WiE Community. Specifically, this article will implement 2 examples of simple proximity rules that drive most location-based service scenarios:

- Proximity to a Point of Interest (fixed)

- Proximity to other members (dynamic)

I’ll also touch on an approach to synchronizing data to and from SQL Data Services (SDS – Formerly known as SSDS) using SQL Server Integration Services (SSIS).

Don’t PASS this up!

Ed Katibah and I will be presenting SQL Server Spatial and WiE in a couple weeks at this year’s PASS Conference . We look forward to seeing you there, please stop by and share your experiences with Spatial, SQL Compact, and Mobile development.

An Introduction to SQL Server Spatial Data types

There are two related data types associated with the new spatial support in SQL Server 2008. These are the GEOMETRY and GEOGRAPHY data types. The GEOMETRY data type maps data on a two dimensional plane (x & y coordinates) while the GEOGRAPHY data type deals with the fact that the earth is not flat (nor is it a perfect sphere) by dealing with data on a geodetic plane.

The most visible difference is in specifying positions. The GEOGRAPHY data type expects positions specified using latitudes and longitudes while with the GEOMETRY data type specifies them as X, Y coordinates.

The Shape of Things…

The SQL Server spatial data types allow you to represent any shape that can live on a plane. These shapes include POINTs, MULTIPOINTs (a collection of points), LINESTRINGs (continuous line), POLYGONs and GEOMETRYCOLLECTIONs (collection of shapes).

SQL Server stores these shapes internally as binary data but they are represented using a well known text (WKT) representation. You can learn more about WKT at https://www.opengeospatial.org.

Both the GEOMETRY and the GEOGRAPHY data types offer the same functions for operating over spatial data:

Function

Description

AsGML

Returns the shape in GML format.

GeomFromGML

Builds a GEOMETRY object from GML text.

STArea

Returns the total area of the shape(s)

STAsText

Returns a WKT representation of the shape.

STDistance

Shortest distance between two points.

STGeomFromText

Builds a GEOMETRY object from WKT text.

STIntersects

True if shape intersects another shape.

STTouches

True if shape touches another shape.

STSrid

Returns the SRID for the shape / value.

STWithin

True if shape is contained by another shape.

 

You’re in my personal bubble: Buffers…

One important concept that we will leverage is the ability to create buffers (STBuffer) around shapes (buffers in turn become shapes). Buffers allow you to create borders of a certain distance around a shape. This capability allows you to ask questions akin to “is anything within 1 mile of my current location?”

To learn more about Spatial...

For a more substantive introduction to Spatial data types please refer to Isaac Kunen’s blog and to Ed Katibah’s blog.

 

Implementing the SQL Server Device Location History Table

Now that we have the basic concepts for spatial down, we’ll go ahead and create a new SQL Server database that will “mirror” the data we collect from the WiE Mobile Client and store in SQL Data Services. We need to bring the data stored in SQL Data Services over to SQL Server in order to apply our spatial rules.

The key tables are the similar to those we implemented for the SQL Compact database (See Part III) and for the entities in SQL Data Services (See Part IV):

Table

Description

tabDeviceLocationHistory

Table holding all collected location information.

tabDevice

Table holding a record for each device reporting GPS and location information

tabMember

Table holding a record for each member of the community.

 

One key change will be the use of the GEOGRAPHY POINT type in tabDeviceLocationHistory table to represent the member device’s location rather than using two floats to keep track of longitude and latitude.

CREATE TABLE [dbo].[tabDeviceLocationHistory](

      [guidLocationHistoryID] [uniqueidentifier] NOT NULL,

      [geoLocation] [geography] NOT NULL,

      [flSpeed] [float] NULL,

      [flHeading] [float] NULL,

      [flAltitudeWRTSeaLevel] [float] NULL,

      [flAltitudeWRTEllipsoid] [float] NULL,

      [nNumSatellites] [int] NOT NULL,

      [dtTimeCollected] [datetime] NOT NULL,

      [guidDeviceID_FK] [uniqueidentifier] NOT NULL,

      [guidMemberID_FK] [uniqueidentifier] NOT NULL,

  CONSTRAINT [PK_tabDeviceLocationHistory] PRIMARY KEY CLUSTERED

  (

      [guidLocationHistoryID] ASC

  )WITH (PAD_INDEX  = OFF,

         STATISTICS_NORECOMPUTE = OFF,

         IGNORE_DUP_KEY = OFF,

         ALLOW_ROW_LOCKS = ON,

         ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

I mentioned earlier that we will look at two examples, the first of these rules being finding the proximity of a member’s current location to a list of well known points of interests. For this example we leveraged a database of known points of interest (unfortunately I can’t share that database). For your own solutions you would likely have a table or collection of POI specific to your application or enterprise.

Here is a subset of the schema for a POI table:

CREATE TABLE [dbo].[tabUS_POI](

      [ID] [int] IDENTITY(1,1) NOT NULL,

      [ENTITYID] [nvarchar](255) NULL,

      [DESCRIPTION] [nvarchar](255) NULL,

      [DESCNAME] [nvarchar](255) NULL,

      [geog] [geography] NULL,

PRIMARY KEY CLUSTERED

(

      [ID] ASC

)WITH (PAD_INDEX  = OFF,

       STATISTICS_NORECOMPUTE = OFF,

       IGNORE_DUP_KEY = OFF,

       ALLOW_ROW_LOCKS = ON,

       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

 

As an optimization for querying the most recent location of a member we augment the tabDevice table to store the “most recent” location for a device and we will keep this information up to date using a trigger on the tabDeviceLocationHistory table.

CREATE TABLE [dbo].[tabDevice](

      [guidDeviceID] [uniqueidentifier] NOT NULL,

      [guidMemberID_FK] [uniqueidentifier] NULL,

      [dtClientStarted] [datetime] NULL,

      [dtClientHeartbeat] [datetime] NULL,

      [dtLastLocationDateCollected] [datetime] NULL,

      [geoLastLocation] [geography] NULL,

  CONSTRAINT [PK_tabDevice] PRIMARY KEY CLUSTERED

  (

      [guidDeviceID] ASC

  ) WITH (PAD_INDEX  = OFF,

       STATISTICS_NORECOMPUTE  = OFF,

       IGNORE_DUP_KEY = OFF,

       ALLOW_ROW_LOCKS = ON,

       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

For completeness here is the tabMember table:

CREATE TABLE [dbo].[tabMember](

      [guidMemberID] [uniqueidentifier] NOT NULL,

      [strFirstName] [nvarchar](100) NOT NULL,

      [strLastName] [nvarchar](100) NOT NULL,

      [strEmail] [nvarchar](100) NULL,

      [strPhoneNumber] [nvarchar](20) NOT NULL,

      [strUserName] [nvarchar](100) NOT NULL,

      [strUserPassword] [nvarchar](100) NOT NULL,

 CONSTRAINT [PK_tabMember] PRIMARY KEY CLUSTERED

 (

      [guidMemberID] ASC

 )WITH (PAD_INDEX  = OFF,

        STATISTICS_NORECOMPUTE = OFF,

        IGNORE_DUP_KEY = OFF,

        ALLOW_ROW_LOCKS = ON,

        ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

SQL Server 2008 Triggers + Spatial = Rules Engine

Now that we have our tables defined we need some event to wake us up to evaluate our spatial rules. I presume that most of you are already familiar with creating triggers on tables for the purposes of evaluating validation logic or simple data cleansing. I’ll be leveraging triggers to implement our “business logic” for spatial rules.

I am only interested in looking at newly inserted location records, so we’ll create an INSERT trigger on the tabDeviceLocationHistory table:

CREATE TRIGGER [dbo].[trigOnNewLocation]

ON [dbo].[tabDeviceLocationHistory]

AFTER INSERT AS

BEGIN

      -- A new location record was just inserted in the database,

      -- use this trigger to evaluate any spatial rules we want

      -- evaluated whenever we receive a new location

      -- record.

     

      -- The location record will typically include location, speed

      -- and heading (although location is the only "guaranteed"

      -- information. You might want to use speed and distance

      -- from previous location as a mechanism to minimize

      -- triggering of alerts.

      DECLARE @guidMemberID uniqueidentifier

      DECLARE @guidDeviceID uniqueidentifier

      DECLARE @geoLocation geography

      DECLARE @flSpeed float

      DECLARE @dtTimeCollectedUTC datetime

     

      -- Retrieve the newly inserted data so we can evaluate some

      -- rules against it

      SELECT

      @guidMemberID = guidMemberID_FK,

      @guidDeviceID = guidDeviceID_FK,

      @geoLocation = geoLocation,

      @flSpeed = flSpeed,

      @dtTimeCollectedUTC = dtTimeCollected

      FROM inserted;

. . .

 

We now have the “plumbing” in place to evaluate some rules whenever a new location record is inserted into the tabDeviceLocationHistory table.

Rule #1: Let me know of Points of Interest near my current location

In Spatial-Speak: Create a buffer around my current location of 1,000 meters and tell me all the points of interest that intersect my buffer, select the first

-- Create a buffer of 1,000 meters around my current

-- location (@geoLocation)

DECLARE @geoBuffer GEOGRAPHY

SET @geoBuffer = @geoLocation.STBuffer(1000)   

-- Now Query the POI table for all POIs intersected

-- by my buffer

SELECT TOP 1 @poiName = DESCNAME

FROM tabUS_POI WITH (index=geom_sidx)

WHERE geog.STIntersects(@geoBuffer) = 1;

-- Trace: Display the resulting match (if any)

IF @poiName IS NOT NULL

   BEGIN

      SELECT 'You are near ' + @poiName + '.';

   END

 

Rule #2: Let me know if other members are near my current location

This rule is very similar to rule #1, there is however one additional complexity in that while POI are fixed other members tend to move around, so it becomes important to take the “current time” into account when comparing location between members.

In Spatial-Speak: Create a buffer around my current location of 1,000 meters and query the location history table for all locations from other members that occurred around the same time as this location that intersect the buffer.

We now leverage the INSERT trigger to update the most recent location information before evaluating the rule.

-- UPDATE the tabDevice table to reflect this latest

-- location in case we have not recently synchronized

-- the tabDevice table, we use the tabDevice table to

-- find other members that are "nearby".

UPDATE tabDevice

SET

      dtLastLocationDateCollected = @dtTimeCollectedUTC,

      geoLastLocation = @geoLocation

WHERE

      (guidDeviceID = @guidDeviceID) AND

      ((dtLastLocationDateCollected IS NULL) OR

      (dtLastLocationDateCollected<@dtTimeCollectedUTC))

 

With this change we can be fairly certain that the tabDevice table will have the most recent location information for each device and we can go ahead and use that table in our rule implementation.

-- LOOK UP the name of the closest member (exluding current member)

-- that is/was nearby around the same time (within 5 minutes)

-- as this member's current location.

DECLARE @strClosestMember nvarchar(100)

SELECT TOP 1 @strClosestMember = (tabMember.strFirstName + ' '

                                     + tabMember.strLastname)

FROM tabDevice INNER JOIN

      tabMember

            ON tabDevice.guidMemberID_FK=tabMember.guidMemberID

WHERE

      (tabDevice.geoLastLocation.STIntersects(@geoBuffer) = 1) AND

      (tabMember.guidMemberID <> @guidMemberID) AND

      (ABS(DATEDIFF(minute,tabDevice.dtLastLocationDateCollected,@dtTimeCollectedUTC))<5)

-- Trace: Display the resulting match (if any)

IF @ strClosestMember IS NOT NULL

   BEGIN

      SELECT 'You are near ' + @ strClosestMember + '.';

   END

 

Notifying Members of their Spatial Results

Now that we have implemented the rules and have retrieved the POI and closest member we use DBMail to generate an e-mail alert. The member table holds the e-mail address for each member,

--

-- SEND AN E-mail or Text Message to alert the member of his

-- or her current location and proximity to another member.

--

-- "You are near McDonalds in Redmond, WA (King County) and

-- you are closest to John Smith."

--

DECLARE @strMessage VARCHAR(255)

SELECT @strMessage = 'You are '

     

IF @poiName IS NOT NULL

   BEGIN

      SELECT @strMessage = @strMessage + 'near ' + @poiName + '. ';

   END

           

IF @strClosestMember IS NOT NULL

   BEGIN

      SELECT @strMessage = @strMessage +

            'You are closest to ' + @strClosestMember + '. ';

   END

           

-- Retrieve the member's e-mail or cell phone contact information and

DECLARE @strEmail VARCHAR(255)

SELECT @strEmail = strEmail FROM tabMember WHERE guidMemberID=@guidMemberID

     

-- Generate the e-mail notification

EXEC msdb.dbo.sp_send_dbmail

     @profile_name = 'DBMail',

     @recipients = @strEmail,

     @body = @strMessage,

     @subject = 'WiE Alert';

Using SSIS to synchronize data from SDS to SQL Server

We’ve already covered one synchronization mechanism in Part V of the series. We could have used a similar approach to synchronize data from SQL Data Services (SDS) to SQL Server by building an IWiEModel implementation that “speaks SQL” and synchronizing between the SDS Model implementation and the SQL Server implementation.

I however chose a slightly different approach in order to illustrate some of the flexibility and options available from the Microsoft stack and hopefully add a couple tools to your toolbox. I chose to attempt to build a simple synchronization workflow using SSIS. SQL Server Integration Services provides a very complete and powerful environment for building ETL processes and in some ways synchronization is really an ETL process.

SQL Data Services Providers for SSIS

There is a CodePlex project that provides a set of SQL Data Services components for SSIS including an SDS Source, SDS Target and SDS Connection provider. These are the key components required for data flow tasks in SSIS. To download these components visit the project site.

What’s Next

In next week’s article I’ll cover the implementation of the SSIS package that leverages the SDS SSIS providers to build a simple synchronization mechanism between SDS and SQL Server. The SSIS package will query SDS, retrieve all newly inserted locations from SDS and then insert them into the SQL Server databases (in turn triggering the spatial rules discussed in this week's article).

Have a great week, and see you at PASS.

Olivier