Oracle PL/SQL Object-based Spatial Procedures (Including LRS)

Description

Spatial Companion for Oracle – PL/SQL Edition (SC4OP) is a set (over 150) of PL/SQL Objects that are aimed at developers creating stored procedures processing spatial data. All licensable Oracle functions in the MDSYS.SDO_GEOM and MDSYS.SDO_UTIL packages are included but wrapped within a single consistent interface. (Every attempt has been made to ensure use is in agreement with the licensed version.)

The collection contains a full Linear Referencing System (LRS) implementation.

The objects include representations of single Vertices (T_Vertex), (2 point) LineString / (3 point) CircularCurve (T_Segment), and a geometry object (T_Geometry).

All functionality is accessed via object “dot notation”.

If a request for new function is received, and implemented, the function will be provided for the original purchaser and any future persons who download the package without the need to redownload as long as a valid email address was associated with the original download.

The documentation for the objects and functions is to be found here:

Oracle Objects & Packages (Single Page)
Oracle Objects & Packages (Multiple Pages)

The value of the collection to the customer, besides a single consistent interface is that the cost of $45 when divided by the number of functions, 235, amounts to 20 cents per function.

A summary of the functions available is as follows:

T_Geometry General Functions
T_GEOMETRY Object Type
ST_Add_Segment — Adds a segment to an existing geometry.
ST_Affine — Applies a 3d affine transformation to the geometry to do things like translate, rotate, scale in one step.
ST_Append — Appends sdo_geometry to underlying sdo_geometry.
ST_Area — Returns area of underlying polygon sdo_geometry
ST_AsEWKT — Exports 3/4D mdsys.sdo_geometry object to an Extended Well Known Text (WKT) format.
ST_AsTText — Returns text Description of T_GEOMETRY
ST_AsText — Exports mdsys.sdo_geometry object to its Well Known Text (WKT) representation by executing, and returning, result of mdsys.sdo_geometry method get_wkt().
ST_AsWKB — Exports mdsys.sdo_geometry object to its Well Known Binary (WKB) representation by executing, and returning, result of mdsys.sdo_geometry method get_wkb().
ST_AsWKT — Exports mdsys.sdo_geometry object to its Well Known Text (WKT) representation by executing, and returning, result of mdsys.sdo_geometry method get_wkt().
ST_Boundary — Returns All Outer Rings of a polygon or multipolygon as a single linestring/multilinestring.
ST_Buffer — Creates a buffer around input geometry.
ST_Centroid_A — Creates a centroid for a polygon mdsys.sdo_geometry object.
ST_Centroid_L — Creates a centroid for a linestring mdsys.sdo_geometry object.
ST_Centroid_P — Creates a centroid for a multipoint object.
ST_Cogo2Line — Creates linestring from supplied bearing and distance instructions.
ST_Cogo2Polygon — Creates single polygon exterior ring from supplied bearing and distance instructions.
ST_Compress — Turns coordinate array of linestring/polygon into equivalent to MoveTo and LineTo components.
ST_Concat_Line — Adds supplied linestring to start/end of underlying linestring depending on geometric relationship.
ST_CoordDimension — Returns Coordinate Dimension of mdsys.sdo_geometry object.
ST_Decompress — Reverse Compress applied by ST_Compress with same p_factor_applied and p_origin.
ST_DeleteVertex — Function which deletes the coordinate at position p_vertex_id from the underlying geometry.
ST_Densify — Implements a basic geometry densification algorithm.
ST_Difference — Returns the spatial difference between two sdo_geometry objects.
ST_DimInfo2Rectangle — Converts diminfo structure in XXX_SDO_GEOM_METADATA to a polygon with an optimized rectangle exterior ring.
ST_Dimension — Returns spatial dimension of underlying geometry.
ST_Dims — Returns number of ordinate dimensions
ST_Distance — Returns distance from current T_geometry (SELF) to supplied T_GEOMETRY.
ST_Dump — Extracts all parts of a multipart linestring, polygon, or collection geometry.
ST_ElemInfo — Returns underlying mdsys.sdo_geometry’s SDO_ELEM_INFO array as a Set of T_ElemInfo objects.
ST_Elem_Info_Equal — Compares current object (SELF) geometry’s GEOM.SDO_ELEM_INFO object with supplied p_elem_info mdsys.sdo_elem_info_array.
ST_ElementTypeAt — Element_Type value in Sdo_Elem_Info triplet index.
ST_EndPoint — Returns last Point in underlying geometry.
ST_EndSegment — Returns last Segment in underlying geometry.
ST_EndVertex — Returns last vertex in underlying geometry.
ST_Envelope — Returns lower left and upper right coordinates of underlying geometry’s envelope.
ST_Equals — Compares current object (SELF) with supplied T_GEOMETRY.
ST_Extend — Function that lengthens underlying linestring at one or both ends.
ST_ExteriorRing — Returns All Outer Rings of a polygon or multipolygon.
ST_ExtractRings — Extracts all rings of a polygon/multipolygon into a set of simple T_GEOMETRY polygon objects.
ST_ExtractRings — Extracts all single geometry objects from geometry collection, multi linestring or multi polygon.
ST_ExtractRings — Extracts required geometry types (point, line, polygon) from underlying geometry collection.
ST_FilterRings — Removes rings from polygon/multipolygon below supplied area.
ST_Find_Offset(p_point in mdsys.sdo_geometry…);
ST_FixOrdinates — Allows calculations expressed as formula to change the ordinates of the underlying Geometry
ST_FixZ — Replaces and measure/elevation NULL values with supplied value eg -9999
ST_Flip_Segments — Turns linestring and polygon rings into segments and then flips each vector until all point in the same direction.
ST_FromEWKT — Implements an import method for Extended Well Known Text including EWKT with Z and M ordinates..
ST_FromText — This is a wrapper function that has the ST_FromText name common to users of PostGIS.
ST_GType — Returns underlying mdsys.sdo_geometry’s geometry type by executing mdsys.sdo_geometry method get_gtype().
ST_Geometry2DimInfo — Converts any non-null geometry into an SDO_DIM_ARRAY capable of being written to USER_SDO_GEOM_METADATA by computing MBR of object.
ST_GeometryType — Returns underlying mdsys.sdo_geometry’s SQLMM Geometry Type.
ST_HasDimension — Returns spatial dimension of underlying geometry.
ST_HasZ — Checks if underlying geometry has z ordinate.
ST_InsertVertex — Function which inserts new coordinate (p_vertex) at position p_vertex.id into the supplied geometry.
ST_Intersection — Returns the spatial intersection of two sdo_geometry objects.
ST_Length — Returns length of underlying linestring or polygon (rings) sdo_geometry
ST_Line2Cogo — Creates Cogo Instructions from linestring segments.
ST_LineShift — Moves linestring parallel to imaginary line drawnn from first to last vertex.
ST_MBR — Returns lower left and upper right coordinates of underlying geometry’s minimum bounding rectangle (MBR).
ST_Multi — Converts any single sdo_geometry (point, line or polygon) to its multi equivalent (multipoint, multiline, multipolygon).
ST_Multi_Centroid — Computes centroid for all parts of supplied multilinestring or multipolygon.
ST_NumELems — Returns number of top level elements of the underlying mdsys.sdo_geometry.
ST_NumElementInfo — Returns number of SDO_ELEM_INFO triplets that describe geometry.
ST_NumGeometries — Returns number of top level geometry elements in underlying mdsys.sdo_geometry.
ST_NumInteriorRing — Returns number of interior rings in underlying polygon mdsys.sdo_geometry.
ST_NumPoints — Returns number of points (coordinates) in underlying mdsys.sdo_geometry.
ST_NumRectangles — A function that returns the number of optimized rectangles in the underlying (multi)polygon geometry.
ST_NumRings — Returns Number of Rings of specified type in a polygon/mutlipolygon.
ST_NumSegments — Returns number of two-point segments in the underlying linear or polygon geometry.
ST_NumSubElements — Interprets underlying mdsys.sdo_geometry’s SDO_ELEM_INFO array returning either total count of elements or just underlying sub-elements.
ST_NumVertices — Returns number of vertices (coordinates) in underlying mdsys.sdo_geometry.
ST_OneSidedBuffer — Creates a square buffer to left or right of a linestring.
ST_Ord2SdoPoint — Changes point encoding from SDO_ELEM_INFO_ARRAY/SDO_ORDINATE_ARRAY to SDO_POINT_TYPE.
ST_Ordinates_Equal — Compares current object (SELF) geometry’s GEOM.sdo_ordinates with supplied mdsys.sdo_ordinate_array
ST_Parallel — Moves linestring parts of underlying Geometry left (-ve) or right (+ve) depending of distance value.
ST_PointN — Returns point (coordinate) at position p_point in underlying geometry.
ST_Polygon2Line — Converts Polygon or MultiPolygon rings to equivalent linestrings.
ST_Polygon2Rectangle — Converts optimized rectangles (rings) coded as 5 vertex/point polygons to rectangles.
ST_Rectangle — Creates a rectangle polygon around all point objects within the underlying mdsys.sdo_geometry.
ST_Rectangle2Polygon — Converts Single Optimized Rectangle Exterior Ring Polygon element to its 5 vertex polygons.
ST_Reduce — Function that can shorten underlying linestring at one or both ends.
ST_Reflect — Function which reflects the underlying geometry to a new location using the provided parameters.
ST_Reflect(p_reflect_geom,-1) POINT (0.0 0.0)
ST_Relate — Determines spatial relations between two geometry instances.
ST_Release — Returns Version Number for the code in the type.
ST_RemoveCollinearPoints — Removes any collinear points in a linestring
ST_RemoveDuplicateVertices — Function that removes duplicate points in underlying linear or polygonal geometry.
ST_RemoveInnerRings — Removes all interior/inner rings from polygon/multipolygon.
ST_Reverse_Geometry — Reverses non-point geometries
ST_Reverse_Linestring — Reverses linestring including multi-linestring.
ST_RotTransScale — Function which reflects the underlying geometry to a new location using the provided parameters.
ST_Rotate — Function which rotates the underlying geometry around a provided point or its centre (of MBR).
ST_Round — Rounds X,Y,Z and w ordinates to passed in decimal digits of precision.
ST_SDO_GType — Returns underlying mdsys.sdo_geometry’s SDO_GTYPE attribute.
ST_SRID — Returns underlying mdsys.sdo_geometry’s SDO_SRID attribute.
ST_Scale — Function which scales the ordinates of the underlying geometry.
ST_SdoPoint2Ord — Changes point encoding from SDO_POINT_TYPE to SDO_ELEM_INFO_ARRAY/SDO_ORDINATE_ARRAY.
ST_Sdo_Point_Equal — Compares current object (SELF) geometry’s GEOM.SDO_POINT with supplied mdsys.sdo_point_type
ST_SegmentN — Returns the segment referenced by p_segment in the underlying linear or polygonal geometry.
ST_Segmentize — This function processes underlying non-point geometries and returns all segments that fall within the defined parameters eg measures covered by range p_start_value .. p_end_value .
ST_SetPoint — Sets, or replaces, SDO_POINT_TYPE element of underlying SDO_GEOMETRY.
ST_SetPrecision — Sets dPrecision property value of object after construction.
ST_SetProjection — Sets projected property of object after query of MDSYS CS metadata.
ST_SetSRID — Sets SDO_SRID for underlying geometry object.
ST_SetSdoGtype — Sets SDO_GTYPE for underlying geometry object.
ST_SetTolerance — Sets tolerance value of object after construction.
ST_SmoothTile — Smoothly polygon created from raster to segment conversion
ST_Snap — The function snaps a point to a linestring(2002) or multi-linestring (2006).
ST_Snap which is called by this function can return more than one point if p_point was equidistant from two
ST_SnapN — The function snaps a point to a (multi)linestring (2002/2006) and returns the requested point as a single geometry.
ST_Split — Splits linestring or multi-linestring object at closest point on linestring to supplied point.
ST_Split_Segments — Splits geometry at nearest point on the geometry to supplied point/vertex.
ST_SquareBuffer — Creates a square buffer around (multi)linestrings.
ST_StartPoint — Returns first Point in underlying geometry.
ST_StartSegment — Returns first Segment in underlying geometry.
ST_StartVertex — Returns first vertex in underlying geometry.
ST_SwapOrdinates — Allows for swapping ordinate pairs in a geometry.
ST_Tile — Covers envelope of supplied goemetry with a mesh of tiles of size p_Tile_X and p_Tile_Y.
ST_To2D — Converts underlying 3D or 4D mdsys.sdo_geometry to 2D (xy).
ST_To3D — Converts underlying 2D or 4D geometry to a 3D geometry.
ST_Translate — Function which translates the underlying geometry to a new location.
ST_TravellingSalesman — Constructs a path through a set of points.
ST_UpdateVertex — Function that updates (replaces) all geometry points that are equal to the supplied point with the replacement point.
ST_UpdateVertex — Function which updates the coordinate at position v_vertex.id of the underlying geometry with ordinates in v_vertex.x etc.
ST_VertexN — Returns number of vertices (coordinates) in underlying mdsys.sdo_geometry.
ST_Vertices — Extracts all vertices of the underlying geometry, and outputs them as a pipelined set of T_Vertex objects.
ST_Which_Side — Returns the side the supplied point lies on.
ST_hasCircularArcs — A function that tests whether underlying mdsys.sdo_geometry contains circular arcs.
ST_hasM — Tests geometry to see if coordinates include a measure.
ST_hasRectangles — A function that tests whether underlying mdsys.sdo_geometry contains optimized rectangles.
ST_inCircularArc — A function that checks if the provided point is part of a circular arc.
ST_isClosed — Checks if underlying geometry object is simple as per the OGC.
ST_isEmpty — Checks if underlying geometry object is empty.
ST_isOrientedPoint — A function that tests whether underlying mdsys.sdo_geometry is an oriented point.
ST_isSimple — Checks if underlying geometry object is simple as per the OGC.
ST_isValid — Executes sdo_geom.validate_geometry or validate_geometry_with_context against underlying geometry and returns value.
ST_isValidContext — Executes sdo_geom.validate_geometry_with_context against underlying geometry and returns value.
OrderBy — Allows for use of T_GEOMETRY objects in SQL ORDER BY clauses.

T_GEOMETRY Linear Referencing System (LRS)
ST_LRS_Add_Measure — Adds measures to 2D (multi)linestring.
ST_LRS_Concatenate — Rescales geometry measures and optionally offsets them, stretching the geometry.
ST_LRS_Dim — Tests underlying mdsys.sdo_geometry to see if coordinates include a measure ordinate and returns measure ordinate’s position.
ST_LRS_End_Measure — Returns M value of last vertex in measured geometry.
ST_LRS_Find_Measure — Snaps input point to measured linestring returning measure value(s)
ST_LRS_Find_MeasureN — Returns nominated measure nearest to supplied point if it exists.
ST_LRS_Find_Offset — Returns smallest (perpendicular) offset from supplied point to the linestring.
ST_LRS_Get_Measure — The function returns the measure of the T_GEOMETRY point object.
ST_LRS_Intersection — Intersects input geometry against measured linestring.
ST_LRS_Is_Measure_Decreasing — Checks if M values decrease in value over all of the linestring.
ST_LRS_Is_Measure_Increasing — Checks if M values increase in value over all of the linestring.
ST_LRS_Is_Shape_Pt_Measure — Checks if M values increase in value over all of the linestring.
ST_LRS_Locate_Between — Converts supplied measures into single point or linestring.
ST_LRS_Locate_Measure — Returns point geometry at supplied measure along linestring.
ST_LRS_Locate_Measures — Converts supplied measures into single point or linestring.
ST_LRS_Locate_Along — Wrapper over ST_LRS_Locate_Measure
ST_LRS_Locate_Point — Wrapper over ST_LRS_Locate_Measure
ST_LRS_Measure_Range — Returns Last Vertex M Value minus First Vertex M Value.
ST_LRS_Measure_To_Percentage — Converts supplied M value to percentage of M range.
ST_LRS_Percentage_To_Measure — Converts supplied Percentage value to Measure.
ST_LRS_Project_Point — The function uses ST_Snap to snap a point to a linestring(2002) or multi-linestring (2006).
ST_LRS_Reset_Measure — Wipes all existing assigned measures.
ST_LRS_Reverse_Measure — Reverses vertices measures: first becomes last, second becomes second last etc.
ST_LRS_Scale_Measures — Rescales geometry measures and optionally offsets them, stretching the geometry.
ST_LRS_Start_Measure — Returns M value of first vertex in measured geometry.
ST_LRS_Update_Measures — Updates existing measures.
ST_LRS_Valid_Geometry — Checks if underlying LRS linestring is valid.
ST_LRS_Valid_Measure — Checks if supplied measure falls within the linestring’s measure range.
ST_LRS_Valid_Point — Checks if underlying LRS point is valid.
ST_LRS_Valid_Segment — Checks if underlying LRS linestring is valid.
ST_LRS_isMeasured — Tests geometry to see if coordinates include a measure.

T_SEGMENT Object Type
ST_AddCurveBetweenSegments — Adds Circular Curve between two segments
ST_AsText — Returns text Description of underlying segment
ST_Bearing — Returns Bearing, in degrees, from start to end (possibly normalized to 0..360 degree range.
ST_Closest — Finds nearest point on line where supplied vertex would fall (snap to).
ST_ComputeTangentLine — Computes point that would define a tandential line at the start or end of a circular arc
ST_ComputeTangentPoint — Computes point that would define a tandential line at the start, mid or end point of a circular arc
ST_Densify — Implements a basic geometry densification algorithm.
ST_Dims — Returns number of ordinate dimensions
ST_Distance — Returns Distance from segment supplied T_Vertex (Wrapper)
ST_Equals — Compares current object (SELF) with supplied segment.
ST_FindCircle — Finds a centre X and Y and Radius from three points of underlying circular arc.
ST_Intersect — Computes intersection point between two 2D or 3D segments, returning a single intersection vertex.
ST_IntersectDescription — Interprets intersection that results from a call to STIntersectionDetail with same parameter values.
ST_IntersectDetail — Computes intersecton point between two 2D segments.
ST_LRS_Compute_Measure — Computes measure for supplied p_vertex against underlying LRS measured T_SEGMENT.
ST_LRS_Dim — Tests segment to see if coordinates include a measure ordinate and returns measure ordinate’s position.
ST_LRS_Measure_Length — Returns difference between end measure and start measure of segment.
ST_Length — Returns Length of the segment
ST_LineSubstring — Creates a new segment by cutting out the line defined by p_start_fraction and p_end_fraction.
ST_Merge — Merge two segments
ST_OffsetBetween — Computes offset point on the bisector between two vertices.
ST_OffsetPoint — Creates a point described by a ratio along the
ST_Parallel — Moves segment parallel the provided p_offset distance.
ST_Reverse — Reverses underlying segment’s start and end coordinates.
ST_Round — Rounds X,Y,Z and w ordinates of segment’s coordinates to passed in precision.
ST_SRID — Returns the object’s SDO_SRID attribute value.
ST_SdoGeometry — Returns segment as a suitably encoded MDSYS.SDO_GEOMETRY object.
ST_Self — Handy method for use with TABLE(T_Segments) to return element as T_Segment object.
ST_To2D — Constructs a 2D segment from the underlying segment which can have any dimension.
ST_To3D — Constructs a 3D segment from the underlying segment which can have any dimension.
ST_UpdateCoordinate — Function which updates the start, mid or end coordinate depending on p_which.
ST_hasM — Tests segment to see if coordinates include a measure.
ST_hasZ — Tests segment to see if coordinates include a Z ordinate.
ST_isCircularArc — Checks if segment is a CircularArc
ST_isEmpty — Checks if segment has any valid data.
ST_isReversed — Returns 1 (true) if the underlying segment has its start/end coordinates reversed to supplied segment.
OrderBy — Function that allows T_SEGMENT objects to be used in SELECT ORDER BY clause

T_VERTEX Object Type
ST_AsCoordString — Returns text Description of Vertex
ST_AsText — Returns text Description of Vertex
ST_Bearing — Returns bearing from SELF to supplied T_Vertex.
ST_Dims — Returns number of ordinate dimensions
ST_Distance — Returns distance from current vertex (SELF) to supplied T_Vertex.
ST_Equals — Compares current object (SELF) with supplied vertex.
ST_FromBearingAndDistance — Returns new T_Vertex given bearing and distance.
ST_HasM — Tests vertex to see if coordinates include a measure.
ST_LRS_Set_Measure — Sets measure attribute, adjusts sdo_gtype.
ST_Lrs_Dim — Tests vertex to see if coordinates include a measure ordinate and returns measure ordinate’s position.
ST_Round — Rounds X,Y,Z and w ordinates to passed in precision.
ST_SdoGeometry — Returns Vertex as a suitably encoded MDSYS.SDO_GEOMETRY object.
ST_SdoPointType — Returns vertex X,Y and Z ordinates as a MDSYS.SDO_POINT_TYPE object.
ST_Self — Handy method for use with TABLE(T_Vertices) to return element as T_Vertex object.
ST_SubtendedAngle — Returns angle subtended by p_start_vertex/SELF/p_end_vertex
ST_To2D — Removes and Z or measured attributes, adjusts sdo_gtype.
ST_To3D — Adds Z attribute, adjusts sdo_gtype.
ST_VertexType — Returns vertex ordinates as a MDSYS.VERTEX_TYPE object.
ST_WithinTolerance — Discovers whether supplied vertex is within tolerance of current object vertex (SELF).
ST_hasZ — Tests vertex to see if coordinates include a Z ordinate.
ST_isEmpty — Checks if Vertex data exists or not.
ST_W — Returns W/M ordinate of vertex.
ST_X — Returns X of vertex.
ST_Y — Returns Y of vertex.
ST_Z — Returns Z of vertex.
ST_SDO_GTYPE — Returns SDO_GTYPE of vertex.
ST_SRID — Returns SRID of vertex.
OrderBy — Allows for use of T_VERTEX objects in SQL ORDER BY clauses.