Invalid import into postgis

Home Forums Advice & Tutorials Software & Services Invalid import into postgis

Tagged: 

This topic contains 17 replies, has 1 voice, and was last updated by  Nathaniel 6 years, 10 months ago.

Viewing 18 posts - 1 through 18 (of 18 total)
  • Author
    Posts
  • #3417

    whizziwig
    Participant

    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

    #4106

    Nathaniel
    Keymaster

    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

    #4107

    darkblueb
    Participant

    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…

    #4108

    darkblueb
    Participant

    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

    #4109

    Nathaniel
    Keymaster

    @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?

    #4110

    darkblueb
    Participant

    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…

    #4111

    darkblueb
    Participant

    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

    #4112

    Nathaniel
    Keymaster

    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?

    #4113

    darkblueb
    Participant

    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

    #4114

    Nathaniel
    Keymaster

    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.

    #4115

    darkblueb
    Participant

    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

    #4116

    Nathaniel
    Keymaster

    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

    #4117

    darkblueb
    Participant

    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

    #4118

    darkblueb
    Participant

    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

    #4119

    Nathaniel
    Keymaster

    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).

    #4120

    darkblueb
    Participant

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

    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

    #4121

    clapspade
    Participant

    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

    #4122

    Nathaniel
    Keymaster

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

Viewing 18 posts - 1 through 18 (of 18 total)

You must be logged in to reply to this topic.