2

I export a polygon layer from Oracle to a FileGDB using ogr2ogr. I need to append one row to the data table, with '0' for numeric fields and 'No Elements' for the one string field. In ArcGIS, I run a model that appends a feature class with the one observation to the target feature class (the one exported from Oracle).

How can I perform the same function using ogr2ogr? Would I append the 'no_element' feature class to the target feature class, and if so, what is the ogr2ogr syntax? Both feature classes are in the same File GDB.

Or, is there something I could add to my ogr2ogr syntax when I export my data from Oracle; I've included my ogr2ogr for my Oracle export.

     ogr2ogr -update -overwrite -nlt polygon -nln EO_DSMRE  -a_srs EPSG:3089 -progress -f FileGDB G:/Office/Heritage/DataMngt/dpaml_app/KSNPC_Update/DSMRE.gdb OCI:biotics_dlink/XXXXXXXXXXXX@biotics5:EO_SHAPE -sql "select  EO_SHAPE.SHAPE SHAPE,  EO_SHAPE.EO_ID EO_ID, to_char( EO_SHAPE.EO_ID) EO_ID_STR, CASE WHEN (DSMRE_EO_VW.ELCODE LIKE 'P%' OR DSMRE_EO_VW.ELCODE LIKE 'N%') THEN 'Plant' WHEN (DSMRE_EO_VW.HIGHER_TAX_CODE LIKE 'IMGASF%' OR DSMRE_EO_VW.ELCODE LIKE 'IMBIV%' OR DSMRE_EO_VW.ELCODE LIKE 'AF%') THEN 'Aquatic Animal' WHEN DSMRE_EO_VW.ELCODE LIKE 'AMACC%' THEN 'Bat' WHEN DSMRE_EO_VW.ELCODE LIKE 'C%' THEN 'Community' ELSE 'Terrestrial Animal' END TAX_TYPE  from  EO_SHAPE,  DSMRE_EO_VW where  DSMRE_EO_VW.EO_ID =  EO_SHAPE.EO_ID"

***Update

I have created a table in the Oracle data base with the fields 'SHAPE, EO_ID, TAX_TYPE'. The values for these fields are 'NULL, 0, "No Elements"'. I also created a view of the data that I have been exporting. Then I tried this:

    select no_element_table.* from no_element_table union select shape_table.* from shape_table

That did not work, SQL Developer spits out ORA-22901: cannot compare VARRAY or LOB attributes of an object type.

So at this point, now that I have created the 'No_Elements' table, I just -update -append that to the target table.

    ogr2ogr -update -append ......
  • I think you need -update -append. – klewis Apr 19 '16 at 17:56
  • Thanks for the response. But how do I add the extra 'No Elements' observation into the data table, i.e., how do I insert a row? – Ian Horn Apr 19 '16 at 18:43
  • Do you need a sub-query to select from Oracle, then have an outer query add fields not in the sub-query? – klewis Apr 19 '16 at 19:22
  • No, I do not need to add any fields. I need to add a NULL observation where I assign a 'No Element' value to the TAX_TYPE field. – Ian Horn Apr 19 '16 at 20:05
1

The solution for this problem was to create a table in oracle with the NULL value and 'No_elements' for tax_type field. I then add an extra ogr2ogr line in my batch file that appends the NULL observation into the existing file geodatabase that is exported in the previous line.

0

Using union all will not throw an error.

ogr2ogr ... -sql "select * from table1 union all select * from table2"

This eliminates the need to run a second ogr2ogr statement appending the null values.

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.