SQL 2008 - Ways to architect Spatial apps
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.
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.
- 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.
- 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.
- 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)
- 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.
- 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)
- 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
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.
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)
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.