3

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

  • I would rater guess that there is geometry type that is not defined correctly for some reason. You might want to check the geometry types of your failing table with SELECT ST_GeometryType(geom) FROM xxx respectively with SELECT ST_AsText(geom) FROM xxx. The answer to the question you are referring to has been solved by specifying the geometry type in some way, either by giving it as an option to the command line or by a type cast in PostGIS. – chkaiser Apr 3 '17 at 20:52
  • did you try the -nlt option mentioned in this answer to the question you linked to? – Steven Kay Apr 3 '17 at 21:22
  • 1
    @StevenKay there points and polygon not just 1 geometry type – ziggy Apr 3 '17 at 21:56
  • @chkaiser i cant use the -nlt option because its points and polygons. as for checking the geometry, I can export them through the dbf loader, bring them into qgis, arc and there are no problems whatsoever. this unknown layer type only shows up when I export to a fileGDB – ziggy Apr 3 '17 at 21:57
  • in fact ive exported all these tables with ogr2ogr to shapefiles with no problems as well – ziggy Apr 3 '17 at 21:58
1
+50

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

  • this looks promising I will try your suggestions out tomorrow at work! – ziggy Apr 5 '17 at 0:26
  • I am stuck on converting the my geometry type to polygon or multipolygon – ziggy Apr 5 '17 at 13:19

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.