15

I have a table that has its geometry type as geometry and I need to convert it to Polygon for ogr2ogr to read it. Can't figure this out.

I have tried 

ALTER TABLE oak_all_buffers ALTER COLUMN geom type geometry(MultiPolygon, 102689);

error 

    ERROR:  Geometry type (MultiPolygon) does not match column type (Polygon)
    ********** Error **********

    ERROR: Geometry type (MultiPolygon) does not match column type (Polygon)
    SQL state: 22023

and 

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

ERROR:  Geometry type (Polygon) does not match column type (MultiPolygon)
********** Error **********

ERROR: Geometry type (Polygon) does not match column type (MultiPolygon)
SQL state: 22023

enter image description here

ziggy
  • 4,515
  • 3
  • 37
  • 83
  • 1
    Ogr2ogr should be totally happy with reading generic geometries. Please tell more about your use case and what problems you have. – user30184 Apr 05 '17 at 13:33
  • @user30184 this is my post. I updated all the SRID's and it still craps out on me http://gis.stackexchange.com/questions/234164/ogr2ogr-export-postgresql-to-esri-file-gdb-wkbunknown-layer-geometry-type/234706#234706 – ziggy Apr 05 '17 at 13:38
  • First thing that I would do is to make an own ogr2ogr command for each layer instead of trying to convert the whole database with one shot. – user30184 Apr 05 '17 at 13:46
  • okay il do that but it already tells me which layer stops the process and why – ziggy Apr 05 '17 at 13:52
  • I would like to know how to change the geometry type regardless – ziggy Apr 05 '17 at 13:53
  • I would try something like ogr2ogr -f gpkg -sql "select * from oak_all_buffers where OGR_GEOMETRY='POLYGON'" output.gpkg PG:input_string -nlt POLYGON. I wrote intentionally GeoPackage as outputformat because I do not know file geodatabase. – user30184 Apr 05 '17 at 14:07
  • specifying the -nlt polygon works for one layer at a time, but correlating this back to my original problem is I cant specify -nlt Polygon because I have 18 tables that are either points or polygons... – ziggy Apr 05 '17 at 14:09
  • 1
    If you have multipolygons you must explode then for getting polygons. Ogr2ogr has an documented option -explodecollections for that purpose http://www.gdal.org/ogr2ogr.html. With PostGIS you will use ST_Dump http://postgis.net/docs/ST_Dump.html. – user30184 Apr 05 '17 at 14:11
  • Make a script or a batch file. – user30184 Apr 05 '17 at 14:12
  • To convert a multipolygon column to a polygon geometry column... HIS: ALTER TABLE oak_all_buffers ALTER COLUMN geom type geometry(Polygon, 102689); YOURS: ALTER TABLE oak_all_buffers ALTER COLUMN geom type geometry(Polygon, 102689); There is no difference in what Ziggy reported and the solution. I have the same issue converting a MultiPolygon to Polygon geometry. What am I missing. – user9491577 Oct 12 '18 at 11:12
  • This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. You can also add a bounty to draw more attention to this question once you have enough reputation. - From Review – LaughU Oct 12 '18 at 12:44
  • @user9491577 use (ST_Dump(geom)).geom – ziggy Oct 12 '18 at 18:22

1 Answers1

43

You can change the Column Type to Polygon with this command:

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

or change to MultiPolygon if you have mixed Polygon/Multipolygon (credits @Vesanto)

ALTER TABLE oak_all_buffers ALTER COLUMN geom type geometry(MultiPolygon, 102689) using ST_Multi(geom);
pLumo
  • 6,489
  • 20
  • 41
  • check my edit! this did work for some of the layers – ziggy Apr 05 '17 at 14:07
  • 4
    ALTER TABLE oak_all_buffers ALTER COLUMN geom type geometry(MultiPolygon, 102689) using ST_Multi(geom); – HeikkiVesanto Apr 05 '17 at 14:09
  • Quick and dirty way to generate UPDATE statement text for all dim > 2 geometries: SELECT 'ALTER TABLE ' || f_table_schema || '.' || f_table_name || ' ALTER COLUMN ' || f_geometry_column || ' TYPE geometry(' || "type" || ', ' || srid || ') USING ST_Force2D(' || f_geometry_column || ');' query FROM geometry_columns WHERE coord_dimension > 2;. Could be made a lot prettier with a function. – Pocketsand Jul 04 '18 at 11:54
  • 1
    How to convert multipoint to point?

    I try with

    'ALTER TABLE geodb.v_rete_prova ALTER COLUMN geometry TYPE geometry(MultiPointZ,3003) USING ((st_dump(geometry)).geom::geometry(pointz,3003));' but it doesn't work

     – Roberto Marzocchi Sep 03 '19 at 09:37
  • what if i want multi srid geometries in the same table ? – romain gal Mar 04 '24 at 12:57