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:
'LINESTRING (411714.523521 4493882.696417,
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.