OGR to FileGeoDB Error

[aestrada]

I am running Centos version 6 with yesterday's build of GDAL trunk. Below you'll find the errors I am getting from ogr2ogr when trying to create a FGDB.

[root@me filegdb]# vim run
  1 #!/bin/bash
  2
  3 ogr2ogr -overwrite -skipfailures -f "FileGDB" country.gdb \
  4         PG:"host=172.16.2.66 user=me dbname=gossip password=password" \
  5         -sql "SELECT d.id as id, d.service as service, d.date_taken as date_taken,
  6         d.date_inserted as date_inserted,d.url as url,d.translated as translated,d.geom as geom FROM
  7         getdata d, world_boundaries b WHERE (b.cntry_name = 'country') AND d.geom && b.wkb_geometry
  8         AND intersects(d.geom, b.wkb_geometry)" -nln "country" -nlt POINT

I've tried it with and without -nlt with each of the two geom types (GEOMETRY AND POINT).

POINT throws this error: "ERROR 1: FGDB driver does not support OGR type"

GEOMETRY type throws the following:

[root@me filegdb]# ./run
ERROR 1: Error: Failed to open Geodatabase (The system cannot find the path specified.)
ERROR 1: Error: Failed to open Geodatabase (The system cannot find the path specified.)
ERROR 1: Cannot map OGRwkbGeometryType (0) to ESRI type
ERROR 1: Error (-1): Unable to map OGR type to ESRI type
ERROR 1: Terminating translation prematurely after failed
translation from sql statement.
[root@gossip filegdb]# ERROR 1: Error: Failed to open Geodatabase (The system cannot find the path specified.)
ERROR 1: Cannot map OGRwkbGeometryType (0) to ESRI type
ERROR 1: Error (-1): Unable to map OGR type to ESRI type
ERROR 1: Terminating translation prematurely after failed
translation from sql statement.

I believe this only happens when using the -sql flag. I am not around that machine now and can't officially test it as the FGDB driver doesn't want to behave on my Mac. 

Adam

[aestrada]

Original Error was posted here: ​http://pastebin.com/RP3DDtuW

[Even Rouault]

Must be fixed by :

r22912 /trunk/gdal/ogr/ogrsf_frmts/filegdb/FGdbLayer.cpp: FileGDB: allow writing features with a OFTDate column; fix offset of 1900 years on written value (#4186)

Reopen with more elements if issue still persists.

[aestrada]

Even,

This fix works when going from shapefile to fgdb but as soon as I start getting fancy it dies.

 12 POSTGRESQL="host=localhost user=me dbname=yikes password=password"
 13
 1 15 if [ $1 = "new" ]; then
 16 ogr2ogr -overwrite -skipfailures -f "FileGDB" blerg.gdb PG:"$POSTGRESQL" \
 17         -sql "SELECT d.id as id, d.service as service, d.date_taken as date_taken,
 18         d.date_inserted as committed,d.url as url,d.translated as translated,d.geom as geom FROM
 19         getdata d, world_boundaries b WHERE (b.cntry_name = '$2') AND d.geom && b.wkb_geometry
 20         AND intersects(d.geom, b.wkb_geometry)"


ERROR 1: Error: Failed to open Geodatabase (The system cannot find the path specified.)
ERROR 1: Error: Failed to open Geodatabase (The system cannot find the path specified.)
ERROR 1: Error (-1): FGDB layers cannot be created with a wkbUnknown layer geometry type.
ERROR 1: Terminating translation prematurely after failed

Additionally, when I completely exclude a sql statement and just do a straight Posgresql table to FGDB I get this error. There are several thousand of these "bad value" errors.

ERROR 1: Error: Failed to open Geodatabase (The system cannot find the path specified.)
ERROR 1: Error: Failed to open Geodatabase (The system cannot find the path specified.)
Warning 1: Empty Spatial Reference
ERROR 1: Error: Failed at writing Row to Table in CreateFeature. (The row contains a bad value.)

After a bit more investigation it looks like the only values that can go from PostGIS to FGDB are those that are in WKB format and specifically inserted in the DB using ogr2ogr. Does this sound strange to you or is it something I am doing on my end that is horking everything up. QGIS and Geoserver both like these data tables...

Adam

[Even Rouault]

1) I changed the error message into "FGDB layers cannot be created with a wkbUnknown layer geometry type." in the hope it was obvious... Apparently not... Well you need to explictely set the layer geometry type with -nlt XXXX when doing a sql request, because there's no way OGR can guess the geometry type that will be returned (it could be indeed a mix of geometries depending on the request and data).

2) I don't understand what you mean by "After a bit more investigation it looks like the only values that can go from PostGIS to FGDB are those that are in WKB format and specifically inserted in the DB using ogr2ogr"

I'm not sure to understand why the error "Failed at writing Row to Table in CreateFeature. (The row contains a bad value." occurs. It would be good if you could isolate which features cause that problem and provide us a way of replicating. Perhaps you could try pg->shapefile as an intermediate format (or GML, or whatever) and if shapefile (GML, whatever) -->fgdb fails, that would be easier to share to help diagnosing what goes wrong.

[aestrada]

Replying to rouault:

1) I changed the error message into "FGDB layers cannot be created with a wkbUnknown layer geometry type." in the hope it was obvious... Apparently not... Well you need to explictely set the layer geometry type with -nlt XXXX when doing a sql request, because there's no way OGR can guess the geometry type that will be returned (it could be indeed a mix of geometries depending on the request and data).

2) I don't understand what you mean by "After a bit more investigation it looks like the only values that can go from PostGIS to FGDB are those that are in WKB format and specifically inserted in the DB using ogr2ogr"

I'm not sure to understand why the error "Failed at writing Row to Table in CreateFeature. (The row contains a bad value." occurs. It would be good if you could isolate which features cause that problem and provide us a way of replicating. Perhaps you could try pg->shapefile as an intermediate format (or GML, or whatever) and if shapefile (GML, whatever) -->fgdb fails, that would be easier to share to help diagnosing what goes wrong.

Even,

Please see the steps I took to recreate this error. ​http://pastebin.com/zbY5xq6v

Thanks, Adam

[aestrada]

This appears to be fixed with a workaround. I think that some better error handling could be implemented to test whether or not field character limits are being maxed out prior to conversion.

This is from IRC: EvenR:aestrada: replace "alternatenames" by "cast(alternatenames as character(300))" in your select clause

Adam

[Even Rouault]

I've created #4189 to track the issue in a more focused way