Go to content Go to navigation and search


Current SQL Server Blog Articles



RSS / Atom

Email me


Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Identifying Tight Radius Curves sections within LineString geometry data

Wednesday May 04 2011 at 12:20

Sometimes it is a data quality requirement for linear data (roads, pipelines, transmission lines) that curves within the lines must have a radius greater than a particular amount.

Recently a customer asked me to write some TSQL functions to help them run data quality checks over linear data loaded into a SQL Server 2008 geometry column directly from a CAD package.

The function I created is called CheckRadii and is as follows:

/** ----------------------------------------------------------------------------------------
  * @function   : CheckRadii
  * @precis     : Function that checks vertices in a linestring/multilinestring to see if
  *               the circular arc they describe have radius less than the provided amount.
  *               Each set of three vertices (which could be overlapping) that fail the test
  *               are written to a single MultiPoint object. If no circular arcs in the linestring
  *               describe a circle with radius less than the required amount a NULL geometry is returned.
  *               If another other than a (Multi)linestring is provided it is returned as is.
  * @version    : 1.0
  * @usage      : Function CheckRadii(@p_geom       geometry,
  *                                   @p_min_radius Float,
  *                                   @p_precision  int )
  *                 Return geometry
  *               eg SELECT [CheckRadii](geometry::STGeomFromText('LINESTRING(0.0 0.0,10.0 0.0,10.0 10.0)',0), 15.0,3).STAsText();
  * @param      : p_geom       : Projected (Multi)Linestring geometry
  * @paramtype  : p_geom       : geometry
  * @param      : p_min_radius : A not null value that describes the minimum radiue of any arc within the linestring.
  * @paramtype  : p_min_radius : Float
  * @param      : p_precision  : Precision of any XY value ie number of significant digits. If null then 3 is assumed (ie 1 mm): 3456.2345245 -> 3456.235 
  * @paramtype  : p_precision  : Int
  * @return     : mpoint_geom  : Projected 2D MultiPoint geometry
  * @rtnType    : mpoint_geom  : geometry
  * @note       : Supplied geometry must not be geographic: function only guaranteed for projected data.
  * @note       : Does not honour dimensions over 2.
  * @history    : Simon Greener - May 2011 - Original coding.
  * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. (http://creativecommons.org/licenses/by-sa/2.5/au/)
CREATE FUNCTION [CheckRadii](@p_geom       geometry,
                             @p_min_radius Float,
                             @p_precision  int )
RETURNS geometry 

And can be called as follows:

SELECT [CheckRadii](geometry::STGeomFromText('MULTILINESTRING(
(0.0 0.0,10.0 0.0,10.0 10.0),
(20.0 0.0,30.0 0.0,30.0 10.0))',0), 15.0,3).STAsText(); 

Some examples of running this against test road data with a 15.0 meter radius and precision of 3 (ie 1 mm) are as follows.

If anyone wishes for a similar function to be written for them, please let me know.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter