Shapefile is being imported to Oracle DB. Is there a tool to export this spatial data as a shapefile without writing any SQL (like you have to do with SQL Developer and GeoRaptor)?

EDIT: from all your answers I understand that there is no way for me to do action: import shapefile X to my database and later use some tool to export same shapefile X without using SQL or SQL like filtering... Unless I want to export whole layer which is not my interest.


GDAL - specifically ogr2ogr is the proper way to go. The base syntax for exporting one table is like this:

$ ogr2ogr -f "ESRI Shapefile" us_cities.shp OCI:scott/tiger@localhost:1521/orcl122:us_cities

where localhost:1521/orcl122 is your database connection string. Note that you do not actually need to specify the output file format (as OGR assumes you want a shapefile by default):

$ ogr2ogr us_cities.shp OCI:scott/tiger@localhost:1521/orcl122:us_cities

There are many things you can do in addition.

Select the rows to export:

ogr2ogr us_cities.shp OCI:scott/tiger@orcl122:us_cities -where "pop90 > 500000" -progress -overwrite

Select the columns to export. Notice you can rename them.

ogr2ogr us_cities.shp OCI:scott/tiger@orcl122:us_cities -select "id, city as city_name, state_abrv" -where "pop90 > 500000" -progress -overwrite

Even use some pseudo-SQL syntax:

ogr2ogr us_cities.shp OCI:scott/tiger@orcl122:us_cities -sql "select id, location from us_cities where pop90 > 500000" -progress -overwrite

Finally, you can also export multiple tables:

ogr2ogr us_data OCI:scott/tiger@localhost:1521/orcl122:us_cities,us_counties,us_states

In this case, us_data is the name of a directory that will be created if it does not exist yet, and each table becomes a shapefile in that directory:

$ ls -l us_data/
total 6772
-rw-rw-r--. 1 spatial spatial   48748 Sep 21 10:16 us_cities.dbf
-rw-rw-r--. 1 spatial spatial     165 Sep 21 10:16 us_cities.prj
-rw-rw-r--. 1 spatial spatial    5560 Sep 21 10:16 us_cities.shp
-rw-rw-r--. 1 spatial spatial    1660 Sep 21 10:16 us_cities.shx
-rw-rw-r--. 1 spatial spatial 1218031 Sep 21 10:16 us_counties.dbf
-rw-rw-r--. 1 spatial spatial     165 Sep 21 10:16 us_counties.prj
-rw-rw-r--. 1 spatial spatial 4605788 Sep 21 10:16 us_counties.shp
-rw-rw-r--. 1 spatial spatial   25940 Sep 21 10:16 us_counties.shx
-rw-rw-r--. 1 spatial spatial  156889 Sep 21 10:16 us_states.dbf
-rw-rw-r--. 1 spatial spatial     165 Sep 21 10:16 us_states.prj
-rw-rw-r--. 1 spatial spatial  837388 Sep 21 10:16 us_states.shp
-rw-rw-r--. 1 spatial spatial     548 Sep 21 10:16 us_states.shx

And not specifying any table name will export all (spatial) tables from the schema you connect as:

ogr2ogr us_data OCI:scott/tiger@localhost:1521/orcl122


Forgot to mention that you can use the same selection techniques (columns, rows, pseudo-sql) also when you import from shapefile (or from any other format).

| improve this answer | |

If you use geoserver, this is quite user friendly to extract shapefile from your Oracle database or any other spatial data source. You need to connect geoserver with oracle database (connection guidelines) then you can export data by using layer preview -> shapefiles.

In case you need to filter your data or choose selected columns, you can create SQL views or apply filters in WFS request.

| improve this answer | |
  • We ended up using geoserver-> layer preview -> shapefiles even though it exports whole layer. Thank you for pointing out this method. – user435421 Oct 11 '17 at 7:18
  • Filters are available, see docs.geoserver.org/latest/en/user/filter. If you need to choose few columns you can create SQL view. – Ashutosh Agrawal Oct 12 '17 at 8:49

It is unclear from your question if you are importing or exporting the data. In either case ogr2ogr will solve the problem for you. Providing that what you want is all the data then there should be no need for any SQL.

The BostonGIS cheat sheet gives some examples with PostGIS but Oracle should be much the same syntax.

| improve this answer | |
  • Thanks for the response. I am importing shape files but I am interested in posibility to export them later. – user435421 Sep 20 '17 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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