Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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.
Documentation
- MySQL Spatial General Functions
- Oracle LRS Objects
- Oracle Spatial Exporter (Java + pl/SQL)
- Oracle Spatial Object Functions
- Oracle Spatial Object Functions (Multi Page)
- PostGIS pl/pgSQL Functions
- SC4O Oracle Java Topology Suite (Java + pl/SQL)
- SQL Server Spatial General TSQL Functions
- SQL Server Spatial LRS TSQL Functions