4

I'm using ogr2ogr to pull features out of Oracle and put them into a File GeoDatabase. The FGDB is being used by an active ArcGIS Server map service. This is all done for performance / reducing DB load. The DB data is refreshed nightly and then the ogr2ogr process runs so I don't worry about stale FGDB records.

The data can both grow and shrink overnight during the update so I need my FGDB refresh to both remove and add features.

I know this is possible if I use ogr2ogr's -overwrite option, e.g.

ogr2ogr --config FGDB_BULK_LOAD YES -f "FileGDB" <path to>.gdb OCI:user/pass@SID -overwrite -nlt LINESTRING -a_srs EPSG:4326 -nln layer_name -sql "select * from src_table"

But -overwrite fails if I don't first stop the ArcGIS Server map service.

I can use -update -append to update FGDB records while the service is running but this is 'additive' - records are added but nothing is removed.

ogr2ogr --config FGDB_BULK_LOAD YES -f "FileGDB" <path to>.gdb OCI:user/pass@SID -update -append -nlt POINT -a_srs EPSG:4326 -nln layer_name -sql "select * from src_table"

I'm looking for a way to either

  1. Truncate the FGDB layer without removing it prior to an -update -append or
  2. Change my ogr2ogr command to remove features not pulled from Oracle

I also saw this post this page on programmatically stopping / starting AGS map services but option 1 or 2 is my preference as I want an approach that doesn't require authentication / tokens over HTTP. If necessary I can bring in Python and perhaps the arcpy module.

Any input on GDAL / ogr2ogr parameters I haven't seen yet that might help would be much appreciated (or alternative approaches of course).

Improve this question
6
  • No idea if this will work, but have you tried issuing a TRUNCATE TABLE <table> or DELETE * FROM <table> SQL statement through the FGDB driver? See the "SQL support" note on the FGDB driver page: gdal.org/ogr/drv_filegdb.html
    – blah238
    Nov 28, 2012 at 19:58
  • @blah238 I really like the idea but I don't understand how to send SQL to the FileGDB driver. The link isn't exhaustive. The best I can come up with is ogr2ogr -dialect FileGDB <path to>.gdb -sql "delete from table" but so far no joy
    – tomfumb
    Nov 28, 2012 at 20:11
  • @blah238 Amazing! this worked: ogrinfo -dialect FileGDB -sql "delete from table_name" <path to>.gdb I got errors trying to use truncate table table_name and truncate table_name. Do you want to add your suggestion as an answer?
    – tomfumb
    Nov 28, 2012 at 20:34
  • Great to hear! Done.
    – blah238
    Nov 28, 2012 at 22:20
  • So you can delete the records and reinsert them while the service is running against the file geodatabase?
    – Chad Cooper
    Nov 29, 2012 at 3:13

1 Answer 1

Reset to default
3

It is possible to issue SQL statements to the FileGDB driver, e.g.:

ogrinfo -dialect FileGDB -sql "delete from <table_name>"

See the "SQL support" note on the FGDB driver page: http://gdal.org/ogr/drv_filegdb.html

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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