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

Community
  • 1
tomfumb
  • 3,678
  • 1
  • 31
  • 59
  • No idea if this will work, but have you tried issuing a TRUNCATE TABLE or DELETE * FROM
    SQL statement through the FGDB driver? See the "SQL support" note on the FGDB driver page: http://www.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 03:13
  • @ChadCooper that's what I was doing today and didn't see any issues. I checked the AGS log to be sure but nothing in there. I had my browser application periodically requesting maps from the service too. I think the only restriction is that you can't change the schema (including dropping and recreating the layer) while the service is running – tomfumb Nov 29 '12 at 06:47

1 Answers1

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

blah238
  • 35,793
  • 7
  • 94
  • 195