2

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.

NASA WorldWind visualization

What may cause that and how can I fix it?

4
  • 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 Yes, I directly connect to PostGIS with JDBC. – petrolesha May 31 '16 at 13:28
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:

Check geometry validity tool screenshot

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

9
  • 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

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.