Functions to Convert Native PostgreSQL geometric types to PostGIS

A previous article outlined how to convert the native PostgreSQL geometric types to PostGIS.

This article publishes a set of functions that wrap the methods developed in the previous post.

PostGIS Functions

1. Point

CREATE FUNCTION spdba.ST_Point(pGeom point, pSrid integer)
    RETURNS geometry
    LANGUAGE 'sql'
    IMMUTABLE PARALLEL UNSAFE
AS $BODY$
SELECT ST_Point(pGeom[1],pGeom[0],pSrid)
$BODY$

select ST_AsEWKT(spdba.ST_Point('(147.5,-43.1)'::point,4326)) as geom;

geom
SRID=4326;POINT(-43.1 147.5)

2. lseg

CREATE FUNCTION spdba.ST_Line(pGeom lseg, pSrid integer)
    RETURNS geometry
    LANGUAGE 'sql'
    IMMUTABLE PARALLEL UNSAFE
AS $BODY$
select ST_MakeLine(
          ARRAY[ ST_Point((pGeom[0])[1],(pGeom[0])[0],pSrid), 
                 ST_Point((pGeom[1])[1],(pGeom[1])[0],pSrid) ] )
$BODY$

select ST_AsEWKT(spdba.ST_Line('(-43.1,147.1),(-43.2,147.2)'::lseg,4326)) as geom;

geom
SRID=4326;LINESTRING(147.1 -43.1,147.2 -43.2)

3. Box

CREATE FUNCTION spdba.ST_Polygon(pGeom box, pSrid integer)
    RETURNS geometry
    LANGUAGE 'sql'
    IMMUTABLE PARALLEL UNSAFE
AS $BODY$
SELECT ST_MakeBox2D(
          ST_Point((pGeom[0])[1],(pGeom[0])[0],pSrid),
	      ST_Point((pGeom[1])[1],(pGeom[1])[0],pSrid)) as geom
$BODY$

select ST_AsEWKT(spdba.ST_Polygon('(-43.1,147.1),(-43.2,147.2)'::box,4226)) as geom;

geom
POLYGON((147.1 -43.2,147.1 -43.1,147.2 -43.1,147.2 -43.2,147.1 -43.2))

4. Path

CREATE FUNCTION spdba.ST_Line(pGeom path, pSrid integer)
    RETURNS geometry
    LANGUAGE 'sql'
    IMMUTABLE PARALLEL UNSAFE
AS $BODY$
SELECT ST_GeomFromEWKT(replace(replace(replace(replace(replace(pGeom::text,'),(',')('),',',' '),'((','SRID=' || pSRID || ';LINESTRING('),'))',')'),')(',',')) as geom
$BODY$

select ST_AsEWKT(spdba.ST_Line('(147.1,-43.2),(147.2,-43.2),(147.2,-43.1),(147.1,-43.1),(147.1,-43.2)'::path,4326)) as geom;

geom
SRID=4326;LINESTRING(147.1 -43.2,147.2 -43.2,147.2 -43.1,147.1 -43.1,147.1 -43.2)

5. Polygon

CREATE FUNCTION spdba.ST_Polygon(pGeom polygon, pSrid integer)
    RETURNS geometry
    LANGUAGE 'sql'
    IMMUTABLE PARALLEL UNSAFE
AS $BODY$
SELECT case isClosed(pGeom::path)
       when true  
       then ST_GeomFromEWKT(replace(replace(replace(replace(replace(pGeom::text,'),(',')('),',',' '),'((','SRID=' || pSrid || ';POLYGON(('),')(',','),'))',','||replace((firstCoord)[1],',',' ')||'))'))
       when false 
       then ST_GeomFromEWKT(replace(replace(replace(replace(pGeom::text,'),(',')('),',',' '),'((','SRID=' || pSrid || ';POLYGON(('),')(',','))
       end as egeom
  FROM REGEXP_MATCHES(pGeom::text,'[0-9][\-0-9.,]*') as firstCoord
$BODY$

select 'Closed' as isClosed, 
       ST_AsEWKT(spdba.ST_Polygon('(147.1,-43.2),(147.2,-43.2),(147.2,-43.1),(147.1,-43.1),(147.1,-43.2)'::polygon,4326)) as geom
union all
select 'unClosed' as isClosed,
       ST_AsEWKT(spdba.ST_Polygon('(147.1,-43.2),(147.2,-43.2),(147.2,-43.1),(147.1,-43.1)'::polygon,4326)) as geom

isClosed	geom
Closed		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))
unClosed	SRID=4326;POLYGON((147.1 -43.2,147.2 -43.2,147.2 -43.1,147.1 -43.1,147.1 -43.2))

6. Circle

CREATE OR REPLACE FUNCTION spdba.ST_Polygon(pGeom circle, iSegments integer, pSrid integer, iRound integer)
    RETURNS geometry
    LANGUAGE 'sql'
    IMMUTABLE PARALLEL UNSAFE
AS $BODY$
with parameters as (	     
select CAST(2.0 as float) * PI() / iSegments::float as dDeltaTheta,
       f.elem[1]::float as dCentreX,
       f.elem[2]::float as dCentreY, 
       f.elem[3]::float as dRadius
  from (select string_to_array(replace(replace(replace(pGeom::text,'<(',''),')',''),'>',''),',') as elem) as f
)
select ST_AsText(
          ST_GeomFromEWKT(
             'SRID=' || pSrid || ';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,pSrid) 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),
                        pSrid) as point
          from parameters as p,
               generate_series(1,iSegments,1) as iSeg
        ) as f
$BODY$

select ST_AsEWKT(spdba.ST_Polygon('<(100,200),10>'::circle, 8, 0, 3)) as geom
union all
select ST_AsEWKT(spdba.ST_Polygon('<(100,100),100>'::circle, 8, 0, 3)) as geom;

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))
POLYGON((200 100,170.711 170.711,100 200,29.289 170.711,0 100,29.289 29.289,100 0,170.711 29.289,200 100))

I hope this is of use to someone out there.

These functions are shipped via the package of PostGIS functions available via the SPDBA shop.