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

Wrapper Functions for PostGIS geography

Having done a lot of work with PostGIS geometry and geography I decided that I needed some wrapper functions to force more consistency in my use.

The wrapper functions are as follows are are available at GitHub

ST_AddMeasure(geography,float8,float8);
ST_AddPoint(geography,geography);
ST_AddPoint(geography,geography,integer);
ST_CoordDim(geography,geography);
ST_Dimension(geography);
ST_EndPoint(geography);
ST_ExteriorRing(geography);
ST_FlipCoordinates(geography);
ST_GeometryN(geography,integer);
ST_GeometryType(geography);
ST_InteriorRingN(geography,integer);
ST_IsClosed(geography);
ST_IsEmpty(geography);
ST_IsValid(geography);
ST_IsValid(geography,integer);
ST_IsValidDetail(geography);
ST_IsValidDetail(geography,integer);
ST_IsValidReason(geography);
ST_IsValidReason(geography,integer);
ST_M(geography);
ST_NPoints(geography);
ST_NumGeometries(geography);
ST_NumInteriorRings(geography);
ST_NumPoints(geography);
ST_PointN(geography,integer);
ST_Points(geography);
ST_RemovePoint(geography,integer);
ST_SetPoint(geography,integer,geography);
ST_StartPoint(geography);
ST_X(geography);
ST_Y(geography);
ST_Z(geography);

Posted on

Change Log

2019-08-17: Oracle JTS Utilities.

  • Added ST_OffsetLine – Offsets a linestring by the required value with optional styling.
  • Added ST_OneSidedBuffer – Creates a buffer polygon on one side of the supplied line with optional styling.
  • Updated ST_Buffer to remove one sided option

2019-08-16: SQL Server Spatial.

  • Added STPointToCircularArc which returns a measured point by snapping provided point to the provided circularstring
  • Updated STProjectPoint to support CircularString and CompoundCurve objects via STPointToCircularArc.
  • Updated documentation.

2019-07-19: SQL Server Spatial.

  • Corrected installation errors
  • Fixed STPointFromText null Z with M handling
  • Moved all PostGIS LRS functions into a single script.
  • Added STLocateBetweenElevations, STLineSubstring, and STLineInterpolatePoint
  • Modified STExtract to extract polygon ring sub elements where ring defined using CircularCurve or CompoundCurve.
  • Modified STIsMeasureDecreasing
  • Modified STCogo2Line
  • Improved function documentation.
  • Updated html documentation.

2019-05-17: SQL Server Spatial

  • Added STDensify function which implements a basic geometry densification algorithm.
  • Added STGeometryTypes function which extracts all geometry type keywords from a geometry (and its sub-elements) eg COMPOUNDCURVE,LINESTRING,CIRCULARSTRING

2019-01-12: Oracle PLSQL Object code.

  • Created install.sh Linux bash script