SpatialDB Advisor

Home

PostGIS Blog Articles

Building polygons from overlapping linestrings requiring intersection

(read more..)

Converting Oracle Optimized Rectangles to PostGIS

(read more..)

DMS2DD for PostGIS

(read more..)

Filtering Rings in Polygon (PostGIS)

(read more..)

FOSSLIC - PostGIS presentations

(read more..)

How to apply spatial constraints to PostGIS tables

(read more..)

Implementing Oracle's GetVertices function in PostGIS - ST_DumpPoints

(read more..)

Loading and Processing GPX 1.1 files using PostgreSQL XML

(read more..)

Loading Point Data from a CSV File in PostGIS

(read more..)

Vectorization: Exploding a linestring or polygon into individual vectors in PostGIS

(read more..)

Search

RSS / Atom

Email me

Converting Oracle Optimized Rectangles to PostGIS

· Apr 27, 10:39 pm by Simon Greener

Someone on the PostGIS discussion list asked about a problem converting an Oracle database to PostGIS and he had run into some difficulties with Oracle’s Optimized Rectangles.

Is there a way to store rectangles in postGIS in a similar fashion?

With the “similar fashion” being all about converting to equivalent 5 vertex POLYGONS:

MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(topleftlat,topleftlon,bottomrightlat,bottomrightlon)),null)

I understand this loads a rectangle in 2 points. I’ve written this postGIS code:

GeomFromText(‘POLYGON (” + topleftlat + “ “ + topleftlon + “ “ + bottomrightlat + “ “ + bottomrightlon + “))’, -1)

Putting aside the inversion of the rectangle above, and the incorrect specification of the WKT Polygon, I endeavoured to help him within some SQL scripting.

Here ‘tis.

Create Optimized Rectangle Data

So that you can replicate the conversion, let’s first start by creating a SQL script that will generate 500 Oracle Spatial optimized rectangles.

DROP   TABLE Conversion PURGE;
CREATE TABLE Conversion ( gid integer, geom mdsys.sdo_geometry );
SET FEEDBACK OFF
    INSERT INTO Conversion
      SELECT rownum,
             mdsys.sdo_geometry(2003,4326,NULL,
                   MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), -- optimized rectangle
                   MDSYS.SDO_ORDINATE_ARRAY(
                         ROUND(lon,6),
                         ROUND(lat,6),
                         ROUND(lon+dbms_random.value(0.1,1.0),6),
                         ROUND(lat+dbms_random.value(0.1,1.0),6)
                         ))
       FROM (SELECT dbms_random.value(147,149) as lon,
                    dbms_random.value(-44,-42) as lat
               FROM DUAL)
     CONNECT BY LEVEL <= 500;
commit;
select distinct sdo_geom.validate_geometry(geom,0.005) from conversion;

-- Write CSV header and data
SET ECHO OFF
@write_csv

The last @write_csv call is done to ensure that “SET ECHO OFF” does not reprint the SELECT statements in write_csv.sql into the spooled CSV file.

Export from Oracle as 4 numeric columns

Now, to create a CSV file we need a second file called “write_csv.sql” into which we will write the optimized rectangle’s 2 coordinates.

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 9999
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET TRIMSPOOL ON
SET LONG 4000
SET LONGCHUNKSIZE 500
SET TERMOUT OFF
SET SQLPROMPT OFF
spool c:\temp\conversion.csv
select 'gid,bottomleftlon,bottomleftlat,toprightlon,toprightlat' from dual;
select gid || ',' || bottomleftlon || ',' || bottomleftlat || ',' || toprightlon || ',' ||  toprightlat
  from (select a.gid, 
               sum(case when MOD(rownum,2) = 1 then v.x else null end) as bottomleftlon, 
               sum(case when MOD(rownum,2) = 1 then v.y else null end) as bottomleftlat, 
               sum(case when MOD(rownum,2) = 0 then v.x else null end) as toprightlon, 
               sum(case when MOD(rownum,2) = 0 then v.y else null end) as toprightlat
          from conversion a, 
               table(sdo_util.getvertices(a.geom)) v
        group by a.gid
        order by 1
       );
spool off
SET SQLPROMPT 'SQL> '

The conversion.csv file looks like this:

gid,bottomleftlon,bottomleftlat,toprightlon,toprightlat
1,148.629941,-42.548192,149.541498,-42.20901
2,148.629941,-42.548192,148.855305,-42.255013
3,148.629941,-42.548192,148.990817,-41.829754
4,148.629941,-42.548192,149.515169,-42.380816
…

Load into PostGIS as BBOX

The CSV file holds all the data for creating a PostGIS BBOX. The following script shows how this would be done.

DROP   TABLE Conversion;

Query returned successfully with no result in 16 ms.

CREATE TABLE Conversion (
  GID           integer,
  bottomLeftLon double precision,
  bottomLeftLat double precision,
  topRightLon   double precision,
  topRightLat   double precision);

Query returned successfully with no result in 15 ms.

COPY Conversion ( gid,bottomLeftLon,bottomLeftLat,topRightLon, topRightLat )
    FROM 'c:/temp/conversion.csv'
    WITH 
          DELIMITER AS ','
          CSV HEADER ;

Query returned successfully: 500 rows affected, 16 ms execution time.

SELECT addGeometryColumn('postgis','conversion','bbox','4326','POLYGON','2'); 

addgeometry
text
------------------------------------------------------
"public.conversion.bbox SRID:4326 TYPE:POLYGON DIMS:2"

UPDATE Conversion set bbox = ST_SetSRID(ST_MakeBox2D(ST_MakePoint(bottomLeftLon,bottomLeftLat), 
                                        ST_MakeBox2D(ST_MakePoint(topRightLon, topRightLat)),
                                        4326);

Query returned successfully: 500 rows affected, 32 ms execution time.

SELECT gid, ST_AsText(bbox)
  FROM Conversion
  LIMIT 10;

gid integer	st_astext text
1	“POLYGON ((148.789993286133 -43.4000015258789,148.789993286133 149.660003662109,149.660003662109 149.660003662109,149.660003662109 -43.4000015258789,148.789993286133 -43.4000015258789))”
2	“POLYGON ((148.789993286133 -43.4000015258789,148.789993286133 149.600006103516,149.600006103516 149.600006103516,149.600006103516 -43.4000015258789,148.789993286133 -43.4000015258789))”
3	“POLYGON ((148.789993286133 -43.4000015258789,148.789993286133 149.240005493164,149.240005493164 149.240005493164,149.240005493164 -43.4000015258789,148.789993286133 -43.4000015258789))”
4	“POLYGON ((148.789993286133 -43.4000015258789,148.789993286133 149.380004882813,149.380004882813 149.380004882813,149.380004882813 -43.4000015258789,148.789993286133 -43.4000015258789))”
5	“POLYGON ((148.789993286133 -43.4000015258789,148.789993286133 148.990005493164,148.990005493164 148.990005493164,148.990005493164 -43.4000015258789,148.789993286133 -43.4000015258789))”
6	“POLYGON ((148.789993286133 -43.4000015258789,148.789993286133 149.419998168945,149.419998168945 149.419998168945,149.419998168945 -43.4000015258789,148.789993286133 -43.4000015258789))”
7	“POLYGON ((148.789993286133 -43.4000015258789,148.789993286133 149.470001220703,149.470001220703 149.470001220703,149.470001220703 -43.4000015258789,148.789993286133 -43.4000015258789))”
8	“POLYGON ((148.789993286133 -43.4000015258789,148.789993286133 149.009994506836,149.009994506836 149.009994506836,149.009994506836 -43.4000015258789,148.789993286133 -43.4000015258789))”
9	“POLYGON ((148.789993286133 -43.4000015258789,148.789993286133 148.919998168945,148.919998168945 148.919998168945,148.919998168945 -43.4000015258789,148.789993286133 -43.4000015258789))”
10	“POLYGON ((148.789993286133 -43.4000015258789,148.789993286133 149.169998168945,149.169998168945 149.169998168945,149.169998168945 -43.4000015258789,148.789993286133 -43.4000015258789))”

-- Create a spatial index for faster querying
CREATE INDEX conversion_bbox ON conversion USING GIST ( bbox );

Query returned successfully with no result in 0 ms.

-- Now, use the newly indexed spatial column in the spatial equivalent of the above query
SELECT count(*)
  FROM conversion
 WHERE bbox && SetSRID('BOX3D(148.7 -42.1, 148.8 -42.3)'::box3d,4326) ;

count
bigint
------
438

OK, so that is all based on CSV holding the 4 ordinate values of the optimized rectangle. You could, of course, export the data to a shapfile, or we could alter the above process to export the data as an OGC WKT Geometry.

Export from Oracle as OGC WKT

At 10g, Oracle Spatial has a .GET_WKT() method on the SDO_GEOMETRY class. We can use this to export the WKT description of an optimized rectangle. (You will notice in the output below that Oracle automatically converts this to a 5 point POLYGON object.)

All we need to do is change the SQL in the write_csv.sql script above.

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 9999
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF
SET TRIMSPOOL ON
SET LONG 4000
SET LONGCHUNKSIZE 500
SET TERMOUT OFF
SET SQLPROMPT OFF
spool c:\temp\conversion.csv
select 'gid,geom_wkt' from dual;
select a.gid || ',"' || CAST(a.geom.get_wkt() as VARCHAR2(1000)) || '"'
  from conversion a
order by a.gid;
spool off
SET SQLPROMPT 'SQL> '

The conversion.csv file looks like this:

gid,geom_wkt
1,“POLYGON ((148.738888 -42.967608, 149.122624 -42.967608, 149.122624 -42.505418, 148.738888 -42.505418, 148.738888 -42.967608))”
2,“POLYGON ((148.738888 -42.967608, 149.143449 -42.967608, 149.143449 -42.078775, 148.738888 -42.078775, 148.738888 -42.967608))”
3,“POLYGON ((148.738888 -42.967608, 149.406925 -42.967608, 149.406925 -42.584677, 148.738888 -42.584677, 148.738888 -42.967608))”
4,“POLYGON ((148.738888 -42.967608, 149.727345 -42.967608, 149.727345 -42.703013, 148.738888 -42.703013, 148.738888 -42.967608))”
…

Load into PostGIS as POLYGON

Here is our, revamped, load script for loading the OGC WKT into PostGIS.

DROP   TABLE Conversion;

Query returned successfully with no result in 16 ms.

CREATE TABLE Conversion (
  GID       integer,
  GEOM_WKT  text);

Query returned successfully with no result in 94 ms.

COPY Conversion ( gid,geom_wkt)
    FROM 'c:/temp/conversion.csv'
    WITH 
          DELIMITER AS ','
          CSV HEADER ;

Query returned successfully: 500 rows affected, 16 ms execution time.

SELECT gid, ST_PolygonFromText(geom_wkt), geom_wkt
  FROM Conversion
  LIMIT 10;

gid	geom_wkt
1	“01030000000100000005000000056D72F8A49762408A….”;“POLYGON ((148.738888 -42.967608, 149.122624 -42.967608, 149.122624 -42.505418, 148.738888 -42.505418, 148.738888 -42.967608))”
2	“01030000000100000005000000056D72F8A49762408A….”;“POLYGON ((148.738888 -42.967608, 149.143449 -42.967608, 149.143449 -42.078775, 148.738888 -42.078775, 148.738888 -42.967608))”
3	“01030000000100000005000000056D72F8A49762408A….”;“POLYGON ((148.738888 -42.967608, 149.406925 -42.967608, 149.406925 -42.584677, 148.738888 -42.584677, 148.738888 -42.967608))”
4	“01030000000100000005000000056D72F8A49762408A….”;“POLYGON ((148.738888 -42.967608, 149.727345 -42.967608, 149.727345 -42.703013, 148.738888 -42.703013, 148.738888 -42.967608))”
5	“01030000000100000005000000056D72F8A49762408A….”;“POLYGON ((148.738888 -42.967608, 149.245611 -42.967608, 149.245611 -41.96837, 148.738888 -41.96837, 148.738888 -42.967608))”
6	“01030000000100000005000000056D72F8A49762408A….”;“POLYGON ((148.738888 -42.967608, 149.472138 -42.967608, 149.472138 -42.211894, 148.738888 -42.211894, 148.738888 -42.967608))”
7	“01030000000100000005000000056D72F8A49762408A….”;“POLYGON ((148.738888 -42.967608, 149.443473 -42.967608, 149.443473 -42.32729, 148.738888 -42.32729, 148.738888 -42.967608))”
8	“01030000000100000005000000056D72F8A49762408A….”;“POLYGON ((148.738888 -42.967608, 149.277115 -42.967608, 149.277115 -42.075974, 148.738888 -42.075974, 148.738888 -42.967608))”
9	“01030000000100000005000000056D72F8A49762408A….”;“POLYGON ((148.738888 -42.967608, 149.21169 -42.967608, 149.21169 -42.264614, 148.738888 -42.264614, 148.738888 -42.967608))”
10	“01030000000100000005000000056D72F8A49762408….”;“POLYGON ((148.738888 -42.967608, 149.300831 -42.967608, 149.300831 -42.01735, 148.738888 -42.01735, 148.738888 -42.967608))”

SELECT addGeometryColumn('postgis','conversion','geom','4326','POLYGON','2'); 

addgeometry
text
------------------------------------------------------
"public.conversion.geom SRID:4326 TYPE:POLYGON DIMS:2"

UPDATE Conversion set geom = ST_SetSRID(ST_PolygonFromText(geom_wkt),4326);

Query returned successfully: 500 rows affected, 78 ms execution time.

SELECT gid, ST_AsText(geom)
  FROM Conversion
  LIMIT 10;

gid	ST_AsText text
1	“POLYGON ((148.738888 -42.967608,149.122624 -42.967608,149.122624 -42.505418,148.738888 -42.505418,148.738888 -42.967608))”
2	“POLYGON ((148.738888 -42.967608,149.143449 -42.967608,149.143449 -42.078775,148.738888 -42.078775,148.738888 -42.967608))”
3	“POLYGON ((148.738888 -42.967608,149.406925 -42.967608,149.406925 -42.584677,148.738888 -42.584677,148.738888 -42.967608))”
4	“POLYGON ((148.738888 -42.967608,149.727345 -42.967608,149.727345 -42.703013,148.738888 -42.703013,148.738888 -42.967608))”
5	“POLYGON ((148.738888 -42.967608,149.245611 -42.967608,149.245611 -41.96837,148.738888 -41.96837,148.738888 -42.967608))”
6	“POLYGON ((148.738888 -42.967608,149.472138 -42.967608,149.472138 -42.211894,148.738888 -42.211894,148.738888 -42.967608))”
7	“POLYGON ((148.738888 -42.967608,149.443473 -42.967608,149.443473 -42.32729,148.738888 -42.32729,148.738888 -42.967608))”
8	“POLYGON ((148.738888 -42.967608,149.277115 -42.967608,149.277115 -42.075974,148.738888 -42.075974,148.738888 -42.967608))”
9	“POLYGON ((148.738888 -42.967608,149.21169 -42.967608,149.21169 -42.264614,148.738888 -42.264614,148.738888 -42.967608))”
10	“POLYGON ((148.738888 -42.967608,149.300831 -42.967608,149.300831 -42.01735,148.738888 -42.01735,148.738888 -42.967608))”

-- Create a spatial index for faster querying
CREATE INDEX conversion_geom ON conversion USING GIST ( geom );

Query returned successfully with no result in 62 ms.

-- Now, use the newly indexed spatial column in the spatial equivalent of the above query
SELECT count(*)
  FROM conversion
 WHERE geom && SetSRID('BOX3D(148.7 -42.1, 148.8 -42.3)'::box3d,4326) ;

count
bigint
------
183

I hope this is of use to someone.

Name		Remember
E-mail
http://
Message
	Textile Help

<<Building polygons from overlapping linestrings requiring intersection >>Loading and Processing GPX 1.1 files using PostgreSQL XML