Look Ma! No GIS!

Before you light’ em torches, let me tell you: Not really, no. But the following are a couple of posts I wrote on LinkedIn explaining why a “GIS-only” approach is not ideal when it comes to Linear Referencing and Transportation networks. So, for your reading pleasure:

When GIS is just not enough – Part 1

When GIS is just not enough – Part 2

And the Tech Talk video



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 (https://bitbucket.org/geographika/sql-server-spatial-tools) 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.