Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
COGO: Finding centre and radius of a curve defined by three points (PostGIS)
Recently I had need to convert a PL/SQL Oracle Spatial function I created years ago called FindCircle to SQL Server 2008 for use in another project. That function was original work already released to the public domain as part of my free COGO package for Oracle. Here is that function
for SQL Server.
Note that I have a schema call cogo in which I create functions like this. You can use anything you like.
/** ---------------------------------------------------------------------------------------- * @function : FindCircle * @precis : Function that determines if three points form a circle. If so a table containing * centre and radius is returned. If not, a null table is returned. * @version : 1.0 * @param : p_pt1 : First point in curve * @param : p_pt2 : Second point in curve * @param : p_pt3 : Third point in curve * @return : geometry : In which X,Y ordinates are the centre X, Y and the Z being the radius of found circle * or NULL if three points do not form a circle. * @history : Simon Greener - Feb 2012 - Original coding. * @copyright : Simon Greener @ 2012 * Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. (http://creativecommons.org/licenses/by-sa/2.5/au/) **/ CREATE OR REPLACE FUNCTION Find_Circle(p_pt1 geometry, p_pt2 geometry, p_pt3 geometry) RETURNS geometry AS $BODY$ DECLARE v_Centre geometry; v_radius NUMERIC; v_CX NUMERIC; v_CY NUMERIC; v_dA NUMERIC; v_dB NUMERIC; v_dC NUMERIC; v_dD NUMERIC; v_dE NUMERIC; v_dF NUMERIC; v_dG NUMERIC; BEGIN IF ( p_pt1 IS NULL OR p_pt2 IS NULL OR p_pt3 IS NULL ) THEN RAISE EXCEPTION 'All supplied points must be not null.'; RETURN NULL; END IF; IF ( ST_GeometryType(p_pt1) <> 'ST_Point' OR ST_GeometryType(p_pt1) <> 'ST_Point' OR ST_GeometryType(p_pt1) <> 'ST_Point' ) THEN RAISE EXCEPTION 'All supplied geometries must be points.'; RETURN NULL; END IF; v_dA := ST_X(p_pt2) - ST_X(p_pt1); v_dB := ST_Y(p_pt2) - ST_Y(p_pt1); v_dC := ST_X(p_pt3) - ST_X(p_pt1); v_dD := ST_Y(p_pt3) - ST_Y(p_pt1); v_dE := v_dA * (ST_X(p_pt1) + ST_X(p_pt2)) + v_dB * (ST_Y(p_pt1) + ST_Y(p_pt2)); v_dF := v_dC * (ST_X(p_pt1) + ST_X(p_pt3)) + v_dD * (ST_Y(p_pt1) + ST_Y(p_pt3)); v_dG := 2.0 * (v_dA * (ST_Y(p_pt3) - ST_Y(p_pt2)) - v_dB * (ST_X(p_pt3) - ST_X(p_pt2))); -- If v_dG is zero then the three points are collinear and no finite-radius -- circle through them exists. IF ( v_dG = 0 ) THEN RETURN NULL; ELSE v_CX := (v_dD * v_dE - v_dB * v_dF) / v_dG; v_CY := (v_dA * v_dF - v_dC * v_dE) / v_dG; v_Radius := SQRT(POWER(ST_X(p_pt1) - v_CX,2) + POWER(ST_Y(p_pt1) - v_CY,2) ); END IF; RETURN ST_SetSRID(ST_MakePoint(v_CX, v_CY, v_radius),ST_Srid(p_pt1)); END; $BODY$ LANGUAGE plpgsql VOLATILE STRICT;
The function can be used as follows:
SELECT ST_X(c.circle) AS CX, ST_Y(c.circle) AS CY, ST_Z(c.circle) AS radius FROM (SELECT Find_Circle(ST_MakePoint(0.0,0.0), ST_MakePoint(10.0,0.0), ST_MakePoint(10.0,10.0)) AS circle ) AS c;
Resulting in ….
cx | cy | radius |
---|---|---|
5 | 5 | 7.07106781186548 |
Or can be used as follows…
SELECT ST_AsEWKT(Find_Circle(ST_SetSrid(ST_MakePoint(525000, 5202000),32615), ST_SetSrid(ST_MakePoint(525500, 5202000),32615), ST_SetSrid(ST_MakePoint(525500, 5202500),32615))) AS circle;
Resulting in ….
circle |
---|
SRID=32615;POINT (525250 5202250 353.553390593274) |
I hope this is of help to someone.
Documentation
- MySQL Spatial General Functions
- Oracle LRS Objects
- Oracle Spatial Exporter (Java + pl/SQL)
- Oracle Spatial Object Functions
- Oracle Spatial Object Functions (Multi Page)
- PostGIS pl/pgSQL Functions
- SC4O Oracle Java Topology Suite (Java + pl/SQL)
- SQL Server Spatial General TSQL Functions
- SQL Server Spatial LRS TSQL Functions