Invalid import into postgis

[whizziwig]

Hi all–

Has anyone successfully imported 10m-admin-1 into postgis? I’m trying with postgis 1.5.2, and it seems to be generating a lot of invalid data.

My import command looks like this:

shp2pgsql -W LATIN1 -I 10m-admin-1-states-provinces-shp > 10m-admin-1-states-provinces-shp.sql

and of the import, 148 rows have invalid geometry, with errors like:

blackmad=# select fips_1 FROM public.”10m-admin-1-states-provinces-shp” WHERE ST_IsValid(the_geom) = false;

NOTICE: Holes are nested at or near point 101.662 3.04074

NOTICE: Self-intersection at or near point 120.185 22.9625

NOTICE: IllegalArgumentException: Invalid number of points in LinearRing found 2 – must be 0 or >= 4

NOTICE: Ring Self-intersection at or near point -47.3025 -16.0401

NOTICE: Too few points in geometry component at or near point -65.458 -22.1012

This cleangeometry.sql script @ http://www.sogis1.so.ch/sogis/dl/postgis/cleanGeometry.sql fixes most, but the LinearRing errors cause the script to choke. Does anyone know how to fix these?

thanks

–dave

[Nathaniel]

You might ping Mike Migurski at Stamen Design. A forthcoming version of the file will fix these topology errors. They don’t matter for most design and GIS applications. PostGIS seems particularly strict.

_Nathaniel

[darkblueb]

hi- I have done some preliminary investigation with PostGIS. I ran ~67 tables through PostGIS st_buffer(0), (19 failed for various reason, including all of the bathymetry tables) but its a start. More to come…

[darkblueb]

I am building a script that does the auto-cleaning..

Postgres has its own behaviour about table names and field names..

In particular, lower case is the default for most things, and starting with numbers requires special quoting. I am trying to catch all the small gotcha’s , but the bathymetry table and field names are particularly challenging.. if possible, maybe the naming could be adjusted the next time around

[Nathaniel]

@darkblueb: Thanks for working on the cleaning script for Postgres. I’m sure it will be a great benefit for folks and we can archive a copy of it here. I agree it would be best to rename the fields in the source tables to work with the most end uses. We already limit field names so the files will open in ArcGIS as SHPs and have “_” in file names rather than “-“. When you refer to the numbers in names, do you mean “10m_admin_0” where the leading 1 is causing problems? That’s a bit harder to fix since we need to distinguish between the 3 sets of data. Do you have a recommendation?

[darkblueb]

well I have the gnarly quoting worked out now.. it was the upper case letters in the field names and table names that were hard.. In general, stick to lower case letters in table names and field names if at all possible.. as far as leading numbers go, you can pre-pend a single letter and still use your naming, if you choose to.. as said, this script now handles it, its just going to make other scripts in the future harder,also…

[darkblueb]

fyi- here is the error log from the latest script.. 12 files failed to convert…

=============================================================

67 shp tables attempted, 39 errors

---

ERROR: 10m_lakes_europe;GEOS buffer() threw an error!

---

ERROR: 10m_glaciated_areas;GEOS buffer() threw an error!

---

ERROR: 10m_lakes;GEOS buffer() threw an error!

---

ERROR: 10m_ocean;POSTGIS2GEOS conversion failed

---

ERROR: 10m_land;GEOS buffer() threw an error!

---

ERROR: 10m_minor_islands;POSTGIS2GEOS conversion failed

---

ERROR: 10m_geography_regions_polys;GEOS buffer() threw an error!

---

ERROR: 10m_bathymetry_L_0;POSTGIS2GEOS conversion failed

---

ERROR: 10m_bathymetry_K_200;POSTGIS2GEOS conversion failed

---

ERROR: 10m_bathymetry_I_2000;POSTGIS2GEOS conversion failed

---

ERROR: 10m_bathymetry_H_3000;POSTGIS2GEOS conversion failed

---

ERROR: 10m_bathymetry_F_5000;POSTGIS2GEOS conversion failed

---

ERROR: 10m_admin_1_states_provinces_shp;POSTGIS2GEOS conversion failed

[Nathaniel]

Can you tell me the difference between the two types of errors?

ERROR: 10m_lakes;GEOS buffer() threw an error!

---

ERROR: 10m_ocean;POSTGIS2GEOS conversion failed

One looks like an import error based on feature topology. The other looks like an extent error when it tries to do something with the coordinates?

[darkblueb]

hmm learning more here…

PostGIS 2.0 “trunk” (under development) provides this on the bathymetry

=============================================================

12 shp tables attempted, 15 errors

---

ERROR: 10m_bathymetry_L_0;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_bathymetry_K_200;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_bathymetry_I_2000;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_bathymetry_H_3000;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_bathymetry_F_5000;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

[Nathaniel]

The bathymetry is a nasty complicated file. Let me repair the geometry on it and see if that cleans it up for you? If you’re not doing anything special with it, you might consider using the rasterized version of that instead.

[darkblueb]

more informative errors in PostGIS trunk, sans bathymetry…

=============================================================

55 shp tables attempted, 24 errors

---

ERROR: 10m_lakes_europe;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_glaciated_areas;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_lakes;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_ocean;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

---

ERROR: 10m_land;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_minor_islands;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

---

ERROR: 10m_geography_regions_polys;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_admin_1_states_provinces_shp;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

[Nathaniel]

For all the themes mentioned above except 10m_admin_1_states_provinces_shp (which I’ll send Monday), please try importing the following files (include the bathymetry, too). I’ve cleaned up the topology in ArcMap using Repair Geometry, deleting null polys and correcting self intersections. I also clipped a few themes that were outside of the ±180, ±90 bounding box. I also modified three continent polygons in the geography label area polys so labels won’t get auto placed in the ocean. http://kelsocartography.com/dropbox/temp/10m_physical.zip

[darkblueb]

looking better!

=============================================================

67 shp tables attempted, 2 errors

---

ERROR: 10m_land;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_admin_1_states_provinces_shp;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

real 3m29.797s

user 0m41.840s

sys 0m5.180s

[darkblueb]

a few more nits, from a related tool

---

— 10m_coastline —

Warning 1: Longitude 180.000011 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

— 10m_geographic_lines —

Warning 1: Longitude -180.000015 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_reefs —

Warning 1: Longitude -180.000041 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_land —

Warning 1: Longitude 180.000011 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_geography_marine_polys —

Warning 1: Longitude -180.000144 has been modified to fit into range [-180,180]. This warning will not be issued any more

ERROR 1: Latitude 90.001225 is invalid. Valid range is [-90,90]. This warning will not be issued any more

.

.

— 10m_antarctic_ice_shelves_polys —

Warning 1: Longitude 180.000011 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_antarctic_ice_shelves_lines —

Warning 1: Longitude -180.000015 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_graticules_5 —

Warning 1: Longitude -180.003348 has been modified to fit into range [-180,180]. This warning will not be issued any more

ERROR 1: Latitude 90.000011 is invalid. Valid range is [-90,90]. This warning will not be issued any more

.

.

— 10m_graticules_10 —

Warning 1: Longitude -180.003348 has been modified to fit into range [-180,180]. This warning will not be issued any more

ERROR 1: Latitude 90.000011 is invalid. Valid range is [-90,90]. This warning will not be issued any more

.

.

.

.

— 10m_graticules_30 —

Warning 1: Longitude 180.000000 has been modified to fit into range [-180,180]. This warning will not be issued any more

ERROR 1: Latitude 90.000011 is invalid. Valid range is [-90,90]. This warning will not be issued any more

.

.

.

— 10m_graticules_1 —

Warning 1: Longitude -180.003348 has been modified to fit into range [-180,180]. This warning will not be issued any more

ERROR 1: Latitude 90.000011 is invalid. Valid range is [-90,90]. This warning will not be issued any more

.

.

— 10m_graticules_15 —

Warning 1: Longitude -180.003348 has been modified to fit into range [-180,180]. This warning will not be issued any more

ERROR 1: Latitude 90.000011 is invalid. Valid range is [-90,90]. This warning will not be issued any more

.

.

— 10m_wgs84_bounding_box —

Warning 1: Longitude -180.003339 has been modified to fit into range [-180,180]. This warning will not be issued any more

ERROR 1: Latitude 90.000022 is invalid. Valid range is [-90,90]. This warning will not be issued any more

.

.

.

.

— 10m_graticules_20 —

Warning 1: Longitude -180.003348 has been modified to fit into range [-180,180]. This warning will not be issued any more

ERROR 1: Latitude 90.000011 is invalid. Valid range is [-90,90]. This warning will not be issued any more

.

.

.

— 10m_admin_0_sovereignty —

Warning 1: Longitude 180.000258 has been modified to fit into range [-180,180]. This warning will not be issued any more

— 10m_admin_0_map_subunits —

Warning 1: Longitude 180.000258 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_admin_0_boundary_lines_maritime_indicator —

Warning 1: Longitude -180.000496 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_admin_0_pacific_groupings —

Warning 1: Longitude 180.000052 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_admin_0_scale_ranks —

Warning 1: Longitude 180.000258 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_admin_0_scale_ranks_with_minor-islands —

Warning 1: Layer name ’10m_admin_0_scale_ranks_with_minor-islands’ adjusted to ’10m_admin_0_scale_ranks_with_minor_islands’ for XML validity.

Warning 1: Longitude 180.000258 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_admin_1_states_provinces_shp —

Warning 1: organizePolygons() received an unexpected geometry.

Either a polygon with interior rings, or a polygon with less than 4 points,

or a non-Polygon geometry. Return arguments as a collection.

Warning 1: Geometry of polygon of fid 22 cannot be translated to Simple Geometry. All polygons will be contained in a multipolygon.

Warning 1: organizePolygons() received an unexpected geometry.

Either a polygon with interior rings, or a polygon with less than 4 points,

or a non-Polygon geometry. Return arguments as a collection.

Warning 1: Geometry of polygon of fid 1368 cannot be translated to Simple Geometry. All polygons will be contained in a multipolygon.

Warning 1: Longitude -180.000015 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_admin_0_countries —

Warning 1: Longitude 180.000258 has been modified to fit into range [-180,180]. This warning will not be issued any more

.

.

— 10m_admin_0_map_units —

Warning 1: Longitude 180.000258 has been modified to fit into range [-180,180]. This warning will not be issued any more

[Nathaniel]

Thanks for update. I’m working on fixing the 10m_adm_1 for you. I can clip the others to ±180, ±90 bounds as well. It looks like that covers all the errors?

I also see that “10m_admin_0_scale_ranks_with_minor-islands” has a – in the name between minor and islands. That should be fixed to an underscore.

The 10m_adm_0 variants (sov, country, map unit, map subunit) all have expired thematic attribute values. I will update those for you the next couple days (by Thursday).

[darkblueb]

=============================================================

67 shp tables attempted, 13 errors

---

ERROR: 10m_lakes_europe;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_glaciated_areas;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_lakes;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_ocean;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

---

ERROR: 10m_land;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_minor_islands;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

---

ERROR: 10m_geography_regions_polys;GEOSBuffer: IllegalArgumentException: Ring has fewer than 3 points, so orientation cannot be determined

---

ERROR: 10m_bathymetry_L_0;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

---

ERROR: 10m_bathymetry_K_200;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

---

ERROR: 10m_bathymetry_I_2000;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

---

ERROR: 10m_bathymetry_H_3000;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

---

ERROR: 10m_bathymetry_F_5000;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

---

ERROR: 10m_admin_1_states_provinces_shp;First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 – must be 0 or >= 4

[clapspade]

hey — I’ve noticed that the leading numbers in the shapefile names also impede a successful join with another table in ArcGIS. When trying to view the attribute table afterwards, it drops an error message and the table is empty. At least in Version 9.3.1 SP2. When I rename the shapefile in ArcExplorer so that it starts with a letter, it works fine…

So like suggested here, you should consider another naming scheme. Or just put “ne_” in front of every shapefile name.

keep up the good work!

phil

[Nathaniel]

@Phil: Good idea on the “ne_” prefix!

[Author]

Posts