SpatialDB Advisor

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(‘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;

-- 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;

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;

-- 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.