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?

4 Answers 4


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.


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.

  • The key-line in your question, which was my original question (maybe that was not clear): "create text dumps in the specified format from the oracle tables". Yes! That's it! How? :)
    – nathanvda
    Mar 21, 2013 at 16:21

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

  • If I hadn't found the sdo2geojson stored procedure, this would have been the way to go. Luckily I did find that :)
    – nathanvda
    Mar 21, 2013 at 16:25
  • Indeed. I didn't know about this and I am sure it will come very handy one day. Thanks for sharing it!
    – mapoholic
    Mar 22, 2013 at 20:11

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) 

Not the answer you're looking for? Browse other questions tagged or ask your own question.