[GIS] ogr2ogr export postgreSQL to ESRI file GDB wkbUnknown layer geometry type

file-geodatabaseogr2ogrpostgresql

I have 18 postgreSQL tables that I am trying to export to a ESRI file GDB using ogr2ogr. I have the FGD plugin installed.

all the tables in postgreSQL are good, I can bring them into QGIS or Arcgis and export them and there are no errors whatsoever. However when I try and use the below command ogr2ogr gives me an error. the same error in this post Exporting PostgreSQL table to FileGDB: wkbUnknown layer geometry type

Here is my command: 

ogr2ogr --config FGDB_BULK_LOAD YES -progress -append -f "FileGDB" 
C:\Users\rizagha\Desktop\data.gdb  PG:"host=localhost user=postgres 
password=xxx dbname=free" -t_srs EPSG:4269 -overwrite

error(-1) FGDB layers cannot be created with a wkbunkown layer geometry type.
error 1: terminating translation prematurely after failed
translation of layer oak_buffer_union (use -skipfailures to skip errors)

The SRID is in http://spatialreference.org/ref/esri/102689/
the esri SRID of 102689 and I had to manipulate the SRID insert into postgis query to this

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 9102689, 'esri', 102689, '+proj=lcc +lat_1=44.18333333333333 +lat_2=45.7 +lat_0=43.31666666666667 +lon_0=-84.36666666666666 +x_0=6000000.000000001 +y_0=0 +ellps=GRS80 +datum=NAD83 +to_meter=0.3048006096012192 +no_defs ', 'PROJCS["NAD_1983_StatePlane_Michigan_Central_FIPS_2112_Feet",GEOGCS["GCS_North_American_1983",DATUM["North_American_Datum_1983",SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["False_Easting",19685000],PARAMETER["False_Northing",0],PARAMETER["Central_Meridian",-84.36666666666666],PARAMETER["Standard_Parallel_1",44.18333333333333],PARAMETER["Standard_Parallel_2",45.7],PARAMETER["Latitude_Of_Origin",43.31666666666667],UNIT["Foot_US",0.30480060960121924],AUTHORITY["EPSG","102689"]]');

I replaced the 9102689 with 102689 (first input in the values parentheses) because it was throwing an error using the one from spatialreference.org

so could it be ogr2ogr is not able to read the SRID?

also note the table ogr2ogr is throwing the error on is the second table in the DB, which sort of debunks the argument that the SRID is the problem….

I ran ogrinfo PG:"host=localhost user=postgres password=xxx dbname=free" oak_park_buffers -so

INFO: Open of `PG:host=localhost user=postgres password=xxx dbname=free'
      using driver `PostgreSQL' successful.

Layer name: oak_park_buffers
Geometry: Unknown (any)
Feature Count: 341
Extent: (19871669.064638, -322397.313040) - (20029921.991180, -164853.625252)
Layer SRS WKT:
PROJCS["NAD_1983_StatePlane_Michigan_Central_FIPS_2112_Feet",
    GEOGCS["GCS_North_American_1983",
        DATUM["North_American_Datum_1983",
            SPHEROID["GRS_1980",6378137,298.257222101]],
        PRIMEM["Greenwich",0],
        UNIT["Degree",0.017453292519943295]],
    PROJECTION["Lambert_Conformal_Conic_2SP"],
    PARAMETER["False_Easting",19685000],
    PARAMETER["False_Northing",0],
    PARAMETER["Central_Meridian",-84.36666666666666],
    PARAMETER["Standard_Parallel_1",44.18333333333333],
    PARAMETER["Standard_Parallel_2",45.7],
    PARAMETER["Latitude_Of_Origin",43.31666666666667],
    UNIT["Foot_US",0.30480060960121924],
    AUTHORITY["EPSG","102689"]]
Geometry Column = geom
osm_id: String (10.0)
type_: String (0.0)
fclass: String (20.0)
name: String (100.0)
keypin: String (80.0)
landusecod: String (80.0)
landuse: String (80.0)
localfips: String (80.0)

this layer always throws an error, when I try and export to to FileGDB

when I run st_isvalid all the rows return true and when i run st_geometrytype on this layer they all return st_polygon

picture of the table:
enter image description here

Best Answer

After looking at your GEOMETRY_COLUMNS table, it looks the issue is that some of the SRID's and geometry type are undefined or not defined correctly for ogr2ogr to handle!

1.) Fix the missing SRIDs and you can use either the ST_UpdateGeometrySRID or ST_SetSRID SQL commands to do that:

http://postgis.net/docs/UpdateGeometrySRID.html
http://postgis.net/docs/ST_SetSRID.html

It won't re-project the data, but update the SRID to be something other then 0!

2.) Then the other issue is correcting the geometry type to something more explicit (like POLYGON, MULTIPOLYGON, LINESTRING, etc...) because geometry type GEOMETRY is intended for mixed geometry types and that will cause ogr2ogr to fail when exporting to FileGDB.

 ALTER TABLE tablename ALTER COLUMN geom type geometry(Polygon, 102689);

and

ALTER TABLE tablename ALTER COLUMN geom type geometry(MultiPolygon, 102689) using ST_Multi(geom);

On a side note, I would not recommend storing geometry tables in the public schema - consider placing all geometry tables in a separate schema or multiple schemas.

Create a new schema, then place the new table in the new schema.

CREATE SCHEMA new_schema;
ALTER TABLE table_name SET SCHEMA new_schema;

This way when you export to FileGDB you won't be trying to grab the postgis metadata tables and junk found in the public schema.

To export only tables from a specific schema, modify your ogr2ogr command like:

ogr2ogr --config FGDB_BULK_LOAD YES -progress -append -f "FileGDB" C:\Users\rizagha\Desktop\data.gdb PG:"host=localhost user=postgres password=xxx dbname=free schemas=new_schema" -t_srs EPSG:4269 -overwrite

Related Question