More linear referencing functions for SQL Server 2008

Following Geographika’s LRS add-ons for  SQL Spatial Tools, I have added a new function to locate the closest point along a geometry LineString from a given point. The function called  LineLocatePoint, is the equivalent of PostGIS ST_line_locate_point function. It will return a number between 0 and 1, representing the location of the closest point along a geometry LineString to the input point. In other words, “projecting” the input point to the line. You can use this function in conjunction with the existing LocateAlongGeom function to retrieve the actual point geometry. This is a schematic of how this function works:


Thanks to Seth, a.k.a. Geographika, I have uploaded the updated Spatial Tools code to the BitBucket repository ( along with the register and unregister scripts.

To test the function use the following script:

select dbo.LineLocatePoint(geometry::STGeomFromText(
                  'LINESTRING (411714.523521 4493882.696417, 
                 411737.898344 4493862.196547,
                411753.991966 4493838.696703, 411759.616915 4493820.696827,
                411753.179452 4493807.696923, 411740.179536 4493794.697023)',0) 
             ,geometry::STGeomFromText('POINT (411740.179536 4493794.697023)',0))

One important caveat: If for some reason the point cannot be projected onto the line, the function will return a nice, clean 0.5 value i.e.. the mid-section. If you get 0.5, you are either very lucky, OR (more likely) there was some kind of error or division by 0 due to the way the line and point are located in relation to each other. I did this on purpose since I use this function for some address geocoding routines where I always want a value returned.


Sterling Database for Windows Phone 7, Silverlight and Bing Maps

A bit of a mouthful of a title you may think, but this post should hopefully show you just that. How to create your first Bing Maps enabled, Windows Phone 7 application using Silverlight, and the lovely little database for Windows Phone 7, called Sterling. Best of all, everything you will need for this project is free!

The application will display a Bing Map on your phone, load some POI data in, and then allow you to search for POIs and pan and zoom the map on that location. Think of it as the prototype for a mobile ‘City Guide’ application.

So lets get started. The first bits you will need:

  • Visual Studio 2010 Express
  • Windows Phone Emulator
  • Silverlight 4
  • .NET Framework 4

You can download all this from the Windows Phone Development Site at:

Unless you already own a Windows 7 phone, my guess is that you will do most of the work in the emulator. One important note to make is that the emulator does not work (either at all or not well) on Virtual Machines. It took me a while to figure this out so beware!

Windows Phone 7 does not have a native embedded database so I used this little gem of a database called Sterling written by Jeremy Likness. Download the latest Sterling and extract the zip file to a folder of your choice.

Have a play with the sample project by opening the SterlingPhoneExample.sln solution in Visual Studio under the SterlingDB\src\SterlingSln folder (This will also build the Wintellect.Sterling.WindowsPhone.csproj which we will then reference in our project).

In order for the map control to work correctly, you will also need a Bing Maps Key. Click here for more information.

We are now ready to start our project.

  1. Launch Visual Studio 2010 Express for Windows Phone from the Windows Start menu.

  2. Create a new project by clicking the File | New Project menu command.

  3. The New Project window will be displayed. Select the Windows Phone Application template. Name your project PoiFinderSample.image

  4. Click OK. A new project will be created and MainPage.xaml will be opened in the Visual Studio designer window.


5. Add a reference to Wintelect.Sterling.WindowsPhone.dll by navigating to \SterlingDB\src\SterlingSln\Wintellect.Sterling.WindowsPhone\Bin\Debug folder.

6. Add a reference to Microsoft.Phone.Controls.Maps.dll, System.Xml.Linq and System.Device.dll which should be available as .NET assemblies.

7. Replace the XAML code with the following:

    mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
    FontFamily="{StaticResource PhoneFontFamilyNormal}"
    FontSize="{StaticResource PhoneFontSizeNormal}"
    Foreground="{StaticResource PhoneForegroundBrush}"
    SupportedOrientations="Portrait" Orientation="Portrait"
assembly=Microsoft.Phone.Controls.Maps"> <!--LayoutRoot is the root grid where all page content is placed--> <Grid x:Name="LayoutRoot" Background="Transparent"> <Grid.RowDefinitions> <RowDefinition Height="Auto" /> <RowDefinition Height="*" /> </Grid.RowDefinitions> <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28"> <TextBlock x:Name="ApplicationTitle" Text="Bing Map Demo" Style="{StaticResource PhoneTextNormalStyle}" /> <TextBlock x:Name="PageTitle" Text="POI Finder" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}" /> </StackPanel> <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0"> <Grid.RowDefinitions> <RowDefinition Height="150"></RowDefinition> <RowDefinition Height="*"></RowDefinition> </Grid.RowDefinitions> <Grid x:Name="ctls" Grid.Row="0"> <Grid.ColumnDefinitions> <ColumnDefinition></ColumnDefinition> <ColumnDefinition></ColumnDefinition> </Grid.ColumnDefinitions> <TextBox Name="txbPoi" Width="400" HorizontalAlignment="Left" VerticalAlignment="Center"></TextBox> <Button Name="Test" Content="Find" Width="100" Height="90" Grid.Column="1" /> </Grid> <my:Map Name="map1" Width="Auto" Height="Auto" Grid.Row="1"
VerticalAlignment="Stretch" HorizontalAlignment="Stretch"
CredentialsProvider="<put your bing maps key here>" /> </Grid> </Grid> </phone:PhoneApplicationPage>

8. Compile and run the application. You should be presented with the following:


It doesn’t do much currently but the idea is that you will enter a POI name in the textbox, and then press the [Find] button to pan and zoom to that POI.

9. The next thing we will need to do is get some sample POIs, and in this example I used an XML which contains the POI definition. I took the data from an SQLServer 2008 table by running the following statement. Feel free to skip this step if you don’t have anything similar  the XML output from the query  is defined in the code behind for this sample:





  FROM [dbo].[Pois]

  WHERE Lat is not null

  and Lon is not null


10. We then need to create our POI class. To do this create a new class, called POI.cs and copy/paste this code:

using System;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Ink;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

namespace PoiFinderSample


    public class Poi


        public int PoiID { get; set; }   
        public string PoiName { get; set; }

        public double PoiLat { get; set; }

        public double PoiLon { get; set; }



11. Next, we need to create the required by Sterling database class. Call this class PoiFinderDB:

using System;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Ink;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

using Wintellect.Sterling.Database;

namespace PoiFinderSample


    public class PoiFinderDB : BaseDatabaseInstance


        public const string POINAME_INDEX = "POINAME";

        public override string Name


            get { return "PoiFinderDatabase"; }


        protected override System.Collections.Generic.List<ITableDefinition> _RegisterTables()


            return new System.Collections.Generic.List<ITableDefinition>


               CreateTableDefinition<Poi, int>(p => p.PoiID).WithIndex<Poi, string, int>(POINAME_INDEX, p => p.PoiName)





Note here that I created the database defining my POI class as a table, where the key is the PoiID column and has an index on the PoiName column. For more information on Sterling’s keys, indexes and tables refer to the comprehensive Sterling Users Guide
11. We now turn to the code behind to bring this all together.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

using Microsoft.Phone.Controls;

using Wintellect.Sterling;

using Microsoft.Phone.Controls.Maps;

using System.Xml.Linq;

using System.IO;

using System.Device.Location;

namespace PoiFinderSample


    public partial class MainPage : PhoneApplicationPage


        private SterlingEngine _engine;

        private ISterlingDatabaseInstance _databaseInstance;

        private List<Poi> _allPois = new List<Poi>();

        public const string poixml = @"<?xml version=’1.0′ encoding=’UTF-8′ ?>




                                        <Name>Azzuro Bar</Name>












                                        <Name>The Green Bottle</Name>












                                        <Name>St. Paul</Name>
























                                        <Name>The Shop</Name>






                                        <Name>Disc Shop</Name>





        // Constructor

        public MainPage()



            Loaded += new RoutedEventHandler(MainPage_Loaded);


        private void MainPage_Loaded(object sender, RoutedEventArgs e)


            Test.Click += new System.Windows.RoutedEventHandler(Query_Click);




        private void StartSterling()


            _engine = new SterlingEngine();


            _databaseInstance = _engine.SterlingDatabase.RegisterDatabase<PoiFinderDB>();


        private void DisplayPoisFromXml(string xmlPois)


            var xmlrdr = new StringReader(xmlPois);

            var doc = XDocument.Load(xmlrdr);

            // Populate the POIS list

            _allPois = (from pointrec

                           in doc.Element("ALLPOIS").Elements("POIS")

                        select new Poi


                            PoiID = Convert.ToInt32(pointrec.Element("PoiID").Value),

                            PoiName = pointrec.Element("Name").Value,

                            PoiLat = Convert.ToDouble(pointrec.Element("Lat").Value),

                            PoiLon = Convert.ToDouble(pointrec.Element("Lon").Value)


            //Loop through the list to save the Pois into Sterling

            // and display them in the map

            for (var idx = 0; idx < _allPois.Count – 1; ++idx)


                Poi thisPoi = _allPois[idx];

                var key = _databaseInstance.Save(thisPoi);

                // Create a pushpin

                Pushpin pin1 = new Pushpin();

                //Create POI location

                GeoCoordinate thisLoc = new GeoCoordinate(thisPoi.PoiLat, thisPoi.PoiLon);

                pin1.Location = thisLoc;

                pin1.Content = thisPoi.PoiName;

                //Add pin to the map



            //Flush to storage


            //Zoom the map

            GeoCoordinate mapCenter = new GeoCoordinate(40.6293172201, 22.9462402344);

            map1.SetView(mapCenter, 12);


        private void Query_Click(object sender, RoutedEventArgs e)


            if (txbPoi.Text != string.Empty)


                var q1 = _databaseInstance.Query<Poi, string, int>(PoiFinderDB.POINAME_INDEX).Where(p => p.Index == txbPoi.Text).FirstOrDefault();

                if (q1 != null)


                    //Get POI location and zoom the map

                    GeoCoordinate poiloc = new GeoCoordinate(q1.LazyValue.Value.PoiLat, q1.LazyValue.Value.PoiLon);

                    map1.SetView(poiloc, 18);






12. You should be all done. Compile and run the project. The map should zoom somewhere around downtown Thessaloniki, Greece displaying all 10 POIs. Now enter the name of any Poi in the text box and press [Find]. The map should zoom to that point (e.g. try ‘Everest’) as shown below. A final note to remember:  queries are case-sensitive so for example ‘everest’ will not return any results.



You can download the full source code from the Box.Net  widget on the left (PoiFinderSample.rar)

SQL Server 2008 Spatial and Oracle Spatial comparison and cheat sheet

This is a brief comparison between SQL Server 2008 and Oracle’s spatial functions along with relevant links and simple examples. It is by no means exhaustive but it should help people when migrating.

Note that all Oracle examples and links here refer to the 10g R2 release.

UPDATED: Added link for updated version of SQLSpatialTools from Geographica.

Spatial Data

Feature Oracle 10g SQL Server 2008
Spatial Data types

Defined by implementing the SDO_GEOMETRY type:

CREATE TYPE sdo_geometry AS OBJECT (

Defines two types of spatial data depending whether they contain geographic (round-earth coordinate system) or geometric (Euclidean/flat coordinate system) data: Geometry and Geography. Both types are implemented as a .NET common language runtime (CLR) data types.

Spatial Tables Statement to create a spatial table:
  shape SDO_GEOMETRY);

Statement to create a spatial table (geometry):

CREATE TABLE sp_table 
(sp_id int IDENTITY (1,1), shape geometry);

Statement to create a spatial table (geography):

CREATE TABLE sp_table 
(sp_id int IDENTITY (1,1),shape geography);

Geometry Metadata For spatial tables to perform correctly, Oracle requires the update of the geometry metadata views. These views describe the dimensions, lower and upper bounds, and tolerance in each dimension for each spatial table and are stored in a global table owned by the MDSYS user.

The main view is USER_SDO_GEOM_METADATA which contains metadata information for all spatial tables owned by the user (schema).

Example statement to insert data into the USER_SDO_GEOM_METADATA view:

INSERT INTO user_sdo_geom_metadata
SDO_DIM_ARRAY(    SDO_DIM_ELEMENT(‘X’, 0, 20, 0.005), 
SDO_DIM_ELEMENT(‘Y’, 0, 20, 0.005)), NULL);

SQL Server does not have an equivalent of Oracle’s geometry metadata views
Spatial Indexes Supports R-tree and Quadtree indexing. However, Oracle states that:

the use of quadtree indexes is discouraged, and you are strongly encouraged to use R-tree indexing

For more information about Oracle’s spatial indexing click here.

Creating a spatial (R-tree) index:

CREATE INDEX sp_table_spidx   ON sp_table(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Uses B-tree indexing. For more information on how spatial indexing is handled, click here.

Creating spatial indexing syntax varies depending whether the column is of type Geometry or Geography.

Creating a spatial index on a Geography column:

CREATE SPATIAL INDEX sp_table_spidx    ON sp_table(shape);

Creating a spatial index on a Geometry column:

CREATE SPATIAL INDEX sp_table_spidx ON sp_table(shape) WITH (
BOUNDING_BOX = ( 0, 0, 500, 200 ), 

More examples can be found here

OGG Compliance Conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0. Conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0

Spatial object


More geometry examples here

(Notice that 4326 is the SRID)

1. Using the SDO_GEOMETRY type:

insert into sdo_points (sp_id, shape)
values (1, SDO_GEOMETRY(
      SDO_POINT_TYPE(40, 22, NULL),  NULL,   NULL));

2. Using the Well-Known text (WKT) syntax:


(Notice that 4326 is the SRID)

1. Using the Well-Known text (WKT) syntax (STPointFromText function):

INSERT INTO GeomTable (geom)

VALUES (geography::STPointFromText(‘POINT(40 22)’, 4326))

2. Using the geometry::Point or geography::Point syntax:

INSERT INTO GeogTable (geog) VALUES (geography::Point(40, 22, 4326))

3. Using the GML syntax (GeomFromGml function)

INSERT INTO GeomTable (geom)
     VALUES (geography::GeomFromGml(
‘<Point xmlns="">
<pos>40 22</pos></Point>’
, 4326))

       Simple Lines

Using the SDO_GEOMETRY type:

    SDO_ORDINATE_ARRAY(411392.088118, 4493608.698554, 411436.431582, 4493649.698236)));

(You can also create line geometries using the WKT syntax as above)

Using the Well-Known text (WKT) syntax (STLineFromText function):

INSERT INTO GeogTable (geog)
VALUES (geography::STLineFromText(‘LINESTRING(-122.360 47.656,
-122.343 47.656 )’, 4326));

(You can also create line geometries using the WKT and GML syntax as above)

       Simple Polygons

Using the SDO_GEOMETRY type:

2003, — 2-dimensional polygon
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), — one polygon (exterior polygon ring)
MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)));

(You can also create polygon geometries using the WKT syntax as above)

Using the Well-Known text (WKT) syntax (STPolyFromText function):

INSERT INTO GeogTable (geog) VALUES (geography::STPolyFromText(‘POLYGON((-122.358 47.653,
-122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))’
, 4326))

(You can also create polygon geometries using the WKT and GML syntax as above)

3D/4D support

Both DBs support 3D and 4D (X,Y,Z,M) shapes

Loader and conversion  Tools Oracle makes available for download from its website two utilities to convert shapefile to oracle spatial tables. The first one is a command line utility shp2sdo.exe,and the second a Java shapefile converter. You can find more information about these utilities here. There is a free utility called Shape2Sql available from the SharpGIS website

Spatial Functions

The table list some commonly used spatial functions.

Function Description Oracle 10g

Spatial Functions see also the
SDO_GEOM package
SQL Server 2008

All Geography functions
All Geometry functions

Retrieve the spatial extent of a given table(Minimum Bounding Rectangle (MBR)) Use the SDO_AGGR_MBR function, e.g.:

SELECT SDO_AGGR_MBR(shape) FROM cola_markets;

Does not contain a similar function in the standard install. You will have to use the GeographyUnionAggregate and GeometryEnvelopeAggregate functions available in the SQL Server Spatial Tools CRL package available from Codeplex.

select state, dbo.
GeometryEnvelopeAggregate(shape_geom) from zipcodes

Identify objects within a distance

Use the SDO_WITHIN_DISTANCE function e.g.:

    SDO_ORDINATE_ARRAY(4,6, 8,8)),
  ‘distance=10’) = ‘TRUE’;

Use the STDistance (Geography) or STDistance (Geometry) e.g.:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText(‘POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))’, 0);
SET @h = geometry::STGeomFromText(‘POINT(10 10)’, 0);
SELECT @g.STDistance(@h);

Create a buffer around a spatial object Use the SDO_GEOM.SDO_BUFFER function e.g.:

SELECT, SDO_GEOM.SDO_BUFFER(c.shape, m.diminfo, 1)
  FROM cola_markets c, user_sdo_geom_metadata m
  WHERE m.table_name = ‘COLA_MARKETS’  AND m.column_name = ‘SHAPE’
  AND = ‘cola_a’;

Use the STBuffer (Geography) or STBuffer (geometry) e.g.:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText(‘LINESTRING(0 0, 4 0)’, 0);
SELECT @g.STBuffer(1).ToString();

Get the length of an object Use the SDO_GEOM.SDO_LENGTH function, e.g.:

SELECT, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo)
  FROM cola_markets c, user_sdo_geom_metadata m
  WHERE m.table_name = ‘COLA_MARKETS’ AND m.column_name = ‘SHAPE’;

Use the STLength (Geography) or STLength (Geometry) e.g.:


DECLARE @g geography;
SET @g = geography::STGeomFromText(‘LINESTRING(-122.360 47.656, -122.343 47.656)’, 4326);
SELECT @g.STLength();

Get the area of a polygon   Use the STArea (Geography) or STArea (Geometry) e.g.:

DECLARE @g geography;
SET @g = geography::STGeomFromText(‘POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))’, 4326);
SELECT @g.STArea();

Check the validity of an object Use the SDO_GEOM. VALIDATE_GEOMETRY_WITH_CONTEXT function e.g.:

   FROM cola_markets c WHERE = ‘cola_invalid_geom’;

Use the STIsValid (Geometry) e.g.:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText(‘LINESTRING(0 0, 2 2, 1 0)’, 0);
SELECT @g.STIsValid();

The Geography data type does not –for reasons unknown to me- include a STIsValid function.

Linear Referencing Contains a large number of functions for linear referencing. Lots of examples can be found here SQL Server does not support any true Linear referencing functions. SQL Server Spatial Tools however, provides some rudimentary linear referencing functions such as LocateAlongGeog and LocateAlongGeom which will return the point at a given instance along a linear object.
UPDATED: Geographica provides an updated version of SQLSpatialTools which includes a new function to display a linear event as well (a “LocateLineAlongGeom” function but called CreateLinearReferenceFeature. More details can be found at this address:

Create point geometries dynamically using dynamic segmentation in SQLServer 2008

UPDATED: Change trigger to allow for multiple linear geometries

I have started experimenting with the new spatial functionality in SQL Server 2008. Having used Oracle Spatial for a quite a few years this is my first attempt with SQL Server so my apologies in advance if there is an easier way of doing this! 🙂

So the scenario is as follows:- Consider you have a layer containing A SINGLE multiple linear geometries with the following structure called ROUTES:


The structure can be different- for now all we care about is the [Shape] and [ID] fields.

We also have another table called EVENTS- this is the table we will be updating using the trigger. The table structure is:


Where ROUTE_ID is the ID the Route the event is  located along, (as a FK to ID field in the ROUTES table) and  START_MP is the distance from the start of the Route.

The task at hand was to create a trigger so any changes in the START_MP value would update the shape column in the EVENTS table, creating a Point geometry at that location.

That was easy enough using the SQL Server Spatial tools available from After registering the SQLSpatialTools.dll in my database I was able to use the LocateAlongGeom function in a trigger which would deliver the point geometries. The trigger source is:

ALTER TRIGGER [dbo].[upd_shape_trg]
   ON  [dbo].[EVENTS]

  DECLARE @lineshape geometry
    DECLARE @routeid int
    --SELECT @lineshape = r.shape from marathonia_diadromi_spatial r
    --, events e
    --where e.route_id
    SELECT @routeid=route_id from deleted
    SELECT @lineshape = shape from marathonia_diadromi_spatial where id=@routeid
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
   UPDATE [mpoint].[dbo].[EVENTS]
   SET [SHAPE] = [mpoint].[dbo].[LocateAlongGeom](@lineshape, start_mp).ToString()


Note that right now this would only work if the ROUTES table contains a single linear geometry. As said- first attempt only this! More later…