0

I'm facing a problem when using osgeo/gdal:ubuntu-small-3.2.1 docker image and running these commands on the Natural Earth vector dataset:

# Get and unzip the data 
# (inspired from https://github.com/mapbox/natural-earth-tm2/blob/master/util/setup_db.sh) 
wget -q -N http://naciscdn.org/naturalearth/packages/natural_earth_vector.sqlite.zip
unzip -o -d natural_earth_vector natural_earth_vector.sqlite.zip

Then, insert data into PG using ogr2ogr:


ogr2ogr 

    -progress 

    -f Postgresql 

    -s_srs EPSG:4326 

    -t_srs EPSG:3857 

    -clipsrc -180 -85.05 180.1 85.05 

    PG:"host=localhost port=5432 dbname=mydb user=postgres password=****" 

    -lco GEOMETRY_NAME=geometry 

    -lco OVERWRITE=YES 

    -lco DIM=2 

    -nlt GEOMETRY 

    -overwrite 

    "natural_earth_vector/packages/natural_earth_vector.sqlite"

The PostgreSQL/PostGIS database I'm trying to inject this data into is based on postgis/postgis:11-3.1 docker image.

This is the error I have:

0ERROR 1: TopologyException: Input geom 0 is invalid: Ring Self-intersection at or near point 30.796820538582722 61.704133379158407 at 30.796820538582722 61.704133379158407

This seems to be the culprit watershed in that particular case.

But this error actually appears more than 1'000 thousands times on other geometries .
I'm not sure where to search for a fix. Any ideas?

If this helps, here is the GDAL version number from inside the container:

# ogr2ogr --version
GDAL 3.2.1, released 2020/12/29

This thread sounds related but it's from R: Getting TopologyException: Input geom 1 is invalid which is due to self-intersection in R? and I don't know, if it's the same error, how could I transpose the solution here.

swiss_knight
  • 10,309
  • 9
  • 45
  • 117
  • Have you tried to do a buffer at 0 on all the layer ? – J. Monticolo Feb 20 '21 at 14:20
  • How would you do that in bash? I cannot figure out some buffering option in ogr2ogr:https://gdal.org/programs/ogr2ogr.html – swiss_knight Feb 20 '21 at 14:35
  • Something like this ogrinfo natural_earth_vector/packages/natural_earth_vector.sqlite -dialect SQLite -sql "UPDATE a_sqlite_table SET geometry = ST_BUFFER(geometry, 0) ? – J. Monticolo Feb 20 '21 at 16:23
  • 1
    I would rather use ogrinfo natural_earth_vector/packages/natural_earth_vector.sqlite -dialect SQLite -sql "UPDATE a_sqlite_table SET geometry = ST_MakeValid(geometry). – user30184 Feb 20 '21 at 17:06
  • Do you by chance know from which Debian package I can make use of the ST_MakeValid or any ST_* function within sqlite? I've got plenty of no such function: ST_MakeValid. – swiss_knight Feb 20 '21 at 18:17
  • even MakeValid(geometry) ? – J. Monticolo Feb 20 '21 at 18:55
  • yep: no such function: MakeValid I'm wondering; would it be possible to fix, at the command line, invalid geometries inside a GeoPackage instead of the db.sqlite? – swiss_knight Feb 20 '21 at 19:02
  • 1
    Test it for sure, a Geopackage is a Spatialite DB, so, spatial functions are inside (IMHO). – J. Monticolo Feb 21 '21 at 08:37
  • Tested! Same problem; spatial functions doesn't seem embedded in the GPKG file. At least for the that one I'd like to use: no such function: ST_MakeValid. I also tried without the ST_ prefix and: no such function: MakeValid. – swiss_knight Feb 21 '21 at 09:59
  • 1
    GeoPackage is SQLite DB but not SpatiaLite DB. GeoPackage does not have native spatial functions, other than ST_MinX, ST_MinY, ST_MaxX, ST_MaxY, and ST_IsEmpty which are defined in the standard. GDAL adds a couple of functions https://gdal.org/drivers/vector/gpkg.html but for the most part the support of the SpatiaLite functions goes through the SQLite SQL dialect https://gdal.org/user/sql_sqlite_dialect.html. With SQLite dialect the functions like ST_MakeValid can be used for any datasource. – user30184 Feb 21 '21 at 11:15
  • Actually, for using SpatiaLite functions against GeoPackages, the right dialect to use is "inderect_sqlite". – user30184 Mar 01 '23 at 13:39

0 Answers0