Home

Current PostGIS Blog Articles

Search

Browse

Email me

All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

## Converting Oracle Optimized Rectangles to PostGIS

Monday April 27 2009 at 11:39

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 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 ','

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

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 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 ','

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

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.

### Comment [1]

Hi Simon,
How are you?
I tried commands posted here and all ran right.
Do you know any way to export and import from Oracle Spatial to PostGis the whole Oracle schema, instead of create a script for each table ?

Regards
Eriovaldo

— Eriovaldo Andrietta · 21 November 2010, 18:33 · #

 Name Remember E-mail Message Textile Help