Problems with dividing multipolygons into polygons

Question

I have a shapefile with countries boundaries downloaded from here and I am trying to upload it to PostGIS database with ogr2ogr. This file contains features of polygon and multipolygon types and because of that I cannot upload these features in one column.

I tried to divide the multipolygons into several polygons with QGIS and with python+fiona. But some countries got cut and have wrong boundaries.

What may cause that and how can I fix it?

Have you tried to add -nlt MULTIPOLYGON to you ogr2ogr command to force a multipolygon output? — thibautg, May 31 '16 at 12:26
@thibautg Yes, I tried this. Then in code I extracted polygons from multipolygons to visualize this with WorldWind and got that problem again. — petrolesha, May 31 '16 at 12:34
I didn't know WorldWind. It seems very nice though. Do you connect it directly to PostGIS or do you need to export the table to a file (for example a KML)? — thibautg, May 31 '16 at 13:15

thibautg · Answer 1 · 2016-05-31T15:58:29.710

1

To import the Shapefile to PostGIS:

ogr2ogr -f "PostgreSQL" PG:"host=localhost user=user dbname=db_name password=pw" -nln public.world_borders -nlt MULTIPOLYGON "TM_WORLD_BORDERS-0.3.shp"

The Shapefile is imported as MULTIPOLYGON.

If you want to expode it to POLYGON in PostGIS, use ST_Dump:

SELECT 
  ogc_fid, 
  fips, 
  iso2, 
  iso3, 
  un, 
  name, 
  area, 
  pop2005, 
  region, 
  subregion, 
  lon, 
  lat, 
  ((ST_Dump(wkb_geometry)).geom)::geometry(Polygon,4326) geom
FROM public.world_borders;

Edit: this does not work because the initial Shapefile is corrupted. I've run QGIS's "Check geometry validity" tool and here is the output:

Try contacting the creator of the file to get a repaired version.

edited May 31 '16 at 15:58

answered May 31 '16 at 12:56

thibautg

2,097
10
16

I did so, but got the same result. – petrolesha May 31 '16 at 13:16
I've opened the above query in QGIS via DB Manager and I don't see the same artifacts as you do. Maybe a problem with your viewer (WorldWind)? – thibautg May 31 '16 at 13:28
I've opened it in QGIS and didn't see any artifacts too. But I noticed that when I retrieve polygons in Java some of them has unequal first and last points. I tried simply add first point at the end but it had no effect. – petrolesha May 31 '16 at 14:01
Indeed it seems that some of the polygons are not valid (I ran SELECT a.*, ST_IsValid(geom) FROM ( SELECT ogc_fid, fips, iso2, iso3, un, name, area, pop2005, region, subregion, lon, lat, ((ST_Dump(wkb_geometry)).geom)::geometry(Polygon,4326) geom FROM public.world_borders ) a WHERE NOT ST_IsValid(geom) – thibautg May 31 '16 at 14:09
Maybe try ST_MakeValid like that (very slow though): SELECT ogc_fid, fips, iso2, iso3, un, name, area, pop2005, region, subregion, lon, lat, ST_MakeValid(((ST_Dump(wkb_geometry)).geom))::geometry(Polygon,4326) geom FROM public.world_borders; – thibautg May 31 '16 at 14:13
Tried. That didn't change anything. – petrolesha May 31 '16 at 14:25
Maybe try to first MakeValid the multipolygon: ((ST_Dump(ST_MakeValid(wkb_geometry))).geom)::geometry(Polygon,4326) geom OR add a buffer of 0.0 with ((ST_Dump(ST_Buffer(wkb_geometry,0.0))).geom)::geometry(Polygon,4326) geom like suggested here – thibautg May 31 '16 at 14:36
Tried both, no effect. – petrolesha May 31 '16 at 15:10
I've run out of ideas to repair the geometries. The original Shapefile is damaged. I've updated my answer. – thibautg May 31 '16 at 15:59

Problems with dividing multipolygons into polygons

1 Answers1