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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s