Top 5 Recent Articles
- Algorithms (15)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (3)
- Open Source (18)
- Oracle Spatial and Locator (192)
- PostGIS (34)
- Published Articles (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (35)
- Space Curves (9)
- Spatial Database Functions (107)
- Spatial DB comparison (1)
- Spatial XML Processing (10)
- SQL Server Spatial (General) (84)
- SQL Server Spatial (LRS) (38)
- Standards (1)
- Stored Procedure (15)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
Snap Point to SQL Server Spatial geometry using TSQL
— Function that snaps supplied point to supplied geometry returning result of ShortestLineTo if within supplied distance.
Function [dbo].[STSnapPointToGeom] ( @p_point geometry, @p_geom geometry, @p_snap_within float, @p_round_xy int = 3 ) Returns geometry
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.
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.
@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.
The original @p_point is returned if it is not within @p_snap_distance, otherwise the snap point is returned.
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.