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

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

STScale

STScale is a TSQL function that moves a geometry object’s ordinates using the supplied delta X, Y, Z and M.

Function Specification.

Function [dbo].[STScale] (
           @p_geometry geometry,
           @p_xFactor  float,
           @p_yFactor  float,
           @p_zFactor  float,
           @p_mFactor  float,
           @p_round_xy int = 3,
           @p_round_zm int = 2 
         )
Returns geometry 

Example.

-- XY
SELECT [dbo].[STScale](
               geometry::STGeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),
               -5.0,
                3.2,
                NULL,
                NULL,
                3,
                2
       ).AsTextZM() as scaledGeom;

scaledGeom
LINESTRING (-0.5 0.64, -7 144.64)

-- XYZ
SELECT [dbo].[STScale](
               geometry::STGeomFromText('LINESTRING(0.1 0.2 NULL 1.0,1.4 45.2 NULL 45.02)',0),
                NULL,
                NULL,
                NULL,
                1.5,
                3,
                2
       ).AsTextZM() as scaledGeom;

scaledGeom
LINESTRING (0.1 0.2 NULL 1.5, 1.4 45.2 NULL 67.53)

-- XYZM
SELECT [dbo].[STScale](
               geometry::STGeomFromText('LINESTRING(0.1 0.2 0.9 1.0,1.4 45.2 2.1 45.02)',0),
                1.0,
                1.0,
                2.0,
                1.5,
                3,
                2
       ).AsTextZM() as scaledGeom;

scaledGeom
LINESTRING (0.1 0.2 1.8 1.5, 1.4 45.2 4.2 67.53)

Description.

This function scales the supplied geometry’s ordinates using the supplied scale factors.

The computed ordinates of the new geometry are rounded to the appropriate decimal digits of precision.

Parameters.

    @p_geometry (geometry) - supplied geometry of any type.
    @p_xFactor  (double)   - X ordinate scale factor.
    @p_yFactor  (double)   - Y ordinate scale factor.
    @p_zFactor  (double)   - Z ordinate scale factor.
    @p_mFactor  (double)   - M ordinate scale factor.
    @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 XM ordinates are rounded.

Results.

The function returns the input geometry scaled by supplied ordinate factor values.

Posted on

STMove

STMoveis a function that moves a geometry using the supplied delta X, Y, Z and M ordinate values.

TSQL Function Specification.

Function STMove (
           @p_geometry geometry,
           @p_dX       float,
           @p_dY       float,
           @p_dZ       float,
           @p_dM       float,
           @p_round_xy int = 3,
           @p_round_zm int = 2
         )
Returns geometry

Description.

The STMove function moves the supplied geometry’s ordinates using the supplied x, y, z and m delta ordinate values.

The computed ordinates of the new geometry are rounded to @p_round_xy/@p_round_zm number of decimal digits of precision.

Parameters.

    @p_geometry (geometry) - Supplied geometry of any type.
    @p_dX          (float) - X ordinate delta shift.
    @p_dy          (float) - Y ordinate delta shift.
    @p_dZ          (float) - Z ordinate delta shift.
    @p_dM          (float) - M ordinate delta shift.
    @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 moves the input geometry by supplied ordinate deltas.

Example.

SELECT [dbo].[STMove](geometry::ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),-5.0,30.1,default,default,2,1).AsTextZM() as movedGeom
GO

movedGeom
'LINESTRING(-4.9 30.2,-3.6 31.5)'
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