[GIS] Exporting PostgreSQL table to FileGDB: wkbUnknown layer geometry type

esri-geodatabaseogrpostgis

I'm attempting to export a PostGIS (1.5.3) database table as a File geodatabase using the new FileGDB OGR / GDAL 1.9.0 driver:

$ ogr2ogr -overwrite -sql "SELECT * from db_pts LIMIT 1" \
    -f "FileGDB" /tmp/test.gdb \
    PG:"host='HOST' dbname='DB'"

I get this error:

ERROR 1: Error (-1): FGDB layers cannot be created with a wkbUnknown layer geometry type.
ERROR 1: Terminating translation prematurely after failed translation from sql statement.

The geometry column in the table I'm exported is set up properly and is in the geometry_columns table. I think the problem has to do with the way the -sql option is returning the layer geometry type. For example, accessing the same database table with ogrinfo:

$ ogrinfo -ro -sql "SELECT * from db_allobs LIMIT 1" PG:"host=HOST dbname='DB'"
Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 1
...
OGRFeature(sql_statement):0
...
  POINT (-151.472835 69.635245)

It's as though the query doesn't look in geometry_columns when building the layer, but later, when exporting the rows it knows that the geometry being exported is a POINT (in this case).

When I use a similar command to produce a shapefile, all is well, including the layer geometry, which is set to point.

Anyone have any ideas?

Best Answer

Sorry for the bad form, but the answer is to add the -nlt option, which allows you to set the layer type when -sql is used. I found this at http://trac.osgeo.org/gdal/ticket/4186.

In other words:

ogr2ogr -overwrite -sql "SELECT * from db_pts LIMIT 1" \
        -f "FileGDB" /tmp/test.gdb \
        PG:"host='HOST' dbname='DB'" -nlt POINT
Related Question