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-29 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 STTileByGeogPoint has been radically overhauled as the previous function suffered from error propogation which has now been corrected.

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

Updated

Numerous improvements to documentation, code formatting, bug fixes.

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

  • STMakePolygonGeog — Function that constructs a 5 point polygon from supplied geographic points.
  • STCollectionInsertN — Inserts geometry at specified position in the geometry collection.
  • STCollectionAppendGeog — Inserts geography at end of geography collection.
  • STCollectionInsertNGeog — Inserts geography 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 — Returns (possibly offset) point geometry at supplied length ratio along linestring.
  • 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