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.