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)
Converting PostgreSQL Native Geometric Types to PostGIS
Introduction
I comes as a surprise to some that PostgreSQL has it own native geometric type system.
The geometric types are documented here but the list includes:

point – Point on a plane – (x,y)
lseg – Finite line segment – ((x1,y1),(x2,y2))
box – Rectangular box – ((x1,y1),(x2,y2))
path – Closed path (similar to polygon) – ((x1,y1),…)
path – Open path – [(x1,y1),…]
polygon – Polygon (similar to closed path) – ((x1,y1),…)
circle – Circle – <(x,y),r> (center point and radius)
line – Infinite line – {A,B,C}
These geometric types are all planimetric types. That is they are not geography or projection CRS aware. They are akin to a PostGIS SRID of 0.
PostGIS is an extension to PostgreSQL: it is not its native (Kernel level) geography/geometry type system. PostGIS provides a state of the art, comprehensive, standards compliant, open and free implementation of the OpenGIS Consortium‘s many spatial standards not just its Simple Features Access Specification.
Conversion
Each of the PostgreSQL geometric types can be converted to PostGIS
Why?
Why would we want to convert to PostGIS? Well, as was shown to me the other day, the world does not revolve around spatial or GIS. A company providing a software package to a customer used the geometric point type to store latitude and longitude data. It is understandable for two reasons:

The company may never have heard of PostGIS
Use of the PostgreSQL point type does not require management of the PostGIS extension.
Conversion of each of the geometric types to PostGIS will be done on a simple, single SQL statement basis. At the end, if I have time, I will create a set of functions to encapsulate the SQL implementation.
Point geometric Type
Creating a point geometric object is easy:
 Constructors select point(43.5,147.5) as pnt; select '(43.5,147.5)'::point as pnt; select '43.5,147.5'::point as pnt;
Note: Regardless as to the constructor method used, the canonical form when displaying an point (when in a select list) is always in the following form (X,Y):
 Constructors select point(43.5,147.5) as pnt; pnt (43.5,147.5)
Accessing Point Ordinates
The point type’s X and Y ordinates (stored as floatingpoint numbers) can be accessed simply via simple array dereferencing:

XY[0] – first ordinate
XY[1] – second ordinate.
with pg as ( select '(43.5,147.5)'::point as geom ) select pg.geom[0] as latitude, pg.geom[1] as longitude from pg;
Conversion to PostGIS
Because of array dereferencing points are the simplest to convert. We use the ordinates of the geometric point in the PostGIS ST_Point constructor as follows:
with pg as ( select '(43.5,147.5)'::point as geom ) select pg_typeof(pg.geom) as gtype, ST_AsEWKT(ST_Point(pg.geom[1],pg.geom[0],4326)) as geom  Since I know that the XY's are lat/longs I can assign a geodetic SRID from pg; gtype geom point SRID=4326;POINT(147.5 43.5)
lseg Geometric Type
PostgreSQL lseg’s are simply two point linestrings.
Creating an lseg geometric object is also easy:
 Constructors select '0,0,1,1'::lseg as lseg; select '(0,0),(1,1)'::lseg as lseg; select '((0,0),(1,1))'::lseg as lseg; select '[(0,0),(1,1)]'::lseg as lseg; select lseg(point(0,0),point(1,1)) as lseg;
Note: Regardless as to the constructor method used, the canonical form when displaying an lseg (when in a select list) is always in the following form [(X,Y),{X,Y)]. So any of the above constructors will display the lseg as follows:
select lseg(point(0,0),point(1,1)) as lseg; lseg [(0,0),(1,1)]
Accessing Point Ordinates
Because an lseg is stored as two point types (each point’s ordinates as floatingpoint numbers), it can also be accessed via simple array dereferencing as follows:
with pg as ( select '(43.1,147.1),(43.2,147.2)'::lseg as geom ) select pg_typeof(pg.geom) as gtype, pg.geom::text as lseg, pg.geom[0])[1] as SX, pg.geom[0])[0] as SY, pg.geom[1])[1] as EX, pg.geom[1])[0] as EY from pg; gtype lseg SX SY EX EY lseg [(43.1,147.1),(43.2,147.2)] 147.1 43.1 147.2 43.2
Conversion to PostGIS
PostGIS only has a multipoint linestring object that can contain 2 or more vertices. So, conversion to PostGIS is to a linestring in its most simplest form, i.e., a 2 vertex linestring.
Conversion can be done in two ways using WKT/EWKT (ST_GeomFrom{E}WKT) or directly using the ST_MakeLine PostGIS function.
with pg as ( select '(43.1,147.1),(43.2,147.2)'::lseg as geom ) select gtype, geometric, ST_AsEWKT(eGeom) as eGeom, ST_AsEWKT(geom) as geom from (select pg_typeof(pg.geom) as gtype, pg.geom::text as geometric, ST_GeomFromEWKT(replace(replace(replace(replace(replace(pg.geom::text,'),(',')('),',',' '),'[','SRID=4326;LINESTRING'),']',''),')(',',')) as eGeom, ST_MakeLine( ARRAY[ ST_Point((pg.geom[0])[1],(pg.geom[0])[0],4326), ST_Point((pg.geom[1])[1],(pg.geom[1])[0],4326) ] ) as geom from pg ) as f; gtype geometric eGeom geom lseg [(43.1,147.1),(43.2,147.2)] SRID=4326;LINESTRING(43.1 147.1,43.2 147.2) SRID=4326;LINESTRING(147.1 43.1,147.2 43.2)
Box Geometric Type
A box geometric object is represented by two point types, the first being the upper right coordinate of the box and the second point the lower left coordinate of the box.
Creating a box geometric object is easy:
 Constructors select '(0,0),(1,1)'::box as box; select '((0,0),(1,1))'::box as box; select '0,0,1,1'::box as box;
Note: Regardless as to the constructor method used, the canonical form when displaying an box (when in a select list) is always in the following form (UX,UY),(LX,LY):
 Constructors select '((0,0),(1,1))'::box as box; box (1,1),(0,0)
Notice how the constructor detected that the coordinates were swapped and reorganised them.
Accessing Box Coordinate
As per the lseg a box is stored as two point types (each point’s ordinates as floatingpoint numbers), so it can also be accessed via simple array dereferencing. Thus if the column name in the database of type box its fundamental XY ordinates can be accessed as follows:
with pg as ( select '(43.1,147.1),(43.2,147.2)'::box as geom ) select pg_typeof(pg.geom) as gtype, pg.geom::text as lseg, (pg.geom[0])[0] as SX, (pg.geom[0])[1] as SY, (pg.geom[1])[0] as EX, (pg.geom[1])[1] as EY from pg; gtype lseg SX SY EX EY box (43.1,147.2),(43.2,147.1) 43.1 147.2 43.2 147.1
Conversion to PostGIS
We can convert a geometric box to either a PostGIS box2d or polygonobject.
Polygon conversion is done in three ways below:

Conversion to PostGIS BOX object
Conversion from box2d to polygon using ST_Envelope
WKT conversion from box geometric object using array dereferencing to create a 5 vertex polygon
— box2d
with pg as (
select ‘(43.1,147.1),(43.2,147.2)’::box as geom
)
select gtype, geometric, ST_GeometryType(geom) as pgType, geom
from (select pg_typeof(pg.geom) as gtype,
pg.geom::text as geometric,
ST_MakeBox2D(
ST_Point((pg.geom[0])[1],(pg.geom[0])[0],4326),
ST_Point((pg.geom[1])[1],(pg.geom[1])[0],4326)) as geom
from pg
) as f;
gtype geometric pgType geom
box (43.1,147.2),(43.2,147.1) ST_Polygon BOX(147.1 43.2,147.2 43.1)
— Notice:
1. PostGIS reports the box as an ST_Polygon
2. The canonical text format on display for a box2d is simply as a string (no need for ST_AsEWKT/ST_AsText).
— Polygon
— The easiest method for creating a polygon is to construct a box and then use the ST_Envelope function to turn it into a polygon
with pg as (
select ‘(43.1,147.1),(43.2,147.2)’::box as geom
)
select gtype, geometric, ST_GeometryType(geom) as pgtype, ST_AsEWKT(geom) as geom
from (select pg_typeof(pg.geom) as gtype,
pg.geom::text as geometric,
ST_Envelope(
ST_MakeBox2D(
ST_Point((pg.geom[0])[1],(pg.geom[0])[0],4326),
ST_Point((pg.geom[1])[1],(pg.geom[1])[0],4326))) as geom
from pg
) as f;
gtype geometric pgType geom
box (43.1,147.2),(43.2,147.1) ST_Polygon POLYGON((147.1 43.2,147.1 43.1,147.2 43.1,147.2 43.2,147.1 43.2))
— Conversion using WKT
with pg as (
— stores the upper right and lower left corners, in that order.
select ‘(0,0),(1,1)’::box as geom
)
select gtype, box, ST_AsEWKT(eGeom) as eGeom
from (select pg_typeof(pg.geom) as gtype,
pg.geom::text as box,
ST_GeomFromEWKT(
‘SRID=4326;POLYGON((‘ 
FORMAT(‘%s %s,’,(pg.geom[1])[1],(pg.geom[1])[0]) 
FORMAT(‘%s %s,’,(pg.geom[0])[1],(pg.geom[1])[0]) 
FORMAT(‘%s %s,’,(pg.geom[0])[1],(pg.geom[0])[0]) 
FORMAT(‘%s %s,’,(pg.geom[1])[1],(pg.geom[0])[0]) 
FORMAT(‘%s %s’, (pg.geom[1])[1],(pg.geom[1])[0])  ‘))’
) as eGeom
from pg
) as f;
gtype box eGeom
box (1,1),(0,0) SRID=4326;POLYGON((0 0,1 0,1 1,0 1,0 0))
[/code]
Accessor Functions
The PostgreSQL geometric objects have accessor functions. So, for a box it is possible to ask for its area.
select area('(43.1,147.1),(43.2,147.2)'::box) as geom
Notice how the area is returning in sq decimal degrees. Remember that PostgreSQL geometric objects are just that: geometric; so the ordinates are interpreted as just coordinates on a plain surface.
Now, if we used the method above to convert a box to a PostGIS geometry polygon we would also get an area calculated in sq decimal degrees. To get the actual area of a box, in sq meters, we can use the PostGIS geography type.
Note: Without getting into a discussion about coordinate ordering of geographic/geodetic data it suffices to say that PostGIS expects the input coordinates to be ordered as (Longitude,Latitude) as internal ordering is always (X,Y). There is no version of the ST_MakePoint function for geographic data that expects (latitude,longitude) ordering.
So, to get the area is simple: just cast the created polygon to a geography type and ask for its area.
with pg as ( select '(43.1,147.1),(43.2,147.2)'::box as geom ) select gtype, geometric, ST_GeometryType(geom) as pgtype, ST_Area(geom) as sqm, ST_AsEWKT(geom) as geom from (select pg_typeof(pg.geom) as gtype, pg.geom::text as geometric, ST_Envelope( ST_MakeBox2D( ST_Point((pg.geom[0])[1],(pg.geom[0])[0],4326), ST_Point((pg.geom[1])[1],(pg.geom[1])[0],4326)))::geography as geom from pg ) as f; gtype geometric pgType area geom box (43.1,147.2),(43.2,147.1) ST_Polygon 90367781.78890991 SRID=4326;POLYGON((147.1 43.2,147.1 43.1,147.2 43.1,147.2 43.2,147.1 43.2))
Path Geometric Object
A path geometric object is represented by two or more point types.
Creating a path geometric object is easy:
 Constructors  Constructors select '0,0,1,0,1,1,0,1'::path as line; select '(0,0),(1,0),(1,1),(0,1)'::path as line; select '((0,0),(1,0),(1,1),(0,1))'::path as line; line ((0,0),(1,0),(1,1),(0,1))
Note: Regardless as to the constructor method used, the canonical form when displaying an path (when in a select list) is always in the following form ((X1,Y1),(X2,Y2),…,(Xn,Yn)):
 Constructors select '((0,0),(1,0),(1,1),(0,1))'::path as line; line ((0,0),(1,0),(1,1),(0,1))
If the first and last point are the same the path is considered to be closed (isClosed function) and if not it is considered to be open (isOpen).
with pg as ( select '0,0,1,0,1,1,0,1'::path as geom ) select isOpen(pg.geom) as isOpen, isClosed(pg.geom) as isClosed from pg; isOpen isClosed false true
Accessing path Coordinates
One might hope that accessing the points that make up a path should be possible by array dereferencing.
However as this possible implementation – which uses the geometric vertex counting method # path – shows, it is not possible to access coordinates via array dereferencing.
with pg as ( select '0,0,1,0,1,1,0,1'::path as geom ) select iPoint,(geom[iPoint1])[0] as X,(geom[iPoint1])[1] as Y from pg, generate_series(1,# pg.geom,1) as iPoint ERROR: cannot subscript type path because it does not support subscripting LINE 4: select iPoint,(geom[iPoint1])[0] as X,(geom[iPoint1])[1] a... ^ SQL state: 42804 Character: 72
Converting to a PostGIS Linestring
Because array subscripting is not available to us, we are forced to converting to a PostGIS linestring done by manipulating the canonical string format of the path object. This turns out to be quite easy (much easier than if array subscripting had been possible).
with pg as ( select '0,0,1,0,1,1,0,1'::path as geom ) select pg_typeof(pg.geom) as gtype, pg.geom::text as geometric, ST_AsEWKT(ST_GeomFromEWKT(replace(replace(replace(replace(replace(pg.geom::text,'),(',')('),',',' '),'((','SRID=0;LINESTRING('),'))',')'),')(',','))) as eGeom from pg; gtype gtype eGeom path ((0,0),(1,0),(1,1),(0,1)) LINESTRING(0 0,1 0,1 1,0 1)
Finally, calculating the length of a path object is easy as PostgreSQL makes available the length function, but as in the following example you will note that the length returned is again in decimal degrees.
with pg as ( select '(147.1,43.2),(147.2,43.2),(147.2,43.1),(147.1,43.1),(147.1,43.2)'::path as geom ) select length(geom) as len from pg; len 0.3999999999999915
So, to get the right length we again use a geography cast.
with pg as ( select '(147.1,43.2),(147.2,43.2),(147.2,43.1),(147.1,43.1),(147.1,43.2)'::path as geom ) select ST_Length(ST_GeomFromEWKT(replace(replace(replace(replace(replace(pg.geom::text,'),(',')('),',',' '),'((','SRID=0;LINESTRING('),'))',')'),')(',','))::geography) as len from pg; len 38487.59155240472
Polygon Geometric Type
A polygon geometric object is represented by two or more point types but the start and end coordinates are the same.
Creating a path geometric object is easy:
 Constructors select '0,0,1,0,1,1,0,1,0,0'::polygon as poly; select '(0,0),(1,0),(1,1),(0,1),(0,0)'::polygon as poly; select '((0,0),(1,0),(1,1),(0,1),(0,0))'::polygon as poly; select '(147.1,43.2),(147.2,43.2),(147.2,43.1),(147.1,43.1),(147.1,43.2)'::polygon as geom;
Note: Regardless as to the constructor method used, the canonical form when displaying an path (when in a select list) is always in the following form ((X1,Y1),(X2,Y2),…,(Xn,Yn)) (same as a path):
 Constructors select '0,0,1,0,1,1,0,1,0,0'::polygon as poly; poly ((0,0),(1,0),(1,1),(0,1))
Note: polygons do not have to be closed i.e., the last point does not have to equal the first point.
Area
The area of polygon cannot be calculated using the PostgreSQL geometric area operator. The polygon has to be cast to a path.
Note: The area of an unclosed polygon is still the same as if it was closed (geometric polygon functions such as area must implicitly close unclosed polygons).
select area('(0,0),(1,0),(1,1),(0,1),(0,0)'::polygon::path) as areaClosed, area('(0,0),(1,0),(1,1),(0,1)'::polygon::path) as areaUnClosed; areaClosed areaUnclosed 1 1
Converting to PostGIS Polygons
Geometric polygons are simply geometric paths but they may, or may not be closed, so our conversion method must handle this because PostGIS requires a polygon’s first and last points to be the same.
Converting polygon geometric objects to PostGIS polygons can use the same method as for paths with a little twist when handling unclosed geometric polygons.
Note that geometric polygons do not have interior rings, only having a single exterior ring.
In the example below we will also compute area in square metres by creating a geography object from a closed geometric polygon.
 Closed and Unclosed geometric polygon with pg as ( select '(147.1,43.2),(147.2,43.2),(147.2,43.1),(147.1,43.1),(147.1,43.2)'::polygon as geom with pg as ( select '(147.1,43.2),(147.2,43.2),(147.2,43.1),(147.1,43.1)'::polygon as geom ) select gtype, geometric , ST_Area(eGeom::geography) as sqM, ST_AsEWKT(eGeom) as eGeom from (select pg_typeof(pg.geom) as gtype, pg.geom::text as geometric, case isClosed(pg.geom::path) when true then ST_GeomFromEWKT(replace(replace(replace(replace(replace(pg.geom::text,'),(',')('),',',' '),'((','SRID=4326;POLYGON(('),')(',','),'))',','replace((firstCoord)[1],',',' ')'))')) when false then ST_GeomFromEWKT(replace(replace(replace(replace(pg.geom::text,'),(',')('),',',' '),'((','SRID=4326;POLYGON(('),')(',',')) end as egeom from pg, lateral REGEXP_MATCHES(pg.geom::text,'[09][\09.,]*') as firstCoord ) as f; gtype geometric sqM eGeom polygon ((147.1,43.2),(147.2,43.2),(147.2,43.1),(147.1,43.1),(147.1,43.2)) 90367781.78890991 SRID=4326;POLYGON((147.1 43.2,147.2 43.2,147.2 43.1,147.1 43.1,147.1 43.2))
Converting unclosed geometric polygons is pretty straight forward: we detect if the polygon is unclosed, extract the first coordinate using regexp_matches, and add it to the end of the WKT string before converting to a PostGIS polygon.
The following sql implements conversion for both closed and unclosed geometric polygons.
 Closed and Unclosed geometric polygon with pg as (  Closed select '(147.1,43.2),(147.2,43.2),(147.2,43.1),(147.1,43.1),(147.1,43.2)'::polygon as geom union all  Unclosed select '(147.1,43.2),(147.2,43.2),(147.2,43.1),(147.1,43.1)'::polygon as geom ) select gtype, poly::text as poly, isClosed(poly::path) as isClosed, ST_Area(eGeom::geography) as sqM, ST_AsEWKT(eGeom) as eGeom from (select pg_typeof(pg.geom) as gtype, pg.geom as poly, case isClosed(pg.geom::path) when true then ST_GeomFromEWKT(replace(replace(replace(replace(replace(pg.geom::text,'),(',')('),',',' '),'((','SRID=4326;POLYGON(('),')(',','),'))',','replace((firstCoord)[1],',',' ')'))')) when false then ST_GeomFromEWKT(replace(replace(replace(replace(pg.geom::text,'),(',')('),',',' '),'((','SRID=4326;POLYGON(('),')(',',')) end as egeom from pg, lateral REGEXP_MATCHES(pg.geom::text,'[09][\09.,]*') as firstCoord ) as f; gtype poly isClosed sqM eGeom polygon ((147.1,43.2),(147.2,43.2),(147.2,43.1),(147.1,43.1),(147.1,43.2)) true 90367781.78890991 SRID=4326;POLYGON((147.1 43.2,147.2 43.2,147.2 43.1,147.1 43.1,147.1 43.2,147.1 43.2)) polygon ((147.1,43.2),(147.2,43.2),(147.2,43.1),(147.1,43.1)) true 90367781.78890991 SRID=4326;POLYGON((147.1 43.2,147.2 43.2,147.2 43.1,147.1 43.1,147.1 43.2))
Circle Geometric Type
Creating a point geometric object is easy:
 Constructors select '100,200,10'::circle as circle; select '(100,200),10'::circle as circle; select '<(100,200),10>'::circle as circle;
Note: Regardless as to the constructor method used, the canonical form when displaying an circle (when in a select list) is always in the following form <(CX,CY),Radius>:
select '100,200,10'::circle as circle; circle <(100,200),10>
Accessing Circle Components
A circle contains a point and a numeric object. One would hope that access could be done via array dereferencing but as the following two attempts show, this is not possible.
 1. Simple Array with pg as ( select '(100,200),10'::circle as circle ) select pg_typeof(pg.circle) as gtype, pg.circle[1]::float as dCentreX, pg.circle[2]::float as dCentreY, pg.circle[3]::float as dRadius, pg.circle from pg; ERROR: cannot subscript type circle because it does not support subscripting LINE 5: pg.circle[1]::float as dCentreX,  2. Point Access and Dereferncing plua simple array access to radius with pg as ( select '(100,200),10'::circle as circle ) select pg_typeof(pg.circle) as gtype, (pg.circle[0])[0]::float as dCentreX, (pg.circle[0])[1]::float as dCentreY, pg.circle[2]::float as dRadius, pg.circle from pg; ERROR: cannot subscript type circle because it does not support subscripting LINE 5: (pg.circle[0])[0]::float as dCentreX,
Conversion to PostGIS
Because there is no array dereferencing we need to process the circle object via some sort of string processing.
Here, the PostgreSQL function, string_to_array with some replacement of the <() string elements, helps.
with pg as ( select '(100,200),10'::circle as circle ) select gtype, circle, CX, CY, Radius, ST_AsEWKT(ST_Point(f.CX,f.CY,0)) as point from (select pg_typeof(pg.circle) as gtype, pg.circle, a.elem[1]::float as CX, a.elem[2]::float as CY, a.elem[3]::float as Radius from pg, lateral (select string_to_array(replace(replace(replace(pg.circle::text,'<(',''),')',''),'>',''),',') as elem) as a ) as f; gtype circle CX CY Radius point circle <(100,200),10> 100 200 10 POINT(100 200)
Now PostGIS does not have a ST_Circle(x,y,r) function so the geometric circle will need to be converted to a closed polygon by “stroking” the circle using a defined number of segments e.g., 8.
We can calculate the points for a closed polygon as follows.
with pg as ( select '(100,200),10'::circle as geom, 8 as iSegments ) ,parameters as ( select pg_typeof(pg.geom) as gtype, pg.iSegments, CAST(2.0 as float) * PI() / pg.iSegments::float as dDeltaTheta, f.elem[1]::float as dCentreX, f.elem[2]::float as dCentreY, f.elem[3]::float as dRadius, pg.geom from pg, lateral (select string_to_array(replace(replace(replace(pg.geom::text,'<(',''),')',''),'>',''),',') as elem) as f ) select ST_AsText(point) as point from (  First point select ST_Point(p.dCentreX + p.dRadius,p.dCentreY,0) as point from parameters as p union all  Calculated points select ST_Point(p.dCentreX + p.dRadius * COS(p.dDeltaTheta * iSeg::float), p.dCentreY + p.dRadius * SIN(p.dDeltaTheta * iSeg::float), 0) as point from parameters as p, generate_series(1,p.iSegments,1) as iSeg ) as f; point POINT(110 200) POINT(107.07106781186548 207.07106781186548) POINT(100 210) POINT(92.92893218813452 207.07106781186548) POINT(90 200) POINT(92.92893218813452 192.92893218813452) POINT(100 190) POINT(107.07106781186548 192.92893218813452) POINT(110 200)
Now we can create a polygon from these points quite simply using string aggregation and the ST_GeomFromEWKT() function as follows.
However, because we compute points on the circumference of the circle, with the last computed point being the same as the starting point, and the calculations are done with imprecise floats (double precision) we need to round the vertices to ensure that the last generated point equals the first.
 Create Polygon with pg as ( select '(100,200),10'::circle as geom,8 as iSegments, 3 as iRound ) ,parameters as ( select pg_typeof(pg.geom) as gtype, pg.iSegments, pg.iRound, CAST(2.0 as float) * PI() / pg.iSegments::float as dDeltaTheta, f.elem[1]::float as dCentreX, f.elem[2]::float as dCentreY, f.elem[3]::float as dRadius, pg.geom from pg, lateral (select string_to_array(replace(replace(replace(pg.geom::text,'<(',''),')',''),'>',''),',') as elem) as f ) select ST_AsEWKT( ST_GeomFromEWKT( 'SRID=0;POLYGON(('  STRING_AGG(ST_X(point)::text  ' '  ST_Y(point)::text,',' order by iSeg)  '))' ) ) as geom from (  First point select 0 as iSeg, ST_Point(p.dCentreX + p.dRadius,p.dCentreY,0) as point from parameters as p union all  Calculated points select iSeg, ST_Point(ROUND(CAST(p.dCentreX + p.dRadius * COS(p.dDeltaTheta * iSeg::float) as numeric),iRound), ROUND(CAST(p.dCentreY + p.dRadius * SIN(p.dDeltaTheta * iSeg::float) as numeric),iRound), 0) as point from parameters as p, generate_series(1,p.iSegments,1) as iSeg ) as f; geom POLYGON((110 200,107.071 207.071,100 210,92.929 207.071,90 200,92.929 192.929,100 190,107.071 192.929,110 200))
Infinite Line
Infinite lines are represented by the linear equation Ax + By + C = 0, where A and B are not both zero.
Creating an infinite line geometric object is easy:
 Constructors select '{1,1,0}'::line as iLine; select '(0,0),(100,100)'::line as iline; select '0,0,100,100'::line as iline;
Note: Regardless as to the constructor method used, the canonical form when displaying an infinite line is always in the following form {A,B,C}:
 Constructors select '0,0,100,100'::line as iline; iLine {1,1,0}
Conversion to PostGIS lines is not implemented in this blog.
Wrapping Conversion in functions
In another post this conversion works will be extended to include a set of wrapper functions that can be used to convert PostgreSQL geometric types to PostGIS.
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