Change Log For Open Source Spatial Database Scripts

Comments

As always with changes and fixes, if you have made a donation you are eligible for a free upgrade.

2020-11-11 SQL Server Functions

Summary

Firstly, I have changed the main software license to MIT, but still maintain additional requirements.

The STMinX/MaxX, STMinY/STMaxY functions are simple scalar Minimum Bounding Rectangle (Envelope) functions that allow simple access to a geometry’s MBR extents, as against the table functions STGeometry2MBR/STGeography2MBR which are used in SQL.

The STGeomPoint2GridRef/STGeogPoint2GridRef works with the grid generating STTileGeogByPoint/STTileGeomByPoint functions enabling the geocoding of a point against a grid to return a grid cells column/row reference without use of spatial functions like STContains.

The STRandomBetween was created to support the generation of a random set of test points.

Created

  • STGeomPoint2GridRef — Function that takes a geometry point and some grid
    parameters and returns col/row reference.
  • STGeogPoint2GridRef — Function that takes a geographic point and some
    grid parameters and returns col/row reference.
  • STTileGeomByPoint — Creates mesh of tiles in geometric space anchored to a supplied origin.
  • STGeogDetermine — Determine relationships between two geography
    entities
  • STMaxX — Returns lower left X ordinate valueof supplied Geometry’s
    Envelope.
  • STMaxY — Returns lower left X ordinate valueof supplied Geometry’s
    Envelope.
  • STMinX — Returns lower left X ordinate valueof supplied Geometry’s
    Envelope.
  • STMinY — Returns lower left X ordinate valueof supplied Geometry’s
    Envelope.
  • STRandomBetween — Generates a floating point number between two
    numbers

2020-10-31 – SQL Server Functions

Summary

Shout out to Rayner Howard for his testing of all functions that use CircularStrings.
Quality has improved and I am heavily in his debt.

General Comments

  • Some deprecated and unnecessary functions removed (but not master repository).
  • Some API Functions renamed to improve readability.
  • Improved handling of Circular String angles to take into account rotation of CircularString.
  • Added complete Collection API methods.

Created/Renamed

  • STCollectionInsertN
  • — Inserts geometry at specified position in the geometry collection.

  • STCollectionDeleteN
  • — Deletes geometry at specified position in the geometry collection.

  • STCollectionUpdateN
  • — Updates geometry at specified position in the geometry collection.

  • STCollectionPrint
  • — Prints geometries in supplied geometry collection.

  • STIsQuadrilateral
  • — Checks to see if supplied geometry is a quadrilateral.

  • STFindPointByRatio
  • STCircularString2Circle
  • — Supplied with a circular string this function creates its polygon (circle) equivalent with a single exterior ring.

  • STCircularStringAngleAtCentre
  • — Supplied with a circular arc, this function computes the angle the arc makes at the centre of its circle (rename from STFindCircularStringAngle).

  • STCircularStringAngleToPoint
  • — Supplied with a circular string and a point, this function computes the (subtended) angle from the start of the arc to the supplied point.

  • STCircularStringLengthToPoint
  • — Supplied with a circular string and a point, this function computes the distance from the start of the arc to the supplied point (Renamed from STComputeArcLengthToPoint).

  • STMakeCircularString
  • — Creates a three point Circular linestring.

Modified

Linear Referencing

  • STAddMeasure
  • STFindPointByLength
  • STFindPointsByMeasures
  • STFindPointByMeasure
  • STFindOffset
  • STProjectPoint

Renamed

  • STFindCircularStringAngle –> STCircularStringAngleAtCentre
  • STIntersectCircularArc –> STFindCircularStringIntersection
  • STMakeLineFromCollection –> STCollectionToLine
  • STForceCollection –> STCollectionForce

Documentation, CircularString, Processing changes, New and Updated test SQL

  • STAverageBearing
  • STBearingAndDistance
  • STCheckRadii
  • STCircularStringN
  • STCogoFunctions
  • STCollectionAppend
  • STCollectionDeduplicate
  • STCollectionExtract
  • STComputeTangentPoint
  • STEquals
  • STFilterLineSegment
  • STFindClosestSegment
  • STFindDeflectionAngle – extra methods for creating deflection angles added as parameters
  • STFindPointBisector
  • STHasCircularString – Final release.
  • STIsCCW
  • STIsCollinear
  • STMakeCircularString
  • STMakeEnvelope
  • STMakeEnvelopeFromText
  • STMakeLines
  • STMakePoint
  • STMulti
  • STOffsetLine
  • STOffsetPoint
  • STRemoveDuplicatePoints – completely re-written to remove duplicate vertices for any geometry except Point and GeometryCollection.
  • STRemoveOffsetSegments
  • STSegmentize
  • STSquareBuffer
  • STWhichSide

Deleted

  • STMakeLineWKTFromGeometryCollection
  • STMakeLineWKTFromGeographyCollection

##############################################################################

2020-09-27 SQL Server Functions

Created

  • STHasCircularString.sql – Checks if supplied geometry contains a CircularString
  • STLineToCurve – Alpha release of a function that converts a LineString into a CompoundCurve

Modified

  • STNumCircularStrings.sql – Now counts CircularStrings throughout whole of a CurvePolygon, CompoundCurve, GeometryCollection not just a single CircularString parameter

##############################################################################

2020-08-29 SQL Server Functions

Main changes related to refactoring CircularString mathematics and functions that call them.

Created

  • STCircularString2Circle.sql — Supplied with a circular string this function creates its polygon equivalent
  • STCircularStringAngleAtCentre.sql — Supplied with a CircularString, this function computes the angle the arc makes at the centre of its circle.
  • STCircularStringAngleToPoint.sql — Supplied with a circular string and a point, this function computes the (subtended) angle from the start of the arc to the supplied point.
  • STCircularStringLengthToPoint.sql — Supplied with a circular string and a point, this function computes the distance from the start of the arc to the supplied point.

Modified

  • STCircularStringN.sql — Handles CompoundCurve with a single 30-point CircularString
  • STEquals.sql — Fixed handling of Z and M values in STEqualsByDistance
  • STSegmentize.sql — Fixed calculation of start_length; Changed call to renamed STMakeCircularString
  • STWhichSide.sql — Fixed handling of of CircularStrings with subtended angle of > 180.0
  • STAddMeasure.sql — Improved documentation; changed handling of start/end measure parameters.
  • STFilterLineSegment.sql — Delayed application of rounding of ordinates to point where geometry is returned.
  • STFindPointByMeasure.sql — Changed to use STFilterLineSegment instead of STSegmentize; Improved ordinate comparison.
  • STProjectPoint.sql — Changes to use STFilterLineSegment instead of STSegmentize; Improved ordinate comparison.

Deleted

Replaced by new CircularString functions.

  • STFindCircularStringAngle
  • STComputeArcLengthToPoint.sql

##############################################################################

2020-07-31 SQL Server Functions

Performance improvements were made to the LRS functions at the request of a donator.
This meant a change to STWhichSide, STProjectPoint and a replacement STFilterLineSegment.

Created

  • STFilterLineSegment — Ported from previous version for use in performance improvements.
  • STNumCircularStrings — Returns number of CircularString elements in provided CircularString.

Modified

  • STProjectPoint — Changed to use STFilterLineSegment. Fixed wrong calculation of ratios.
  • STFindOffset — Fixed wrong use of calculation point for offset.
  • STWhichSide — Implemented short circuit of segmentizing when not needed.

##############################################################################

2020-07-09 PostGIS Functions

A new install scripts has been created and added.
New functions have been added with others undergoing rationalisation.

##############################################################################

2020-06-20 SQL Server Functions

Created

  • STReflect — A function which reflects the supplied geometry about the supplied 2 point reflection line.

Modified

  • STOffsetSegment — Fixed offset side bug for CircularStrings with subtended angles less than greater than 180 degrees.
  • Function deletion by install script only drops functions and procedures in the package.

##############################################################################

2020-06-11 SQL Server Functions

Many functions not mentioned here have had improvements in documentation.

A lot of reorganisation of development folders, deployment scripts and checking, along with a move to github, has occurred since the last release.

Created

  • STAsEWKT() – Implements an method to create Extended Well Known Text (EWKT) strings from the input geometry.
  • STComputeArcLengthToPoint() – Supplied with a circular string and a point, this function computes the distance from the start of the arc to the supplied point.
  • STFindCircularStringAngle() – Supplied with a cicular arc, this function computes the angle the arc makes at the centre of its circle.
  • STPopulateGeometryColumns() – Detect tables with geometry datatype columns and adds them to [$(owner)].[geometry_columns]
  • STAddGeometryColumn() – Adds/Updates a single table with geometry datatype columns to [$(owner)].[geometry_columns]
  • STCollectionAppend() – Appends geometry to end of the geometry collection.
  • STCollectionDeduplicate() – Determines all possible spatial relations between two geometry instances.
  • STCollectionExtract() – Extracts geometry of specified geometey type from the geometry collection.
  • STSimilarityByArea() – Function that compares two polygons where their ordinates are not perfectly equal.
  • STThinnessRatio() – Computes a measure of the thinness of a polygon (cf area/perimiter ratio)
  • STSmoothTile() – Smooths polygon created from raster to segment conversion.
  • STIntersect2CircularArcs() – Finds intersections between one CircularString another.
  • STIntersectCircularArc() – Computes intersection between a linestring and a CircularString.
  • STSnapPointToGeom() – Function that snaps a point to a geometry returning (uses ShortestLineTo).

Modified
General:

  • STDetermine() – Added support for Area Similarity Ratio
  • STCogoFunctions() – Various.
  • STIsGeo() – Improved geometry/geography identification.
  • STMakeLineFromCollection() – Added STIsValid to various functions’ parameter checking
  • STMakePoint() – Bug fix.
  • STOffsetLine() – Added STIsValid to parameter checking
  • STOffsetPoint() – Fixed Measure issue, changed COALESCE to ISNULL. Fixed function declaration problem.
  • STOffsetSegment() – Added STIsValid test. Implemented missing CircularString processing.
  • STRemoveOffsetSegments() – Added documentation. Improved parameter testing. Improved STMakeLineFromGeometry SQL processing.
  • STReduce() – Fixed bug with reduction ratio.
  • STVertices() – Re-wrote as in-line function that uses STSegmentize()

LRS:

  • STProjectPoint() – Improved documentation. Fixed bug with handling CircularString Measure calculation.
  • STFindOffset() – Improved documentation. Fixed adding of offset positive/negative directions.
  • STFindPointByMeasure() – Improved Documentation.
  • STFindPointsByMeasures() – Renamed from STFindPointsByDeltaMeasure().
  • STPostGIS() – Added @p_radius_check to a number of functions that call functions that now have @p_radius_check capability.
  • STSplitProcedure() – Improved Documentation, plus added @p_radius_check.
  • STSplit() – Improved Documentation, plus added @p_radius_check.

##############################################################################

2020-12-02 SQL Server Functions

Created

  • STAsEWKT.sql – Convert geometry to EWKT
  • STGeomFromEWKT.sql – Convert EWKT to geometry
  • STBearingAlongLine.sql – Wrapper over STBearing
  • STTo2D.sql – Function that removes any ZM ordinates to return a geometry with XY ordinates only.
  • STWhichSide.sql (moved from LRS to General)
  • STFindPointsByDeltaMeasure.sql – An LRS function that allows one to generate all measures along a line and a set of offsets at each measure point.

Modified

  • STAddZ.sql
  • STAppend.sql – Substantion changes to better support CircularStrings and CompoundCurves
  • STCogoFunctions.sql
  • STFindPointBisector.sql – Improved function after extensive testing.
  • STOffsetLine.sql – Improved function after extensive testing.
  • STOffsetPoint.sql – Improved function after extensive testing.
  • STOneSidedBuffer.sql – Improved function after extensive testing.
  • STRemoveOffsetSegments.sql – Used by STOneSidedBuffer.
  • STRemoveSpikes.sql – Formatting
  • STVertices.sql – Formatting
  • STFindPointByLength.sql – Support for disappearing offsets in circularStrings.
  • STFindPointByMeasure.sql – Support for disappearing offsets in circularStrings.
  • STFindSegmentByLengthRange.sql – Migrated to STSegmentize and support for radius check chagnes.
  • STFindSegmentByMeasureRange.sql – Migrated to STSegmentize and support for radius check changes.
  • STIsMeasured.sql – Modified documentation.
  • STPostGIS.sql – Modified wrapper functions for support of radius check and other changes.
  • STReverseMeasure.sql – Corrected bug found in testing.
  • STSplitSegmentByLength.sql – Migrated to STSegmentize and support for radius check changes.
  • STSplitSegmentByMeasure.sql – Migrated to STSegmentize and support for radius check changes.
  • STGetGeomMetadata.sql – Added GEOMETRY_COLUMNS table generation and function to populate it.

##############################################################################

2019-12-18 SQL Server Functions

Created

  • STOffsetPoint.sql which offsets a single linestring or circularstring.
  • STWhichSide.sql

Integrated Linestring and Arc (CircularString) functions into the following modified functions:

  • STSplitSegmentByLength.sql
  • STSplitSegmentByMeasure.sql
  • STFindPointByLength.sql
  • STFindPointByMeasure.sql
  • STFindSegmentByLengthRange.sql
  • STFindSegmentByMeasureRange.sql

Rationalisation of unneeded functions

  • STFilterLineSegmentByLength.sql
  • STFilterLineSegmentByMeasure.sql
  • STFindArcPointByLength.sql
  • STFindArcPointByMeasure.sql

Modified

  • STReverseMeasure.sql – Modified to use STSegmentize
  • STAddMeasure.sql – Modified to use STSegmentize
  • STScaleMeasure.sql – Modified to use STSegmentize
  • STAverageBearing.sql – to support CircularStrings and CompoundCurves

2019-12-17: SQL Server Functions

Created

  • STAverage.sql – A trivial function that averages the ordinates of two points.
  • STComputeTangentPoint.sql – Extracted from STCogoFunctions.sql, and revamped
  • STFindPointBisector.sql – Extracted from STCogoFunctions.sql and revamped
  • STOffsetPoint.sql – Move code from other functions to execute, as a single function, the offsetting of a point from a 2-point linestring or 3-point circularstring.

Modified

  • STFindDeflectionAngle.sql – Improved with support for CompoundCurves.
  • STMakeLines.sql – Modified STMakeCircularLine to return LineString if three points are collinear
  • STSegmentize.sql – A number of improvements such as more debugging of filters like MEASURE_RANGE

##############################################################################

2019-11-27: PostGIS Functions

  • ST_CircularStringN — Extracts nominated 3-point circular string from a CIRCULARSTRING with more than 3 points.
  • ST_CurveN — Returns requested Curve within COMPOUNDCURVE
  • ST_HasM — Determines if supplied geometry has M ordinates.
  • ST_HasZ — Determines if supplied geometry has Z ordinates.
  • ST_MakeCircularString — Makes a 3 point circular arc from 3 supplied points
  • ST_NumCircularStrings — Compute number of 3-point circular strings within a CIRCULARSTRING with more than 3 points.
  • ST_NumCurves — Returned number of sub-objects within a COMPOUNDCURVE
  • ST_Segmentize — Version of ST_Vectorize that honours COMPOUNDCURVE and CIRCULARSTRING objects.

##############################################################################

2019-11-04: SQL Server Spatial Functions.

Add

  • STAverageBearing — Function that computes average bearing of segments in linestring.
  • STBoundingDiagonal — Returns the diagonal of the supplied geometry’s bounding box as a linestring.
  • STCheckRadii — Checks if radius of any three points in a linestring are less than the desired amount.
  • STDirectVincenty — Vincenty Direct Solution of Geodesics on the Ellipsoid.
  • STEllipsoidParameters — Given a SRID returns the semi major axis and flattening from the sys.spatial_reference_systems table.
  • STGeographicDistance — Computes distance in meters along parallel latitude/medidian longitude or direct.
  • STInverseVincenty — Vincenty inverse calculation between two points
  • STIsCollinear — Function that checks if a linestring’s points all lie on straight line.
  • STRemoveOffsetSegments — Removes any start/end segment in provided linestring that disappear when segments are offset.
  • STTileByNumGrids — Covers supplied geometry object with a mesh of a specific number of times in X and Y.
  • STTileGeogByPoint — Creates mesh of tiles anchored to supplied point.
  • STTileGeomByPoint — Creates mesh of tiles anchored to supplied point.
  • STisCCW — A filter for computing the orientation index of three coordinates.

Renamed

  • STOffsetLine from STLineOffset
  • STOffsetSegment from STParallelSegment

Update

  • STAzimuth
  • STBoundingDiagonal
  • STCogoFunctions
  • STDirectVincenty
  • STFindAngleBetween
  • STFindArcPointByLength
  • STFindLineIntersection
  • STFindSegmentByLengthRange
  • STFindSegmentByMeasureRange
  • STFormatNumber
  • STGeographicDistance
  • STGridSmooth
  • STInverseVincenty
  • STIsCollinear
  • STIsMeasured
  • STMBR
  • STMakeEnvelope
  • STMulti
  • STOffsetLine
  • STOffsetLineByParallel
  • STOneSidedBuffer
  • STPointToCircularArc
  • STRemoveMeasure
  • STRound
  • STSplitFunctions
  • STSquareBuffer
  • STSwapOrdinates
  • STTileGeom
  • STTileXY
  • STTiler
  • STisCCW
  • date_fns

##############################################################################

2019-10-18: SQL Server Spatial Functions.

  • Added STForceCollection – Takes polygon and returns GEOMETRYCOLLECTION of rings as polygons or linestrings or MULTILINESTRING with rings as linestrings
  • Added STMBRLongestSide – Computes Envelope or MBR of input geometry and then computes and returns the longest side of the Envelope or MBR
  • Added STMBRShortestSide – Computes Envelope or MBR of input geometry and then computes and returns the shortest side of the Envelope or MBR
  • Updated STAppend – Corrected missing case
  • Updated STMakeLineFromGeometryCollection – Improved to allow GeometryCollection to hold LineStrings not just Points.
  • Updated STParallel – Continue to improve parallel offsetting of linestrings.
  • Updated STVertices – Modified return table to include actual geometry Point.
  • Updated STDumpRings – Fixed issue with function not dumping interior rings.
  • Updated STConvertToLineString – Fixed bug with single MultiLineString in Geometry Collection.

##############################################################################

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