converting oracle geometry to postgis with 3d data (wkt?)

Question

I am converting Oracle spatial database to postgis. Most of of the spatial data is 2.5d. Meaning we have a Z-coordinate which indicates the depth/height of the element compared to soil level.

Getting the 2D elements is pretty easy: I select the WKT presentation of the element and insert that back into postgis. But, in Oracle the sdo_util.to_wktgeometry only works on 2D geometries. I can convert geometries to 2D using sdo_cs.make_2d but only for those elements that have no height at all.

What is the best way to convert the data?

• is WKB exchangeable between databases? (I guess not)
• are there other formats to extract data from oracle in a format postgis understands?

Thanks.

Answer 1

The OGC WKT and WKB specifications never explicitly addressed how to encode higher dimensionality, so it would not be surprising if Oracle either (a) did not encode those dimensions at all or (b) did so in a way that is not consistent with formats PostGIS can ingest. Absent any examples of Oracle output, I'm just guessing.

You might find that a software-level approach, using ogr2ogr to convert from Oracle to PostGIS works (?), since ogr2ogr will likely read from Oracle using native formats and drivers and write to PostGIS similarly. OGR only supports up to 3 dimensions, so if you have 4D data you'll be out of luck.

Answer 2

PostGIS supports several input formats. Check this reference for details (the ST_GeomFrom* set of functions). If all else fails, create text dumps in the specified format from the oracle tables.

As for WKB, the docs claim they implement two open standards. The question therefore becomes whether the oracle one does too.

Answer 3

Maybe the only way to do this is the hard-way i.e. create an oracle procedure which will read the coordinates for each shape and create sql statements ready to use/run in postgres. For example to get the vertices from an oracle geometry you can run (from here) :

SELECT c.mkt_id, c.name, t.X, t.Y, t.id FROM cola_markets c,
TABLE(SDO_UTIL.GETVERTICES(c.shape)) t ORDER BY c.mkt_id, t.id;

You can then, within your procedure, 'flatten' these coordinates to a string and create 'postgis-ready' insert statements using ST_GeomFromEWKT

Answer 4

POSTGIS supports a lot more formats than Oracle, so I was especially looking for ways to get the data out of Oracle in a format that POSTGIS understands (and that supports 3D data).

Of the formats POSTGIS supports, GeoJSON looked extremely promising to me, and it supports 3D, so I went looking if there was some way to get GeoJSON out of Oracle. And thanks to google, I found http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/206/sdo2geojson

After that it was a piece of cake :) I wrote a small ruby script, that queries oracle:

 select sdo2geojson(my_geometry) from my_oracle_table

and then use a similarly easy query to insert the data:

 insert into my_postgis_table(geometry) VALUES ST_SetSRID(ST_GeomFromGeoJSON('#{geo_json}'),31300) 

I hope this can be of use to somebody else.