Converting Oracle Optimized Rectangles to PostGIS

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(‘<span class="caps">POLYGON</span> (” + 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.