Converting PostgreSQL Native Geometric Types to PostGIS

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 floating-point 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 floating-point 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 multi-point 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 re-organised them.

Accessing Box Coordinate

As per the lseg a box is stored as two point types (each point’s ordinates as floating-point 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 de-referencing 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[iPoint-1])[0] as X,(geom[iPoint-1])[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[iPoint-1])[0] as X,(geom[iPoint-1])[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,'[0-9][\-0-9.,]*') 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,'[0-9][\-0-9.,]*') 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.