Linear Referencing in SQL Server 2008

imageLinear referencing is used to define features in relation to existing line features. These new features can either be points or lines. For example a water monitoring station can be defined as 300m along a section of river, or a stretch of road can be defined as requiring repairs, from 220m along to 270m along. Most GISs implement this functionality – for details look at the ESRI and GRASS help pages.

In terms of storing linear referenced point features, you only need three fields – an ID of the original line feature, an ID of the  feature to reference along the line, and a M value – the measurement along the original feature. Line features require two measurement fields, a starting distance, and an end distance.

Dynamic Segmentation

Linear referenced features can be stored in a standard table structure. What is more complicated however is displaying these features again – a process (termed, at least by ESRI) as dynamic segmentation – the process of dynamically splitting, or segmenting, an existing feature. It’s a step up from a standard table join, but essentially has the same purpose of relating two records in a database.

SQL Spatial Tools is an open source collection of functions to manipulate spatial data within SQL Server 2008. It contains a  LocateAlongGeom function that “takes a geometry linestring and finds the point a given distance along it.” So that takes care of displaying linear referenced points. What it lacks at the moment (and can be found in other spatial databases such as PostGIS and Oracle), is a way to dynamically create line features using a distance from and a distance to. Fortunately the SQL datatypes and existing spatial tool functions make this easy to add in.

The Code

The CreateLinearReferenceFeature code can be seen in the Function.cs file. The full project is available for download and modifications in BitBucket at https://bitbucket.org/geographika/sql-server-spatial-tools. As I’m never sure if CodePlex accepts patches or not, I decided to create my own fork of the code. I have uploaded the key files to CodePlex where they will await evaluation. I’d imagine however that there will be a new release of the tools by Microsoft for the new Denali release of SQL Server 2008, so this funciton may have to be merged into a new release in the future.

The SQL Spatial Tools compile to a DLL, and the assembly needs to be registered with the database along with all the associated functions. I modified the original SQL script to include the new functions.  There is also an  unregister.sql script to remove the assembly and functions. A few caveats on the linear refence function:

  • the code has not been tested on the new SQL Server 2008 curve type
  • the project is written in C# and compiled with Visual Studio 2010
  • if you give the function a MeasureTo value greater than the length of the feature you will receive the following System.ArgumentException: “MeasureTo value must be less than the length of the feature.

To test out the functionality you can use the SQL script below. Feedback and comments appreciated!And no need to store hundreds of little line geometries in the same database as your nice clean line networks.



5 views shared on this article. Join in...

  1. Marco Giana says:

    Hi ya,

    how do I down load the code. I went to https://bitbucket.org/geographika/sql-server-spatial-tools but I cannot figure out how to down load the code as there is nothing in the download link.

    Your assistance would be much appreciated.

    Cheers

    Marco

  2. Cristian Andres Galindo says:

    Hello,

    does this function use the M parameter in the line object? or use the geographic length of the line?

    thanks

    • Kurochan says:

      Abi Chapagai / Database Administrators are responsible for miiatainnng and managing the databases of a company and they will have different roles and responsibilites as per the need of the company but most of the DBAs have same kind of role. Some of the traits of good DBA and bad DBA that i know are listed below. Please feel free to add comments on it: Some of the traits of a good DBA are:• Good attitude.• Fast learner• Good at performance tuning.• Good at database backup and restore.• Automate the jobs.• Good at setting up database security.• Continuous education• Good at database design.• Understand the problem domain fast.• Patience• Understand that nature of the data being stored in the database.• Understand the enterprise architecture.• Good at setting up high availability.• Communication• Certifications• Get up to date with the technology.• Do not play with production data.• Understands the Database Engine Architecture• Monitor the database every day.• Maintenance plan expert.• Detail oriented.• Organized• Follows the corporate rules and regulations. Some of the traits of bad DBA:• Do not want to learn.• Not understand the importance of Organization data.• Not interested in latest technology.• Deletes table and data randomly.• Not organized.• Do not follow the corporate policies.



Pings to this post

  1. [...] 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: http://geographika.co.uk/linear-referencing-in-sql-server-2008 [...]


Leave a Reply

Your email address will not be published. Required fields are marked *

Comment

You may use these tags : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>