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

Improve this question
12
  • 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, 2017 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, 2017 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, 2017 at 13:46
  • okay il do that but it already tells me which layer stops the process and why
    – ziggy
    Apr 5, 2017 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, 2017 at 14:11

1 Answer 1

Reset to default
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);
Improve this answer
4
  • check my edit! this did work for some of the layers
    – ziggy
    Apr 5, 2017 at 14:07
  • 4
    ALTER TABLE oak_all_buffers ALTER COLUMN geom type geometry(MultiPolygon, 102689) using ST_Multi(geom);
    – HeikkiVesanto
    Apr 5, 2017 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, 2018 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 3, 2019 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.