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'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).

share|improve this question
    
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 '12 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 '12 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 '12 at 20:34
    
Great to hear! Done. –  blah238 Nov 28 '12 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 '12 at 3:13

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer

Your Answer

 
discard

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.