7

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

  • 1
    Ogr2ogr should be totally happy with reading generic geometries. Please tell more about your use case and what problems you have. – user30184 Apr 5 '17 at 13:33
  • @user30184 this is my post. I updated all the SRID's and it still craps out on me gis.stackexchange.com/questions/234164/… – ziggy Apr 5 '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 5 '17 at 13:46
  • okay il do that but it already tells me which layer stops the process and why – ziggy Apr 5 '17 at 13:52
  • 1
    If you have multipolygons you must explode then for getting polygons. Ogr2ogr has an documented option -explodecollections for that purpose gdal.org/ogr2ogr.html. With PostGIS you will use ST_Dump postgis.net/docs/ST_Dump.html. – user30184 Apr 5 '17 at 14:11
20

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);
  • check my edit! this did work for some of the layers – ziggy Apr 5 '17 at 14:07
  • 4
    ALTER TABLE oak_all_buffers ALTER COLUMN geom type geometry(MultiPolygon, 102689) using ST_Multi(geom); – HeikkiVesanto Apr 5 '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 4 '18 at 11:54
  • 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 3 at 9:37

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.