Posted on

STMulti

STMulti — Function that returns @p_geometry as a MULTI* geometry

Function Specification

Function [dbo].[STMulti] (
           @p_geometry geometry
         )
Returns geometry 

Description

In situations where a geometry column is constrained to hold only the multi version of a base geometry, storage of a single geometry eg LineString requires that geometry to be wrapped as a multi geometry eg MultiLineString.

ALTER TABLE [dbo].[Colony] WITH CHECK
ADD CONSTRAINT [Colony_Geom_Geometry_Type_Ck]
CHECK ([geom] is null OR [geom].[STGeometryType]()=’MultiPoint’);

So, @p_geometry is returned as a MULTI* geometry, e.g., POINT -> MULTIPOINT

If @p_geometry is already a MULTI*, it is returned unchanged e.g., MULTIPOINT -> MULTIPOINT

STMulti, where is wraps the single geometry returns a multi-geometry that contains only 1 geometry, so the SQL Server Spatial function STNumGeometries will return 1.

Parameters

    @p_geometry (geometry) - Supplied geometry of a supported type.

Result

This function always returns a multi-geometry type object.

Notes

A CircularString input geometry is returned as a GEOMETRYCOLLECTION as it cannot be returned as a MUTLICURVE as it is not instantiable.

Additionally, one cannot construct a MULTILINESTRING containing it MUTLILINESTRING(CIRCULARSTRING(….)).

CompoundCurves are not supported because MultiCurve is not instantiable in SQL Server Spatial

Examples

USE GISDB -- Change to your own database.
GO
    
SELECT f.mGeom.AsTextZM() as mGeom, f.mGeom.STNumGeometries() as numGeometries
  FROM (SELECT [dbo].[STMulti](geometry::STPointFromText('POINT(0 0)',0)) as mGeom
         UNION ALL
        SELECT [dbo].[STMulti](geometry::STGeomFromText ('POLYGON ((0 0,10 0,10 10,0 10,0 0))',0)) as mgeom
         UNION ALL
        SELECT [dbo].[STMulti](geometry::STGeomFromText ('LINESTRING(0 0,10 10,20 20)',0)) as mgeom
         UNION ALL
        SELECT [dbo].[STMulti](geometry::STGeomFromText('CIRCULARSTRING(9.962 -0.872,10.1 0,9.962 0.872)',0)) as mgeom
         ) as f;
GO

mGeom                                                                  numGeometries
MULTIPOINT ((0 0))                                                                 1
MULTIPOLYGON (((0 0, 10 0, 10 10, 0 10, 0 0)))                                     1
MULTILINESTRING ((0 0, 10 10, 20 20))                                              1
GEOMETRYCOLLECTION (CIRCULARSTRING (9.962 -0.872, 10.1 0, 9.962 0.872))            1

I hope this function is of use to someone.

Posted on

STRound

STRound — Function which rounds the ordinates of the supplied geometry.

Function Specification

Function [dbo].[STRound] (
           @p_geometry geometry,
           @p_round_xy int = 3,
           @p_round_zm int = 2
         )
Returns geometry 

Description

The result of many geoprocessing operations in any spatial type can be geometries whose ordinates (X, Y etc) have far more decimal digits of precision than the initial geometry.

Additionally, some input GIS formats, such as shapefiles (which has no associated precision model), when loaded, can show far more decimal digits of precision in the created ordinates misrepresenting the actual accuracy of the data.

STRound takes a geometry object and some specifications of the precision of any X, Y, Z or M ordinates, applies those specifications to the geometry and returns the corrected geometry.

The @p_round_xy/@p_round_zm values are decimal digits of precision, which are used in TSQL’s ROUND function to round each ordinate value.

Parameters

    @p_geometry (geometry) - supplied geometry of any type.
    @p_round_xy (int)      - Decimal degrees of precision to which calculated ordinates are rounded.
    @p_round_zm (int)      - Decimal degrees of precision to which calculated ordinates are rounded.

Result

This function accesses all ordinates in the supplied geometry, rounding all ordinates using the supplied parameter values.

Examples

-- Geometry
-- Point
SELECT [dbo].[STRound](geometry::STPointFromText('POINT(0.345 0.282)',0),1,1).STAsText() as RoundGeom
UNION ALL 
-- MultiPoint
SELECT [dbo].[STRound](geometry::STGeomFromText('MULTIPOINT((100.12223 100.345456),(388.839 499.40400))',0),3,1).STAsText() as RoundGeom 
UNION ALL 
-- Linestring
SELECT [dbo].[STRound](geometry::STGeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),2,1).STAsText() as RoundGeom
UNION ALL 
-- LinestringZ
SELECT [dbo].[STRound](geometry::STGeomFromText('LINESTRING(0.1 0.2 0.312,1.4 45.2 1.5738)',0),2,1).AsTextZM() as RoundGeom
UNION ALL 
-- Polygon
SELECT [dbo].[STRound](geometry::STGeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))',0),2,1).STAsText() as RoundGeom
UNION ALL 
-- MultiPolygon
SELECT [dbo].[STRound](
         geometry::STGeomFromText('MULTIPOLYGON (((160 400, 200.00000000000088 400.00000000000045, 200.00000000000088 480.00000000000017, 160 480, 160 400)), ((100 200, 180.00000000000119 300.0000000000008, 100 300, 100 200)))',0),
          2,1).STAsText() as RoundGeom

RoundGeom
POINT (0.3 0.3)
MULTIPOINT ((100.122 100.345), (388.839 499.404))
LINESTRING (0.1 0.2, 1.4 45.2)
LINESTRING (0.1 0.2 0.3, 1.4 45.2 1.6)
POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))
MULTIPOLYGON (((160 400, 200 400, 200 480, 160 480, 160 400)), ((100 200, 180 300, 100 300, 100 200)))

-- Geography
-- Can't overload existing STRound so have to use conversion functions.
SELECT [dbo].STToGeography(
         [dbo].[STRound](
           [dbo].STToGeometry(
                   geography::STGeomFromText('LINESTRING(141.29384764892390 -43.93834736282 234.82756,
                                                         141.93488793487934 -44.02323872332 235.26384)',
                                             4326),
                   4326
           ),
           7,
           3
         ),
         4326
       ).AsTextZM() as rGeom;
   
rGeom
LINESTRING (141.2938476 -43.9383474 234.828, 141.9348879 -44.0232387 235.264)

I hope this function is of use to someone.

Posted on

STPointToCircularArc

STPointToCircularArc — Return a measured point by snapping provided point to the provided CircularString

Function Specification

Function [lrs].[STPointToCircularArc] (
   @p_circularString geometry,
   @p_vertex         geometry,
   @p_round_xy       int   = 3,
   @p_round_zm       int   = 2
)
Returns geometry

Description

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

Computes Z and M values if exist on @p_circularString.

If input @p_circularString is 2D, length from start of @p_circularString 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 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_circularString (geometry) - (Measured) CircularString with or without Z ordinates.
    @p_point          (geometry) - Point near to linestring.
    @p_round_xy            (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
    @p_round_zm            (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Result

This function computes position of point on CircularString. If @p_point can’t be found on CircularArc, NULL is returned.

Examples

select 'Point has relationship with XYZM circular arc' as test,
       [lrs].[STPointToCircularArc] (
          geometry::STGeomFromText('CIRCULARSTRING (3 6.325 -2.1 0, 0 7 -2.1 3.08, -3 6.325 -2.1 6.15)',0),
          geometry::Point(2,8,0),
          3,2).AsTextZM() as project_point
union all
select 'Point does not have relationship with XYM CircularSring' as test,
       [lrs].[STPointToCircularArc] (
          geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0),
          geometry::Point(8,8,0),
          3,2).AsTextZM() as project_point
union all
select 'Point is on centre of the circular arc' as test,
       [lrs].[STPointToCircularArc] (
          geometry::STGeomFromText('CIRCULARSTRING (3 6.3246 -1, 0 7 -1, -3 6.3246 -1)',0),
          geometry::Point(0,0,0),
          3,2).AsTextZM() as project_point
union all
select 'Point projects on to point half way along circular arc' as test,
       [lrs].[STPointToCircularArc] (
          geometry::STGeomFromText('CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246)',0),
          geometry::Point(0,3.5,0),
          3,2).AsTextZM() as project_point
go

test                                                    project_point
------------------------------------------------------- -----------------------------
Point has relationship with XYZM circular arc           POINT (1.698 6.791 -2.1 1.37)
Point does not have relationship with XYM CircularSring NULL
Point is on centre of the circular arc                  POINT (3 6.3246 -1)
Point projects on to point half way along circular arc  POINT (0 7 NULL 3.1)
Posted on

STLocateBetweenElevations

STLocateBetweenElevations — Computes and returns elements that intersect the specified Z range.

Function Specification

Function [$(lrsowner)].[STLocateBetweenElevations] (
               @p_linestring geometry,
               @p_start_z    Float,
               @p_end_z      Float = null,
               @p_round_xy   int   = 3,
               @p_round_zm   int   = 2
             )
     Returns geometry 

Description

Is implementation of PostGIS:

      geometry ST_LocateBetweenElevations(geometry geom_mline, 
                                          float8 elevation_start,
                                          float8 elevation_end);

Processes the supplied (3D, 3DM) (multi)linestring returning the elements that intersect the specified range of elevations inclusively.

May return points and/or linestrings in the appropriate geometry type.

Where a new xy position is to be computed, the value is rounded using @p_round_xm.

Computes M values if exist on @p_linestring and rounds the values based on @p_round_zm.

Notes

Does not currently support Linestrings with CircularString elements (2012+).

Parameters

    @p_linestring (geometry) - Linestring geometry with Z ordinates (could have M ordinates).
    @p_start_z       (float) - Start Elevation.
    @p_end_z         (float) - End Elevation.
    @p_round_xy        (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
    @p_round_zm        (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Result

This function computes and extracts the geometry of the appropriate type.

Examples

-- PostGIS 1
select [$(lrsowner)].[STLocateBetweenElevations](
         geometry::STGeomFromText('LINESTRING(1 2 3, 4 5 6)',0),
         2,4, 
         3,2
       ).AsTextZM() as geomZ;

geomz
LINESTRING (1 2 3, 2 3 4)

-- PostGIS 2
select [$(lrsowner)].[STLocateBetweenElevations](
         geometry::STGeomFromText('LINESTRING(1 2 6, 4 5 -1, 7 8 9)',0),
         6,9, 
         3,2
       ).AsTextZM() as geomZ;
 
geomz
GEOMETRYCOLLECTION (POINT (1 2 6), LINESTRING (6.1 7.1 6, 7 8 9))

-- PostGIS 3
SELECT d.geom.AsTextZM() as geomWKT
  FROM (SELECT [$(lrsowner)].[STLocateBetweenElevations](
                 geometry::STGeomFromText('LINESTRING(1 2 6, 4 5 -1, 7 8 9)',0),
                 6,9,
                 3,2
               ) As the_geom
       ) As foo
       cross apply
       [$(owner)].[STExtract](foo.the_geom,default) as d;

geomWKT
POINT (1 2 6)
LINESTRING (6.1 7.1 6, 7 8 9)
Posted on

STLineSubstring

STLineSubstring — Returns a substring of the providec linestring starting and ending at the given fractions (between 0 and 1) of total 2D length or measure range.

Function Specification

Function [$(lrsowner)].[STLineSubstring] (
               @p_linestring     geometry,
               @p_start_fraction Float,
               @p_end_fraction   Float = null,
               @p_offset         Float = 0,
               @p_round_xy       int   = 3,
               @p_round_zm       int   = 2
         )
 Returns geometry

Description

Given a start and end measure, this function extracts the line segment defined between them (a point if start=end).

If a non-zero value is supplied for @p_offset, the extracted line is then offset to the left (if @p_offset < 0) or to the right (if @p_offset > 0).

Notes

Supports linestrings with CircularString elements.

Supports measured and unmeasured linestrings.

Is wrapper over STFindSegmentByMeasureRange.

Provides implementation of PostGIS’s ST_LocateBetween(geometry geomA, float8 measure_start, float8 measure_end, float8 offset);

Inputs

    @p_linestring  (geometry) - Linestring geometry with measures.
    @p_start_fraction (float) - Value defining start point of located geometry.
    @p_end_fraction   (float) - Value defining end point of located geometry.
    @p_offset         (float) - Offset (distance) value left (negative) or right (positive) in SRID units.
    @p_round_xy         (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
    @p_round_zm         (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Notes

Current offset generation is limited; a new more robust algorithm is being developed.

Result

This function computes and returns a line between the supplied start/end measure with offset.

Example

-- Measured Linestring
Print '....Line SubString';
select [lrs].[STLineSubstring] (
         geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
         0.0,1.0,0.0,3,2).AsTextZM() as line
union all
select [lrs].[STLineSubstring] (
         geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
         0.0,0.5,0.0,3,2).AsTextZM() as line
GO

line
LINESTRING (-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)
LINESTRING (-4 -4 0 1, 0 0 0 5.6, 13.2 0 0 13.2)

 -- UnMeasured LineStrings';
select [lrs].[STLineSubstring] (
         geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355),
         0.0,1.0,0.0,3,2).AsTextZM() as line
union all
select [lrs].[STLineSubstring] (
         geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355),
         0.0,0.5,0.0,3,2).AsTextZM() as line
GO

line
LINESTRING (-4 -4, 0 0, 10 0, 10 10)
LINESTRING (-4 -4, 0 0, 7.172 0)
Posted on

STLineInterpolatePoint

STLineInterpolatePoint — Returns point geometry at supplied fraction along linestring.

Function Specification

Function [$(lrsowner)].[STLineInterpolatePoint] (
            @p_linestring geometry,
            @p_fraction   Float
            @p_round_xy   int   = 3,
            @p_round_zm   int   = 2
         )
 Returns geometry

Description

Given a fraction between 0 and 1.0, this function returns a geometry point at the position described by that ratio.

Ratio is combined with length, so @p_ratio of 1.0 is equivalent to @p_linestring.STLength() ie @p_linestring.STEndPoint().
For example, @p_ratio value of 0.5 returns point at exact midpoint of linestring (ct centroid).

Supports measured and unmeasured linestrings.

Supports LineStrings with CircularString elements.

Notes

Wrapper over lrs.STFindPointByRatio

Implements PostGIS ST_LineInterpolatePoint function.

Parameters

    @p_linestring (geometry) - Linestring (including CircularString) geometry.
    @p_ratio         (float) - Length ratio between 0.0 and 1.0. If Null, @p_linestring is returned.
    @p_round_xy        (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
    @p_round_zm        (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Result

The function calculates and returns point at provided measure/length fraction from start.

Examples

-- Linestring
select f.fraction,
       [$(lrsowner)].[STLineInterpolatePoint] (
          @p_linestring geometry::STGeomFromText('LINESTRING(-4 -4 0  1, 0  0 0  5.6, 10  0 0 15.61, 10 10 0 25.4)',0),
          @p_fraction   f.fraction,
          @p_round_xy   4,
          @p_round_zm   3
       ).AsTextZM() as fPoint
  from (select 0.01 * CAST(t.IntValue as numeric) as fraction
          from [dbo].[Generate_Series](1,100,10) as t
       ) as f
  order by f.fraction
GO

fraction fPoint
0.01     POINT (-3.8186 -3.8186 0 1.046)
0.11     POINT (-2.0044 -2.0044 0 1.506)
0.21     POINT (-0.1902 -0.1902 0 1.966)
0.31     POINT (2.2968 0 0 6.496)
0.41     POINT (4.8625 0 0 7.497)
0.51     POINT (7.4281 0 0 8.498)
0.61     POINT (9.9938 0 0 9.499)
0.71     POINT (10 2.5595 0 16.587)
0.81     POINT (10 5.1252 0 17.566)
0.91     POINT (10 7.6909 0 18.545)
 
-- Unmeasured 2D Compound curve test.
select f.fraction,
       [$(lrsowner)].[STLineInterpolatePoint] (
          geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0),
          f.fraction,
          4,
          3
       ).AsTextZM() as fPoint
  from (select 0.01 * CAST(t.IntValue as numeric) as fraction
          from [dbo].[Generate_Series](1,100,10) as t
       ) as f
  order by f.fraction
GO

fraction fPoint
0.01     POINT (2.8163 6.4085)
0.11     POINT (0.876 6.945)
0.21     POINT (-1.1367 6.9071)
0.31     POINT (-2.9736 6.269)
0.41     POINT (-2.1079 4.4439)
0.51     POINT (-1.2421 2.6187)
0.61     POINT (-0.3764 0.7935)
0.71     POINT (0.4893 1.0316)
0.81     POINT (1.3551 2.8568)
0.91     POINT (2.2208 4.682)
Posted on

STLineLocatePoint

STLineLocatePoint — Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point

Function Specification.

Function [$(lrsowner)].[STLineLocatePoint] (
           @p_linestring geometry,
           @p_point      geometry,
           @p_round_xy   int   = 3,
           @p_round_zm   int   = 2
         )
 Returns geometry

Description.

This function, given a point near a the supplied measure @p_linestring, returns the measure/length ratio of the found position.

Notes.

Is identical to PostGIS’s ST_LineLocatePoint.

Srid of @p_linestring and @p_point must be the same.

If @p_linestring is measured ratio returned is measure of located point / MeasureRange of linestring.

If @p_linestring is not measured the ratio returned is position of located point from start / STLenth of linestring.

Parameters.

    @p_linestring (geometry) - Linestring geometry with or without measures.
    @p_point      (geometry) - Point near to linestring.
    @p_round_xy        (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
    @p_round_zm        (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Result.

The function returns the ratio of the position of the point on @p_linestring using measure/measure range or length/total length.

Example.

select [lrs].[STLineLocatePoint] (
           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),
           default,
           default
       ) as ratio
union all
select [lrs].[STLineLocatePoint] (
           geometry::STGeomFromText('LINESTRING(-4 -4,0 0,10 0,10 10)',28355),
           geometry::Point(10,0,28355),
           4,
           8
       ) as ratio
GO

ratio
23.44
15.6
Posted on

STIsPseudoMultiCurve

STIsPseudoMultiCurve — Checks if supplied @p_geometry is a GeometryCollection object containing only LineString, CircularString and CompoundCurve elements.

Function Specification.

Function [dbo].[STIsPseudoMultiCurve] (
                  @p_geometry geometry
         )
Returns bit

Description.

This function checks if the supplied @p_geometry is a GeometryCollection object containing only LineString, CircularString and CompoundCurve elements.

One cannot create a MultiLineString object where an element is a CircularString or CompoundCurve.

Any attempt to do so causes the GeometryType to be set to GeometryCollection rather than MultiLineString or MultiCurve.

If these sorts of GeometryCollection objects are not supported, it is impossible to represent a MultiLine object with a mix of CircularCurve and LineString types.

The function returns 1 (true) if provided geometry is a pseudo MultiCurve (or MultiLineString).

Parameters.

    @p_geometry (geometry) - GeometryCollection or MultiLineString.

Result.

This function returns 1 (true) if @_geometry is a pseudo MultiCurve, 0 (false) otherwise.

Example.

-- This is how SQL Server merges a LineString and a CircularString that do not touch
-- 
SELECT geometry::STGeomFromText('LINESTRING(0 0 0 5.6, 5 5 0 6.3,5 10 0 9.6)',0)
       .STUnion(
         geometry::STGeomFromText('CIRCULARSTRING(-0.5 -0.5 0 5.6,-0.2 -0.4 0 5.5, -0.1 -0.1 0 5.65)',0)
       ).AsTextZM() as MultiCurve;
GO

MultiCurve
--------------------------------------------------------------------------------------------------
GEOMETRYCOLLECTION (LINESTRING (5 10, 5 5, 0 0), CIRCULARSTRING (-0.5 -0.5, -0.2 -0.4, -0.1 -0.1))

-- Note that STUnion removes Z and M ordinates

-- Now put result through STIsPseudoMultiCurve
-- 
SELECT [dbo].STIsPseudoMultiCurve] ( geometry::STGeomFromText('GEOMETRYCOLLECTION (LINESTRING (5 10, 5 5, 0 0), CIRCULARSTRING (-0.5 -0.5, -0.2 -0.4, -0.1 -0.1))',0)) as isMultiCurve
GO

isMultiCurve
------------
1
Posted on

STFindPointByMeasure

STFindPointByMeasure — Returns (possibly offset) point geometry at supplied measure along linestring.

Function Specification.

Function [lrs].[STFindPointByMeasure] (
               @p_linestring geometry,
               @p_measure    Float,
               @p_offset     Float = 0.0,
               @p_round_xy   int   = 3,
               @p_round_zm   int   = 2
             )
Returns geometry

Description.

Given a measure, this function returns a geometry point at that measure.

If a non-zero/null value is suppied for @p_offset, the found point is offset (perpendicular to line) to the left (if @p_offset < 0) or to the right (if @p_offset > 0).

The returned point has its ordinate values rounded using the supplied @p_round_xy/@p_round_zm decimal place values.

Notes.

Supports LineStrings with CircularString elements.

Parameters.

    @p_linestring (geometry) - Linestring geometry with measures.
    @p_measure       (float) - Measure defining position of point to be located.
    @p_offset        (float) - Offset (distance) value left (negative) or right (positive) in SRID units.
    @p_round_xy        (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
    @p_round_zm        (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Result.

This function returns the geometry point at the provided measure optionally offset to left or right.

Example.

— Handle non-measured linestring
with data as (
select geometry::STGeomFromText(‘COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))’,0) as linestring
)
select f.linestring.STEquals(f.fpoint) as equals
from (select [lrs].[STFindPointByMeasure](a.linestring,0,0,3,2) as fPoint,
a.linestring
from data as a
) f
go

equals
1

— Circular Arc / Measured Tests
with data as (
select geometry::STGeomFromText(‘LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)’,0) as linestring
union all
select geometry::STGeomFromText(‘CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)’,0) as linestring
union all
select geometry::STGeomFromText(‘COMPOUNDCURVE(CIRCULARSTRING (3 6.3246 NULL 0, 0 7 NULL 3.08, -3 6.3246 NULL 6.15),(-3 6.3246 NULL 6.15, 0 0 NULL 10.1, 3 6.3246 NULL 20.2))’,0) as linestring
)
select a.linestring.STGeometryType() as curve_type,
g.intValue as measure,
o.IntValue as offset,
[lrs].[STFindPointByMeasure](a.linestring,g.IntValue,o.IntValue,3,2).AsTextZM() as fPoint
from data as a
cross apply
[dbo].[generate_series](a.lineString.STPointN(1).M,
round(a.lineString.STPointN(a.linestring.STNumPoints()).M,0,1),
[lrs].[STMeasureRange](a.linestring) / 4.0 ) as g
cross apply
[dbo].[generate_series](-1, 1, 1) as o
order by curve_type, measure
GO

curve_type measure offset fPoint
CircularString 0 -1 POINT (3.428 7.229 NULL 0)
CircularString 0 0 POINT (3 6.325 NULL 0)
CircularString 0 1 POINT (2.572 5.421 NULL 0)
CircularString 1 -1 POINT (2.355 7.646 NULL 1)
CircularString 1 0 POINT (2.061 6.69 NULL 1)
CircularString 1 1 POINT (1.767 5.734 NULL 1)
CircularString 2 -1 POINT (1.234 7.904 NULL 2)
CircularString 2 0 POINT (1.08 6.916 NULL 2)
CircularString 2 1 POINT (0.925 5.928 NULL 2)
CircularString 3 -1 POINT (0.086 8 NULL 3)
CircularString 3 0 POINT (0.076 7 NULL 3)
CircularString 3 1 POINT (0.065 6 NULL 3)
CircularString 4 -1 POINT (-1.063 7.929 NULL 4)
CircularString 4 0 POINT (-0.93 6.938 NULL 4)
CircularString 4 1 POINT (-0.797 5.947 NULL 4)
CircularString 5 -1 POINT (-2.19 7.695 NULL 5)
CircularString 5 0 POINT (-1.916 6.733 NULL 5)
CircularString 5 1 POINT (-1.643 5.771 NULL 5)
CircularString 6 -1 POINT (-3.271 7.301 NULL 6)
CircularString 6 0 POINT (-2.863 6.388 NULL 6)
CircularString 6 1 POINT (-2.454 5.476 NULL 6)
CompoundCurve 0 -1 POINT (3.429 7.228 NULL 0)
CompoundCurve 0 0 POINT (3 6.3246 NULL 0)
CompoundCurve 0 1 POINT (2.571 5.421 NULL 0)
CompoundCurve 5 -1 POINT (-2.19 7.694 NULL 5)
CompoundCurve 5 0 POINT (-1.916 6.733 NULL 5)
CompoundCurve 5 1 POINT (-1.642 5.771 NULL 5)
CompoundCurve 10 -1 POINT (-7 5.325 NULL 10)
CompoundCurve 10 0 POINT (-7 6.325 NULL 10)
CompoundCurve 10 1 POINT (-7 7.325 NULL 10)
CompoundCurve 15 -1 POINT (-12 5.325 NULL 15)
CompoundCurve 15 0 POINT (-12 6.325 NULL 15)
CompoundCurve 15 1 POINT (-12 7.325 NULL 15)
CompoundCurve 20 -1 POINT (-17 5.325 NULL 20)
CompoundCurve 20 0 POINT (-17 6.325 NULL 20)
CompoundCurve 20 1 POINT (-17 7.325 NULL 20)
LineString 1 -1 POINT (-0.707 0.707 NULL 1)
LineString 1 0 POINT (-4 -4 0 1)
LineString 1 1 POINT (0.707 -0.707 NULL 1)
LineString 7 -1 POINT (1.4 1 NULL 7)
LineString 7 0 POINT (1.4 0 NULL 7)
LineString 7 1 POINT (1.4-1 NULL 7)
LineString 13 -1 POINT (7.4 1 NULL 13)
LineString 13 0 POINT (7.4 0 NULL 13)
LineString 13 1 POINT (7.4-1 NULL 13)
LineString 19 -1 POINT (9 3.39 NULL 19)
LineString 19 0 POINT (10 3.39 NULL 19)
LineString 19 1 POINT (11 3.39 NULL 19)
LineString 25 -1 POINT (9 9.39 NULL 25)
LineString 25 0 POINT (10 9.39 NULL 25)
LineString 25 1 POINT (11 9.39 NULL 25)
[/code]

Posted on

STFindPointByRatio

STFindPointByRatio — Returns (possibly offset) point geometry at supplied length ratio along linestring.

Function Specification.

Function [lrs].[STFindPointByRatio] (
               @p_linestring geometry,
               @p_ratio      Float,
               @p_offset     Float = 0.0,
               @p_round_xy   int   = 3,
               @p_round_zm   int   = 2
             )
Returns geometry

Description.

Given a ratio (0 to 1.0), this function returns a geometry point at the position described by that ratio.

Ratio is combined with length, so @p_ratio of 1.0 is equivalent to @p_linestring.STLength() ie @p_linestring.STEndPoint().

For example, @p_ratio value of 0.5 returns point at exact midpoint of linestring (ct centroid).

If a non-zero/null value is suppied for @p_offset, the found point is offset (perpendicular to line) to the left (if @p_offset < 0) or to the right (if @p_offset > 0).

The returned point has its ordinate values rounded using the supplied @p_round_xy/@p_round_zm decimal place values.

Notes.

Supports LineStrings with CircularString elements.

Wrapper over [lrs].[STFindPointByLength]

Parameters.

    @p_linestring (geometry) - Linestring (including CircularString) geometry.
    @p_ratio         (float) - Length ratio between 0.0 and 1.0. If Null, @p_linestring is returned.
    @p_offset        (float) - Offset (distance) value left (negative) or right (positive) in STSrid units.
    @p_round_xy        (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
    @p_round_zm        (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.

Result.

This function returns a point geometry at the provided length ratio from the start, with optional offset to left or right.

Example.

-- LineStrnig Test
select f.ratio,
       o.IntValue as offset,
       [lrs].[STFindPointByRatio] (
          /* @p_linestring*/ geometry::STGeomFromText('LINESTRING(-4 -4 0  1, 0  0 0  5.6, 10  0 0 15.61, 10 10 0 25.4)',0),
          /* @p_ratio     */ f.ratio,
          /* @p_offset    */ o.IntValue,
          /* @p_round_xy  */   3,
          /* @p_round_zm  */   2
       ).AsTextZM() as fPoint
  from (select /* @p_ratio */ 0.01 * CAST(t.IntValue as numeric) as ratio
          from [dbo].[Generate_Series](1,100,10) as t
       ) as f
       cross apply 
       [dbo].[generate_series](-1,1,1) as o
  order by f.ratio
GO

ratio offset fPoint
0.01      -1  POINT (-4.526 -3.112 0 1.05)
0.01       0  POINT (-3.819 -3.819 0 1.05)
0.01       1  POINT (-3.112 -4.526 0 1.05)
0.11      -1  POINT (-2.711 -1.297 0 1.51)
0.11       0  POINT (-2.004 -2.004 0 1.51)
0.11       1  POINT (-1.297 -2.711 0 1.51)
0.21      -1  POINT (-0.897 0.517 0 1.97)
0.21       0  POINT (-0.19 -0.19 0 1.97)
0.21       1  POINT (0.517 -0.897 0 1.97)
0.31      -1  POINT (2.297 1 0 6.5)
0.31       0  POINT (2.297 0 0 6.5)
0.31       1  POINT (2.297 -1 0 6.5)
0.41      -1  POINT (4.862 1 0 7.5)
0.41       0  POINT (4.862 0 0 7.5)
0.41       1  POINT (4.862 -1 0 7.5)
0.51      -1  POINT (7.428 1 0 8.5)
0.51       0  POINT (7.428 0 0 8.5)
0.51       1  POINT (7.428 -1 0 8.5)
0.61      -1  POINT (9.994 1 0 9.5)
0.61       0  POINT (9.994 0 0 9.5)
0.61       1  POINT (9.994 -1 0 9.5)
0.71      -1  POINT (9 2.56 0 16.59)
0.71       0  POINT (10 2.56 0 16.59)
0.71       1  POINT (11 2.56 0 16.59)
0.81      -1  POINT (9 5.125 0 17.57)
0.81       0  POINT (10 5.125 0 17.57)
0.81       1  POINT (11 5.125 0 17.57)
0.91      -1  POINT (9 7.691 0 18.54)
0.91       0  POINT (10 7.691 0 18.54)
0.91       1  POINT (11 7.691 0 18.54)

select f.ratio,
       o.IntValue as offset,
       [lrs].[STFindPointByRatio] (
          /* @p_linestring*/ geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0),
          /* @p_ratio     */ f.ratio,
          /* @p_offset    */ o.IntValue,
          /* @p_round_xy  */   3,
          /* @p_round_zm  */   2
       ).AsTextZM() as fPoint
  from (select /* @p_ratio */ 0.01 * CAST(t.IntValue as numeric) as ratio
          from [dbo].[Generate_Series](1,100,10) as t
       ) as f
       cross apply 
       [dbo].[generate_series](-1,1,1) as o
  order by f.ratio
GO

ratio offset fPoint
0.01      -1  POINT (3.219 7.324)
0.01       0  POINT (2.816 6.409)
0.01       1  POINT (2.414 5.493)
0.11      -1  POINT (1.001 7.937)
0.11       0  POINT (0.876 6.945)
0.11       1  POINT (0.751 5.953)
0.21      -1  POINT (-1.299 7.894)
0.21       0  POINT (-1.137 6.907)
0.21       1  POINT (-0.974 5.92)
0.31      -1  POINT (-2.07 6.698)
0.31       0  POINT (-2.974 6.269)
0.31       1  POINT (-3.878 5.84)
0.41      -1  POINT (-1.204 4.873)
0.41       0  POINT (-2.108 4.444)
0.41       1  POINT (-3.012 4.015)
0.51      -1  POINT (-0.338 3.048)
0.51       0  POINT (-1.242 2.619)
0.51       1  POINT (-2.146 2.19)
0.61      -1  POINT (0.528 1.223)
0.61       0  POINT (-0.376 0.794)
0.61       1  POINT (-1.28 0.365)
0.71      -1  POINT (-0.415 1.46)
0.71       0  POINT (0.489 1.031)
0.71       1  POINT (1.393 0.602)
0.81      -1  POINT (0.451 3.285)
0.81       0  POINT (1.355 2.856)
0.81       1  POINT (2.259 2.427)
0.91      -1  POINT (1.317 5.111)
0.91       0  POINT (2.221 4.682)
0.91       1  POINT (3.125 4.253)