Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?


share|improve this question

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.

share|improve this answer
Thank you for answer, it clarifies things a little. Unfortunately, my goal was to do it using simple SQL myself, and not revert to ogr2ogr if possible. –  nathanvda Mar 21 '13 at 16:23

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.

share|improve this answer
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 '13 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

share|improve this answer
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 '13 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 '13 at 20:11
up vote 2 down vote accepted

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.

share|improve this answer
For completeness: the sdo2geojson as in the link always created 2d data. I created my own fork here: gist.github.com/nathanvda/a61ab4b094c4c3429a39 that always generates 3d data. –  nathanvda Dec 9 '13 at 16:28

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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