Projecting (snapping) a SQL Server Spatial point to a geometry using TSQL

STProjectPoint

— Returns a measured point by snapping provided point to the provided line.

Function Specification.

Function [lrs].[STProjectPoint] (
                  @p_linestring geometry,
                  @p_point      geometry,
                  @p_round_xy   int   = 3,
                  @p_round_zm   int   = 2
             )
Returns geometry

Description.

This function snaps supplied point to @p_linestring, returning the snapped point.

If there is more than one possible solution the first point is returned.

Computes Z and M values if exist on @p_linestring.

If input @p_linestring is 2D, length from start of @p_linestring to point is returned in M ordinate of snapped point.

Returned points ordinate values are rounded to @p_round_xy/@p_round_zm decimal digits of precision.

Notes.

Supports linestrings with CircularString elements.

Parameters.

    @p_linestring (geometry) - Measured linestring with or without Z ordinates.
    @p_point      (geometry) - Point near to linestring.
    @p_round_xy        (int) - Decimal digits of precision for XY ordinates.
    @p_round_zm        (int) - Decimal digits of precision for M ordinate.

Result.

The function snaps the supplied @p_point to @p_linestring computing XY, Z (if exists) and M ordinate values.

Example.

select CAST('Actual Measure' as varchar(20)) as test,
       [lrs].[STProjectPoint] (
          geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
          geometry::Point(8,8,28355),
          3,2).AsTextZM() as project_point
union all
select '2D return length in measure' as test,
       [lrs].[STProjectPoint] (
          geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355),
          geometry::Point(8,8,28355),
          3,2).AsTextZM() as project_point
GO

test	project_point
Actual Measure	POINT (10 8 NULL 23.44)
2D return length in measure	POINT (10 8 NULL 23.66)