- 1. COGO/DD2DMS
- 2. COGO/DMS2DD (2008)
- 3. COGO/DMSS2DD (2008)
- 4. COGO/ST_BearingBetweenPoints (2008)
- 5. COGO/ST_NormalizeBearing
- 6. COGO/ST_PointFromBearingAndDistance (2008)
- 7. COGO/ST_PointFromCOGO
- 8. CONVERSION/STToGeography (2008)
- 9. CONVERSION/STToGeometry (2012)
- 10. EDITOR/ST_Extend (2008)
- 11. GEOPROCESSING/ST_Average
- 12. GEOPROCESSING/ST_Chop
- 13. GEOPROCESSING/ST_InsideLine
- 14. GEOPROCESSING/ST_isCollinear
- 15. GEOPROCESSING/ST_LineInsidePolygon
- 16. TOOLS/STIsGeographicSrid (2012)

[ Top ] [ Functions ]

NAME

DD2DMS-- Returns string equivalent of decimal degree numeric value.

SYNOPSIS

FunctionDD2DMS( dDecDeg in Number, pDegree in NChar default CHR(176), pMinute in NChar default '''', pSecond in NChar default '"' ) Return varchar2 Deterministic;

INPUTS

dDecDeg (Number) - Decimal degrees. pDegree (NChar) - Superscript degree value identifier eg ^ pMinute (NChar) - Superscript minute value identifier eg ' pSecond (NChar) - Superscript second value identifier eg "

RESULT

Decimal Degrees (NUMBER) - eg 22.16972222.

DESCRIPTION

This function converts a numeric decimal degree value into its textual whole-circle bearing equivalent.

EXAMPLE

select spdba.DD2DMS(15.8515065952945,'^','''','"') as dms; DMS 15^51'5.424" select spdba.DD2DMS(415.67845) as dms; dms 55°40'42.420"

AUTHOR

Simon Greener

HISTORY

Simon Greener - Jan 2013 - Original coding.

COPYRIGHT

(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

DMS2DD -- Function computes a decimal degree floating point number from individual degrees, minutes and seconds values.

SYNOPSIS

Function DMS2DD( p_dDeg in Integer, p_dMin in Integer, p_dSec in Float ) Returns Float

USAGE

SELECT spdba.DMS2DD(45,30,30) as DD; DD 45.5083333333333

DESCRIPTION

Function that computes the decimal equivalent to the supplied degrees, minutes and seconds values. No checking of the values of each of the inputs is conducted: one can supply 456 minutes if one wants to!

NOTES

Normalization of the returned value to ensure values are between 0 and 360 degrees can be conducted via the ST_NormalizeBearing function.

INPUTS

p_dDeg (integer) : Non-NULL degree value (0-360) p_dMin (integer) : Non-NULL minutes value (0-60) p_dSec (float) : Non-NULL seconds value (0-60)

RESULT

DecimalDegrees (float) : Decimal degrees equivalent value.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2011 - Original TSQL Coding for SQL Server.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

DMSS2DD -- Function computes a decimal degree floating point number from individual degrees, minutes and seconds values encoded in supplied string.

SYNOPSIS

Function DMSS2DD( p_strDegMinSec varchar ) Returns Float

USAGE

SELECT spdba.DMSS2DD('43° 0'' 50.00"S') as DD; DD -43.0138888888889

DESCRIPTION

The function parses the provided string (eg extracted from Google Earth) that contains DD MM SS.SS values, extracts and creates a single floating point decimal degrees value. No checking of the values of each of the inputs is conducted: one can supply 456 minutes if one wants to! The function honours N, S, E and W cardinal references.

NOTES

Normalization of the returned value to ensure values are between 0 and 360 degrees can be conducted via the STNormalizeBearing function.

INPUTS

p_strDegMinSec (varchar) : DD MM SS.SS description eg 43° 0'' 50.00"S

RESULT

DecimalDegrees (float) : Decimal degrees equivalent value.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2011 - Original TSQL Coding for SQL Server.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener LICENSE Creative Commons Attribution-Share Alike 2.5 Australia License. http://creativecommons.org/licenses/by-sa/2.5/au/

[ Top ] [ Functions ]

NAME

ST_BearingBetweenPoints -- Returns a (Normalized) bearing in Degrees between two non-geodetic (XY) coordinates

SYNOPSIS

Function ST_BearingBetweenPoints ( p_dE1 numeric, p_dN1 numeric, p_dE2 numeric, p_dN2 numeric ) Returns numeric

USAGE

SELECT spdba.ST_Bearing(0,0,45,45) as Bearing; Bearing 45

DESCRIPTION

Function that computes the bearing from the supplied start point (p_dx1) to the supplied end point (p_dx2). The result is expressed as a whole circle bearing in decimal degrees.

INPUTS

p_dE1 (numeric) - X ordinate of start point. p_dN1 (numeric) - Y ordinate of start point. p_dE2 (numeric) - Z ordinate of start point. p_dN2 (numeric) - M ordinate of start point.

RESULT

decimal degrees (numeric) - Bearing between point 1 and 2 from 0-360.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2011 - Original TSQL Coding for SQL Server.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_NormalizeBearing-- Function ensures supplied bearing is between 0 and 360.

SYNOPSIS

Function spdba.ST_NormalizeBearing( p_bearing float ) Returns Float

USAGE

SELECT spdba.ST_NormalizeBearing(450.39494) as bearing; bearing 90.39494

DESCRIPTION

Function that ensures supplied bearing is between 0 and 360 degrees (360 = 0).

INPUTS

p_bearing (float) : Non-NULL decimal bearing.

RESULT

bearing (float) : Bearing between 0 and 360 degrees.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2011 - Original pl/pgSQL Coding.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_PointFromBearingAndDistance -- Returns a projected point given starting point, a bearing in Degrees, and a distance (geometry SRID units).

SYNOPSIS

Function spdba.ST_PointFromBearingAndDistance ( p_dStartE in float, p_dStartN in float, p_dBearing in float, p_dDistance in float, p_iSrid in integer ) Returns float

USAGE

SELECT ST_AsEWKT(spdba.ST_PointFromBearingAndDistance (0,0,45,100,0)) as endPoint; endPoint POINT (70.711 70.711)

DESCRIPTION

Function that computes a new point given a starting coordinate, a whole circle bearing, and a distance (SRID Units). p_Srid is the SRID of the supplied start point.

NOTES

Supports planar data only.

INPUTS

p_dStartE (float) - Easting of starting point. p_dStartN (float) - Northing of starting point. p_dBearing (float) - Whole circle bearing between 0 and 360 degrees. p_dDistance (float) - Distance in SRID units from starting point to required point. p_iSrid (int) - SRID associated with p_dStartE/p_dStartN.

RESULT

point (geometry) - Point

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2011 - Original pl/pgSQL Coding.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_PointFromCOGO-- Returns a projected point given starting point, a bearing in Degrees, and a distance (geometry SRID units).

SYNOPSIS

FunctionST_PointFromCOGO( @p_gStartPoint geometry, @p_dBearing float, @p_dDistance float ) Returns geometry

USAGE

SELECT ST_AsEWKT(ST_PointFromCOGO( ST_GeomFromEWKT('SRID=28355; POINT(0,0)'), 45.0, 100.0 ) ) as endPoint; endPoint POINT (70.711 70.711)

DESCRIPTION

Is a wrapper function over STPointFromBearingAndDistance. Function that computes a new point given a starting coordinate, a whole circle bearing and a distance (SRID Units). SRID of the returned geometry is the SRID supplied start point.

INPUTS

@p_StartPoint (geometry) - Starting point. @p_dBearing (float) - Whole circle bearing between 0 and 360 degrees. @p_dDistance (float) - Distance in SRID units from starting point to required point.

RESULT

point (geometry) - Point

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2011 - Original pl/pgSQL coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STToGeography - Given a geometry object returns a geography object.

SYNOPSIS

Function STToGeography ( p_geom geometry, p_srid Int = NULL ) Returns geography

EXAMPLE

SELECT [$(owner)].[STToGeography] ( geometry::STGeomFromText('LINESTRING(147.234 -43.2345, 148.234 -43.2345)',0), 4326 ).STAsText() as geog GO geog LINESTRING(147.234 -43.2345, 148.234 -43.2345)

DESCRIPTION

Given valid geometry object this function converts it to a geography object, assigns p_srid value and returns. The p_srid value should be a valid geography SRID. Mainly useful for converting 2008 geometry objects with geographic data to 2012+ geography objects. * INPUTS p_geom (geometry) - A valid geometry object. p_srid (int) - Valid geographic SRID. If null, uses SRID associated with supplied geometry.

RESULT

geography (geography) - Geography object.

AUTHOR

Simon Greener

HISTORY

Simon Greener - Aug 20012 - Initial SQL Server 20012 implementation

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STToGeometry - Given a geography object returns a geometry object.

SYNOPSIS

Function STToGeometry ( p_geog geography, p_srid Int = NULL ) Returns geometry

EXAMPLE

SELECT [$(owner)].[STToGeometry] ( geography::STGeomFromText('LINESTRING(147.234 -43.2345, 148.234 -43.2345)',4326), NULL ).STAsText() as geom; GO geom LINESTRING (147.234 -43.2345, 148.234 -43.2345)

DESCRIPTION

Given valid geography object this function converts it to a geometry object, assigns p_srid value and returns. The p_srid value should be a valid projected SRID. Mainly useful for converting 2012 geography objects to geometry equalivalent to be able to use functions only available for geometry.

INPUTS

p_geog (geography) - A valid geographic object. p_srid (int) - Valid projected SRID. If null, uses SRID associated with supplied geography

RESULT

geometry (geometry) - Geometry object.

AUTHOR

Simon Greener

HISTORY

Simon Greener - Aug 20012 - Initial SQL Server 20012 implementation

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Extend -- Function which extends the supplied linestring required distance at its start/end or both.

SYNOPSIS

Function spdba.ST_Extend ( p_linestring geometry, p_extend_length float, p_end int, p_keep int, p_round_xy int = 3, p_round_zm int = 2 ) Returns geometry

USAGE

SELECT ST_Extend(geometry::ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),5.0,'START',1,2,1).AsTextZM() as extendedGeom; extendedGeom LINESTRING(-4.9 30.2,-3.6 31.5)

DESCRIPTION

Function that extends the supplied linestring at either its start or end (p_end), in the direction of a line formed by the first and second vertices (if START) or last and second last vertices (if END). p_end value of BOTH means line is extended at both ends. If p_keep is set to 1, the start or end vertex is kept and a new vertex added at the extended length from the start/end. If p_keep is 0, the actual first or last vertex is moved. The computed ordinates of the new geometry are rounded to p_round_xy/p_round_zm number of decimal digits of precision.

NOTES

MultiLinestrings and CircularString linestrings are not supported. Assumes planar projection eg UTM.

INPUTS

p_linestring (geometry) - Supplied geometry of type LINESTRING only. p_extend_length (float) - Length to extend linestring in SRID units. p_end (varchar5) - START means extend line at the start; END means extend at the end and BOTH means extend at both START and END of line. p_keep (int) - Keep existing first/last vertex and add new (1) vertices, or move (0) existing start/end vertex. p_round_xy (int) - Round XY ordinates to supplied decimal digits of precision. p_round_zm (int) - Round ZM ordinates to supplied decimal digits of precision.

RESULT

linestring (geometry) - Input geometry extended as instructed.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original TSQL Coding for SQL Server.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Average-- Averages ordinates of 2 Points

SYNOPSIS

CREATE OR REPLACE FUNCTION spdba.ST_Average( p_first_point in geometry, p_second_point in geometry ) RETURNS boolean

ARGUMENTS

p_first_point (geometry) -- point p_second_point (geometry) -- point

RESULT

point (geometry - Average of two points

DESCRIPTION

This function takes two points and averages the ordinates. If points have different ordinate dimensions, 2D point is returned.

EXAMPLE

select ST_AsText(spdba.ST_Average('POINT(-1 -1)'::geometry,'POINT(1 1)'::geometry)) as aPoint; aPoint POINT(0 0) select ST_AsText(spdba.ST_Average('POINTZ(-1 -1 1)'::geometry,'POINTZ(1 1 2)'::geometry)) as aPoint; aPoint POINT(0 0 1.5) select ST_AsText(spdba.ST_Average('POINTM(-1 -1 1)'::geometry,'POINTM(1 1 2)'::geometry)) as aPoint; aPoint POINT(0 0 1.5)

AUTHOR

Simon Greener

HISTORY

Simon Greener - April 2019, Original Coding

COPYRIGHT

(c) 2005-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Chop-- Splits a (multi)linestring into individual linestrings.

SYNOPSIS

CREATE OR REPLACE FUNCTION spdba.ST_Chop( p_geometry geometry, p_distance numeric, p_round_xy int default 3, -- For comparing ordinates of xy coordinates p_treat_as_one int default 0, -- Treat MultiLineString as single object if 1, otherwise break in to LineStrings if 0 p_mode int default 0 -- Processing mode: 0 returns all segments even if < p_distance; 1 causes segment < p_distance to be merged with previous segment. ) RETURNS SETOF spdba.segments

ARGUMENTS

p_geometry (geometry) -- LineString or MultiLineString. p_distance (numeric) -- Desired length of individual segmenets generated by function. p_round_xy (int) -- For comparing ordinates of xy coordinates. Result ordinates are not rounded (See ST_SnapToGrid). p_treat_as_one (int) -- Treat MultiLineString as single object if 1, otherwise break in to LineStrings if 0 p_mode (int) -- Processing mode: 0 returns all segments even if < p_distance; 1 causes segment < p_distance to be merged with previous segment.

RESULT

Set of LineStrings (set of geometry) -- Collection / Set of individual linestrings.

DESCRIPTION

This function chops/cuts the input geometry into linestring geometries (segments) of length p_distance. If p_treat_as_one is set to 1, and p_geometry is a MultiLineString, the segments are created by cutting up the MultiLineString as if it were a single linestring. This can mean that a returned segment cross from one linestring to the next thus returning it as a MultiLineString. If p_treat_as_one is set to 0, each LineString is chopped without reference to any other LineString. If the last segment to be returned is < p_distance and p_mode = 0 it is returned; if p_mode = 1 the segment is joined to the previous LineString such that the returned segment is > p_distance but < p_distance * 2.0

EXAMPLE

-- Chop MultiLineString's individual LineStrings last segment is balance of length (< p_distance) select (a.segs).id, ROUND(ST_Length((a.segs).segment)::numeric,3) as len, ST_AsText( ST_SnapToGrid((a.segs).segment,0.001) ) as geom from (select spdba.ST_Chop( /* p_geometry */ ST_GeomFromText('MULTILINESTRING((0 0,100 0),(200 0,300 300))'), /* p_distance */ 40.0, /* p_round_xy */ 3, /* p_treat_as_one */ 0, /* p_mode */ 0 ) as segs ) as a; id len geom text numeric geometry ---- ------- ------------------------------------------- 1 40.000 LINESTRING(0 0,40 0) 2 40.000 LINESTRING(40 0,80 0) 3 20.000 LINESTRING(80 0,100 0) 4 40.000 LINESTRING(200 0,212.649 37.947) 5 40.000 LINESTRING(212.649 37.947,225.298 75.895) 6 40.000 LINESTRING(225.298 75.895,237.947 113.842) 7 40.000 LINESTRING(237.947 113.842,250.596 151.789) 8 40.000 LINESTRING(250.596 151.789,263.246 189.737) 9 40.000 LINESTRING(263.246 189.737,275.895 227.684) 10 40.000 LINESTRING(275.895 227.684,288.544 265.631) 11 36.228 LINESTRING(288.544 265.631,300 300) -- Process individual LineStrings of the MultiLineString with last segment merge select (a.segs).id, ROUND(ST_Length((a.segs).segment)::numeric,3) as len, ST_AsText( ST_SnapToGrid((a.segs).segment,0.001) ) as geom from (select spdba.ST_Chop( /* p_geometry */ ST_GeomFromText('MULTILINESTRING((0 0,100 0),(200 0,300 300))'), /* p_distance */ 40.0, /* p_round_xy */ 3, /* p_treat_as_one */ 0, /* p_mode */ 1 ) as segs ) as a; id len geom text numeric geometry ----- ------- ------------------------------------------------------ 1 40.000 LINESTRING(0 0,40 0) 2 60.000 LINESTRING(40 0,80 0,100 0) 4 40.000 LINESTRING(200 0,212.649 37.947) 5 40.000 LINESTRING(212.649 37.947,225.298 75.895) 6 40.000 LINESTRING(225.298 75.895,237.947 113.842) 7 40.000 LINESTRING(237.947 113.842,250.596 151.789) 8 40.000 LINESTRING(250.596 151.789,263.246 189.737) 9 40.000 LINESTRING(263.246 189.737,275.895 227.684) 10 76.228 LINESTRING(275.895 227.684,288.544 265.631,300 300) -- Treat MultiLineString as one select (a.segs).id, ROUND(ST_Length((a.segs).segment)::numeric,3) as len, ST_AsText( ST_SnapToGrid((a.segs).segment,0.001) ) as geom from (select spdba.ST_Chop( /* p_geometry */ ST_GeomFromText('MULTILINESTRING((0 0,100 0),(200 0,300 300))'), /* p_distance */ 40.0, /* p_round_xy */ 3, /* p_treat_as_one */ 1, /* p_mode */ 1 ) as segs ) as a; id len geom text numeric geometry ----- ------- ------------------------------------------------------ 1 40.000 LINESTRING(0 0,40 0) 2 40.000 LINESTRING(40 0,80 0) 3 40.000 MULTILINESTRING((80 0,100 0),(200 0,206.325 18.974)) 4 40.000 LINESTRING(206.325 18.974,218.974 56.921) 5 40.000 LINESTRING(218.974 56.921,231.623 94.868) 6 40.000 LINESTRING(231.623 94.868,244.272 132.816) 7 40.000 LINESTRING(244.272 132.816,256.921 170.763) 8 40.000 LINESTRING(256.921 170.763,269.57 208.71) 9 40.000 LINESTRING(269.57 208.71,282.219 246.658) 10 56.228 LINESTRING(282.219 246.658,294.868 284.605,300 300)

AUTHOR

Simon Greener

HISTORY

Simon Greener - November 2018, Original Coding

COPYRIGHT

(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_InsideLine-- Generates a line that is fitted inside a polygon.

SYNOPSIS

CREATE OR REPLACE FUNCTION spdba.ST_insideLine( p_point in geometry, p_direction_start in numeric, p_direction_end in numeric default null, p_polygon in geometry, p_dIncrement in numeric default 5.0 ) RETURNS geometry

ARGUMENTS

p_point (geometry) -- Starting point which must fall inside p_polygon p_direction_start (numeric) -- Whole circle bearing in degrees defining direction of line from p_point to the start of the line p_direction_end (numeric) -- Whole circle bearing in degrees defining direction of line from p_point to the end of the line p_polygon (geometry) -- Polygon for which the inside line must be fitted. p_dIncrement (numeric) -- Line increment (distance) for incrementally extending a line, and testing line to find its boundary intersection point.

RESULT

line (geometry) -- Line at desired bearing which touches p_polygon at line start and end.

DESCRIPTION

This function creates a line that lies inside p_polygon but whose start and end points touch p_polygon's boundary. Line is generated from a starting point and two bearings Algorithm generates a line from supplied point to the line's start point by extending and testing the line by p_dIncrement until it finds a p_polygon boundary. After finding point for first half of line, the second line is generated using p_direction_end. If p_direction_end is null or the same as p_direction_start, a default direction of p_direction_start - 180.0 is used The algorithm uses a stepping approach: it first creates a line at p_direction_start for p_dIncrement distance. If the line does not touch a p_polygon boundary point it increases the line length by p_dIncrement and tests again. The stepping process continues until the line touches or crosses the boundary. Once the two halves are created, they are unioned together and the resulting line returned.

EXAMPLE

select ST_AsText( spdba.ST_insideLine( ST_SetSrid(ST_Point(82,60),28355), 0.0::numeric, 0.0::numeric, ST_GeomFromText('POLYGON((8.003 66.926, 11.164 70.086, 13.692 70.929, 19.171 70.929, 23.385 70.508, 26.546 70.297, 33.078 71.983, 36.871 74.301, 43.824 75.776, 51.199 75.986, 59.206 74.511, 62.788 71.772, 64.685 70.719, 73.535 71.351, 78.592 69.244, 83.649 64.187, 84.913 62.501, 86.178 57.022, 85.756 53.019, 85.124 49.226, 86.81 45.433, 87.863 40.376, 89.338 37.215, 89.338 32.58, 87.653 27.522, 83.438 18.462, 81.12 15.933, 74.799 17.619, 77.538 25.205, 80.067 30.472, 80.488 37.215, 78.381 41.219, 75.22 53.229, 72.06 60.394, 62.999 63.133, 52.463 65.451, 46.353 66.926, 37.714 63.344, 29.496 62.501, 20.646 61.447, 14.114 62.922, 9.899 61.447, 3.157 63.765, 3.367 64.187, 8.003 66.926))',28355), 5.0) ) as geom ; geom text LINESTRING(82 16.8931035375324,82 60,82 65.836)

NOTES

This is a simplistic approach that is likely to perform slowly.

AUTHOR

Simon Greener

HISTORY

Simon Greener - November 2018, Original Coding

COPYRIGHT

(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_isCollinear-- Checks three points are collinear

SYNOPSIS

CREATE OR REPLACE FUNCTION spdba.ST_insideLine( p_start_point in geometry, p_mid_point in geometry, p_end_point in geometry ) RETURNS boolean

ARGUMENTS

p_start_point (geometry) -- Starting point p_mid_point (geometry) -- Mid point p_end_point (geometry) -- End point

RESULT

true or false (boolean) -- Checks if three points are collinear.

DESCRIPTION

This function takes three points and determines if any combination of them forms a straight line (ie are collinear).

EXAMPLE

select spdba.ST_IsCollinear('POINT(0 0)'::geometry, 'POINT(-1 -1)'::geometry,'POINT(1 1)'::geometry); st_iscollinear true select spdba.ST_IsCollinear('POINT(-1 -1)'::geometry,'POINT(0 0)'::geometry,'POINT(1 1)'::geometry); st_iscollinear true select spdba.ST_IsCollinear('POINT(-1 -1)'::geometry,'POINT(0 0)'::geometry,'POINT(1 2)'::geometry); st_iscollinear false

AUTHOR

Simon Greener

HISTORY

Simon Greener - February 2019, Original Coding

COPYRIGHT

(c) 2005-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_LineInsidePolygon-- Generates line and clips to polygon returning part p_point is within

SYNOPSIS

CREATE OR REPLACE FUNCTION spdba.ST_LineInsidePolygon( p_point in geometry, p_bearing in numeric, p_length in numeric, p_polygon in geometry ) RETURNS geometry

ARGUMENTS

p_point (geometry) -- Starting point which must fall inside p_polygon p_bearing (numeric) -- Whole circle bearing in degrees defining direction of line from p_point to the start of the line p_length (numeric) -- Line length. p_polygon (geometry) -- Polygon for which the inside line must be fitted.

RESULT

line (geometry) -- Line at desired bearing which is clipped to correct polygon part and touches p_polygon.

DESCRIPTION

This function creates a line that lies inside p_polygon but whose start and end points touch p_polygon's boundary. Line is generated from a starting point and bearings The algorithm creates line from supplied point the length of the diagonal of the ST_BoundingRectangle of the polygon. It then intersects the created line with the polygon to create a set of intersecting lines. Each line is processsed to find the one that contains the starting point; this line is returned.

EXAMPLE

select ST_AsText( spdba.ST_LineInsidePolygon( ST_SetSrid(ST_Point(82,60),28355), 0.0::numeric, 5.0, ST_GeomFromText('POLYGON((8.003 66.926, 11.164 70.086, 13.692 70.929, 19.171 70.929, 23.385 70.508, 26.546 70.297, 33.078 71.983, 36.871 74.301, 43.824 75.776, 51.199 75.986, 59.206 74.511, 62.788 71.772, 64.685 70.719, 73.535 71.351, 78.592 69.244, 83.649 64.187, 84.913 62.501, 86.178 57.022, 85.756 53.019, 85.124 49.226, 86.81 45.433, 87.863 40.376, 89.338 37.215, 89.338 32.58, 87.653 27.522, 83.438 18.462, 81.12 15.933, 74.799 17.619, 77.538 25.205, 80.067 30.472, 80.488 37.215, 78.381 41.219, 75.22 53.229, 72.06 60.394, 62.999 63.133, 52.463 65.451, 46.353 66.926, 37.714 63.344, 29.496 62.501, 20.646 61.447, 14.114 62.922, 9.899 61.447, 3.157 63.765, 3.367 64.187, 8.003 66.926))',28355) ) as geom ; geom text LINESTRING(82 16.8931035375324,82 60,82 65.836)

NOTES

This is a simplistic approach that is likely to perform slowly.

AUTHOR

Simon Greener

HISTORY

Simon Greener - February 2019, Original Coding

COPYRIGHT

(c) 2005-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STIsGeographicSrid -- Checks p_srid to see if exists in sys.spatial_reference_systems table (which holds geodetic SRIDS)

SYNOPSIS

Function STIsGeographicSrid ( p_srid int ) Returns int

USAGE

SELECT [$(owner)].[STIsGeographicSrid](4283) as isGeographicSrid GO isGeographicSrid ---------------- 1

DESCRIPTION

All geographic/geodetic SRIDs are stored in the sys.spatial_reference_systems table. This function checks to see if the supplied SRID is in that table. If it is, 1 is returned otherwise 0.

INPUTS

p_srid (int) - Srid value.

RESULT

Y/N (int) - 1 if True; 0 if False

AUTHOR

Simon Greener

HISTORY

Simon Greener - June 2018 - Original TSQL Coding for SQL Server. Simon Greener - February 2019 - Original pgPLSQL Coding for PostGIS

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener