SQL 2008 - Ways to architect Spatial apps

项目
11/02/2008

Adding spatial capability to your app might be simpler than you think. This is not a comprehensive coverage on architecture, but rather just a few ideas to stimulate your thinking.

Overview

In an N-Tier environment you have one of 3 options :-

All spatial processing occurs on the Server(s) - Mid-Tier &/or Backend.
All spatial processing occurs in the client.
Both Client & Servers are spatially aware.

This post concentrates on Option 1. What a DBA can do to add spatial intelligence into an application with minimal changes to your app design. A few stored procedures & away you go.

1. Spatial capability only on Server

Concept: The application does not deal with Geometry / Geography data types.

The rows passed back from the server may be the result of a query that included some spatial aspect.
If the app needs to show points, the X,Y values are passed as 2 floating point numbers.

Use examples

List all cities within 50km from here.
List the phone number of the nearest Petrol Station (that delivers fuel :-) ).
List phone numbers to all Video Stores in this suburb & any suburbs adjacent to this one.
List Homes for sale in suburb ‘S’ & adjoining suburbs.
- List HousesForSale within 5km of Train, 2km near primary school & 10km Shops in NorthRegion
List the distances between this point (my customer) & our 3 closest repair centres.

Advantage

Doesn't use any new technology. So works with everything:-
- Mobiles/PDA’s (Compact Framework),
- SQL CE, older technologies (ODBC drivers)
- Merge Replication &/or Sync Services.
Ideal for putting spatial apps on mobile phones. Necessary as SQL's Spatial Library is not available for the Compact Framework yet.

Disadvantage

Can be verbose.
- Fine when dealing with a few Points.
- Performance might suck if app requires passing many Shapes each comprising of 1,000’s of points. You can use WKT for this, but as you'd still have to parse them, it would be easier to send as WKB & write you own parsing routines (or rethink your design)

Example 1: Pass X,Y as Numbers

App calls a stored procedure with an two floating point numbers & returns the names of the cities closest to that point. NB: This proc depends on you having a Cities table containing the Long/Lats of all the relevant cities or suburbs. (This code is derived from a sample originally developed by my friend Dr Greg Low for the SQL 2008 Launch Event)

 -- Pass (X,Y) as Float & Returns : Name of nearest City & its X,Y Location
CREATE PROCEDURE [dbo].[LocateNearestCityTo]
   @Latitude float,  @Longitude float
AS
   -- Convert the X & Y numbers into a Geography Point
   DECLARE  @TestLocation AS GEOGRAPHY = GEOGRAPHY::STGeomFromText(
                'POINT (' + CONVERT(varchar(10),@Longitude)  + ' ' 
                + CONVERT(varchar(10),@Latitude) + ')',4326);

    -- Return details of the closest City(s)
   SELECT COALESCE(c.CityName,'') AS CityName
                  ,c.Location.Lat AS ToLat
                  ,c.Location.Long AS ToLong
   FROM dbo.Cities AS c 
   WHERE c.Location.STDistance( @TestLocation) = (
                    -- Find the shortest distance from that point to any city
                    SELECT  MIN(t.Location.STDistance( @TestLocation))
                    FROM dbo.Cities AS t
   );
GO

Example 2: Pass a key as a String, Get back a List

This Proc finds the closest Railway station to the suburb name you pass as a parameter.

Conceptually this shows you could pass a string & get back a set of results. The App developer does not need to know that your stored procedure used the Spatial Methods get the answer.

In Reality – I left the included extra information (numbers) in the results. (a) X,Y of Start point, (b) X,Y of RailStation & (c ) Distance between them. This information could be passed to Virtual Earth or some other Map Display to plot the locations & perhaps draw a line between them.

 CREATE PROCEDURE [dbo].[FindStationNearestTo] @Suburb varchar(100)
AS
   -- Lookup the location of the suburb called @Suburb
    DECLARE @TestLocation GEOGRAPHY;
    SELECT @TestLocation = Location
    FROM dbo.Cities 
    WHERE CityName = @Suburb;

SELECT COALESCE(c.CityName,'') AS CityName, 
     COALESCE(c.Location.STDistance(@TestLocation) / 1000.0,0) AS Distance,
     @TestLocation.Lat AS FromLat,      @TestLocation.Long AS FromLong,
      c.Location.Lat AS ToLat,     c.Location.Long AS ToLong 
FROM dbo.Cities AS c 
WHERE c.Location.STDistance(@TestLocation)
               = (SELECT MIN(t.Location.STDistance(@TestLocation))
                  FROM dbo.Cities AS t
                  WHERE t.IsRailStop = 1
                 );

Example: Windows Mobile Application

It is easy to write .NET Compact Framework applications that talk to a Mobile GPS enabled device. At present, the Microsoft Spatial Types are not part of the Compact Framework, nor are they included in SQL CE 3.5. So how do you create a distributed mobile spatial experience?

Optionally - Use your GPS to track where you are.
Store the Long / Lat data as floating point values in a SQL Server Compact Edition database.
- Either get values from GPS or via central database.
Use Merge Replication or Sync Services to centralise the information with SQL Server 2008 -Express (or higher edition)

Advantage

The Central Database can be “Spatially Aware”. And as nothing else changes this will work with existing infrastructure & applications.
Can leverage existing skills of .NET developers & SQL DBA’s.

Disadvantage

No Spatially Aware calculations are done locally on the mobile device.

Would this work for a commercial application? Sure, this is how ESRI's ArcPad works & lots of people use that.

2. All Spatial processing on the Client

Concept: The map(s) are stored in files with the client. Mainly used for showing where points are located or thematic mapping (colouring in areas) eg: Show suburbs coloured by highest crime rate or Avg Monthly Insurance payout as a percentage of Premiums.

Handy if you aren’t storing your data in a database. You can still have spatial awareness in your app.

3. Spatial is designed into any or all Tiers. (Client & Backend)

Advantage:

Passing as spatial data is more network efficient than passing WKT & Numbers.
Take advantage of the power of SQL brilliant spatial Index’s & the use the client to deliver a rich interactive visual experience.

For code examples see my earlier posts :SQL 2008 Samples

Examples

1. Plotting Data gathered by Mobile users onto a Web Display.

Using Virtual Earth is often cheaper than buying highly detailed data from a professional spatial data supplier & then storing & managing it inside SQL Server.

In this example we are capturing input from mobile & desktop devices then using a web interface to render the data. A potential solution for both desktops & the growing number of connected mobile devices (Laptops & Phones)

There are a ton of good articles written on using Virtual Earth with SQL Server, so I won't duplicate it here.

Virtual Earth & SQL Server 2008 - Part 1: Introduction (1/2)

Virtual Earth & SQL Server 2008 - Part 1: Introduction (2/2)

Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (1/3)

Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (2/3)

Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (3/3)

Virtual Earth & SQL Server 2008 - Part 3: Getting Started with Virtual Earth

Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (1/6)

Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (2/6)

Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (3/6)

Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (4/6)

Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (5/6)

Virtual Earth & SQL Server 2008 - Part 4: Integrating SQL Server 2008 and Virtual Earth (6/6)

Virtual Earth & SQL Server 2008 - Part 5: Conclusion

FindNearRoute with Virtual Earth 6.1 and SQL Server 2008

2. Apps for any UI, Spatial data from SQL, Web or flat file, Business data from any source.

Of course the web is for wimps, and everyone secretly aspires to be rich & powerful. So nothing quite matches the visual power & interactivity of a Rich User Interface. As you can see below, whatever the UI experience, however you want to configure your data sources, it is possible.

(I know that is thin on details. But as you can see it is just an extension of what I've been talking about in other posts & those I've referred to. So you should be good to go)

Thanks

Please give feedback. Is this series of articles useful? Did it save you time? What was good, What could be better?, Notice any errors? What would you like me to cover? All thoughts, comments, suggestions welcome.

Technorati Tags: SQL Server Spatial,Spatial,Virtual Earth

Comments

Anonymous
November 16, 2008
The comment has been removed
Anonymous
January 22, 2009
Hi, is perfect, you have messenger friend? lucassouzace@Hotmail.com i have doubt... thank =)
Anonymous
February 19, 2009
Hi SQL Guru David, I was trying to find sql code that will find the closest bank location for each of our customers and the distance in miles for each customer. I am using SQL server 2005. I have all of the latitudes and longititudes for my bank locations and customer's location. My latitudes and longitudes do not have the decimal. Do you know where I ca find the sql code for use with SQL server 2005?
Anonymous
April 06, 2009
Margarita - if you are still looking for a distance formula, check out this page http://www.xml.lt/Blog/2008/04/22/Calculating+distance+in+SQL which has a simply pythagorean formula plus a more geographically accurate Haversine formula implemented in T-SQL (ie. doesn't require SQL2008's spatial capabilities). there is some useful c# code in this article too http://www.codeproject.com/KB/MCMS/StoreLocator.aspx HTH Craig

通过