Snap Point to SQL Server Spatial geometry using TSQL

STSnapPointToGeom

— Function that snaps supplied point to supplied geometry returning result of ShortestLineTo if within supplied distance.

Function Specification

Function [dbo].[STSnapPointToGeom] (
   @p_point       geometry,
   @p_geom        geometry,
   @p_snap_within float,
   @p_round_xy    int = 3
)
Returns geometry

Description

This function is a wrapper over ShortestLineTo.

Given a point and a geometry the function computes the shortest distance from the point to the distance.

If that distance is < a user supplied @p_snap_within distance, the snap point is returned. If the distance is > the user supplied @p_snap_within distance the original point is returned.

The function rounds each ordinate using the supplied rounding factor.

Notes

Supports CircularString geometries only.

If @p_point does not fall on CircularString, null is returned.

If @p_point is the same as the centre of the circle formed by the CircularString, the first point in the CircularString is returned.

Parameters

    @p_point    (geometry) - The point the caller wants snapped to @p_geom.
    @p_geom     (geometry) - A geometry the caller wants @p_point snapped to
    @p_snap_within (float) - If the distance from @p_point to the snapped point is less than this value, the snapped point is returned.
    @p_round_xy      (int) - X Ordinate rounding factor.

Result

The original @p_point is returned if it is not within @p_snap_distance, otherwise the snap point is returned.

Examples

select [dbo].[STSnapPointToGeom](
              geometry::STGeomFromText('POINT (2172251.39758337 257358.817891138)',2274),
              geometry::STGeomFromText('LINESTRING(2171796.8166267127 257562.7279690057, 2171785.1539784111 257183.20449278614, 2172044.2970194966 256905.68157368898)', 2274),
              NULL,
              3
       ).STAsText() as GEOM;

GEOM
POINT (2171914.725 257044.443)

Visually this looks like the following.

We can also snap a point to the boundary of a polygon:

select [dbo].[STSnapPointToGeom](
              geometry::STGeomFromText('POINT (3 0)',0),
              geometry::STGeomFromText('POLYGON((-1 -1, 1 -1, 1 1,-1 1, -1 -1))', 0),
              NULL,
              3
       ).STAsText() as geom;

GEOM
POINT (1 0)

However, if the point lies inside the polygon ShortestLineTo (and thus this function) returns NULL.

select [dbo].[STSnapPointToGeom](
              geometry::STGeomFromText('POINT (0 0)',0),
              geometry::STGeomFromText('POLYGON((-1 -1, 1 -1, 1 1,-1 1, -1 -1))', 0),
              NULL,
              3
       ).STAsText() as GEOM;

GEOM
(null)

In this case it is best to use the Exterior Ring of the polygon or convert the polygon in to a (multi)linestring.

select [dbo].[STSnapPointToGeom](
              geometry::STGeomFromText('POINT (0 0)',0),
              geometry::STGeomFromText('POLYGON((-1 -1, 1 -1, 1 1,-1 1, -1 -1))', 0).STExteriorRing(),
              NULL,
              3
       ).STAsText() as GEOM;

GEOM
POINT (0 -1)

Note that this function does not support measured geometries. See the LRS function STProjectPoint() instead.