make import-sql fails in New-Zealand with lwgeom_union: GEOS Error: TopologyException:

[quentar]

Trying to generate australia-oceania on fresh machine (no previous db), it fails currently on import-sql step,
drilling down it seems it is is australia-oceania/new-zealand sub region (ie quickstart australia-oceania/new-zealand fails in the same way)
Last release 3.11 does work with same osm.pbf input files - completing all steps and generate tiles.
It seems reproducible for me every time running this region now.

Console :

CREATE FUNCTION
Time: 0.991 ms
psql:/sql/parallel/building.sql:82: ERROR:  lwgeom_union: GEOS Error: TopologyException: Input geom 0 is invalid: Self-intersection at or near point 19566767.608174294 -4873356.7422359968 at 19566767.608174294 -4873356.7422359968
CONTEXT:  PL/pgSQL function osm_building_block_gen1() line 6 at FOR over SELECT rows
Time: 150915.098 ms (02:30.915)

docker-compose logs :

postgres_1                      | ERROR:  lwgeom_union: GEOS Error: TopologyException: Input geom 1 is invalid: Self-intersection at or near point 19566767.608174294 -4873356.7422359968 at 19566767.608174294 -4873356.7422359968
postgres_1                      | CONTEXT:  PL/pgSQL function osm_building_block_gen1() line 6 at FOR over SELECT rows
postgres_1                      | STATEMENT:  CREATE MATERIALIZED VIEW osm_building_block_gen1 AS
postgres_1                      | 	SELECT *
postgres_1                      | 	FROM osm_building_block_gen1();
postgres_1                      | ERROR:  canceling autovacuum task

(Complete docker-compose logs do not seem helpful but for completeness)

Attaching to openmaptiles_postgres_1 postgres_1 | postgres_1 | PostgreSQL Database directory appears to contain a database; Skipping initialization postgres_1 | postgres_1 | LOG: database system was shut down at 2020-06-05 17:19:18 UTC postgres_1 | LOG: MultiXact member wraparound protections are now enabled postgres_1 | LOG: autovacuum launcher started postgres_1 | LOG: database system is ready to accept connections postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landuse_polygon_gen1' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_park_polygon_gen1' to use typmod with srid 3857, type MultiPolygon: Geometry type (Polygon) does not match column type (MultiPolygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_water_polygon_gen1' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landcover_polygon_gen1' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen6, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen3, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen2, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen9, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen8, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen10, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen5, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen11, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen4, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landuse_polygon_gen2' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen1, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_park_polygon_gen2' to use typmod with srid 3857, type MultiPolygon: Geometry type (Polygon) does not match column type (MultiPolygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: No data in table import.osm_border_disp_linestring_gen7, so no information to determine geometry type and srid postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 52 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_water_polygon_gen2' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landcover_polygon_gen2' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landuse_polygon_gen3' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_water_polygon_gen3' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_park_polygon_gen3' to use typmod with srid 3857, type MultiPolygon: Geometry type (Polygon) does not match column type (MultiPolygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landcover_polygon_gen3' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landuse_polygon_gen4' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_water_polygon_gen4' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_park_polygon_gen4' to use typmod with srid 3857, type MultiPolygon: Geometry type (Polygon) does not match column type (MultiPolygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landcover_polygon_gen4' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landuse_polygon_gen5' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_water_polygon_gen5' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_park_polygon_gen5' to use typmod with srid 3857, type MultiPolygon: Geometry type (Polygon) does not match column type (MultiPolygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landcover_polygon_gen5' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landuse_polygon_gen6' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_water_polygon_gen6' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_park_polygon_gen6' to use typmod with srid 3857, type MultiPolygon: Geometry type (Polygon) does not match column type (MultiPolygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landcover_polygon_gen6' to use typmod with srid 3857, type MultiPolygon: Geometry type (Polygon) does not match column type (MultiPolygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landuse_polygon_gen7' to use typmod with srid 3857, type Polygon: Geometry type (MultiPolygon) does not match column type (Polygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_park_polygon_gen7' to use typmod with srid 3857, type MultiPolygon: Geometry type (Polygon) does not match column type (MultiPolygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_landcover_polygon_gen7' to use typmod with srid 3857, type MultiPolygon: Geometry type (Polygon) does not match column type (MultiPolygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | WARNING: Could not convert 'geometry' in 'import.osm_park_polygon_gen8' to use typmod with srid 3857, type MultiPolygon: Geometry type (Polygon) does not match column type (MultiPolygon) postgres_1 | CONTEXT: PL/pgSQL function populate_geometry_columns(oid,boolean) line 64 at RAISE postgres_1 | LOG: checkpoints are occurring too frequently (16 seconds apart) postgres_1 | HINT: Consider increasing the configuration parameter "max_wal_size". postgres_1 | LOG: checkpoints are occurring too frequently (17 seconds apart) postgres_1 | HINT: Consider increasing the configuration parameter "max_wal_size". postgres_1 | LOG: checkpoints are occurring too frequently (15 seconds apart) postgres_1 | HINT: Consider increasing the configuration parameter "max_wal_size". postgres_1 | ERROR: lwgeom_union: GEOS Error: TopologyException: Input geom 1 is invalid: Self-intersection at or near point 19566767.608174294 -4873356.7422359968 at 19566767.608174294 -4873356.7422359968 postgres_1 | CONTEXT: PL/pgSQL function osm_building_block_gen1() line 6 at FOR over SELECT rows postgres_1 | STATEMENT: CREATE MATERIALIZED VIEW osm_building_block_gen1 AS postgres_1 | SELECT * postgres_1 | FROM osm_building_block_gen1();

And thank you for providing amazing vector maps toolkit to everyone!

[TomPohys]

Hi @quentar, it looks like there is some invalid geometry in buildings when it tries to create a materialized view with aggregate buildings. In update_buildings.sql should be added into where condition AND ST_IsValid(geometry) when is new geometry created and is not valid to insert into the materialized view.

Pull Request is very welcome. Thanks

[TomPohys]

Hi @quentar, it is fixed by #1044 by adding ST_SnapToGrid().

[sdacunha]

@TomPohys

I seem to be getting this same issue sadly,

CREATE INDEX Time: 24049.953 ms (00:24.050) psql:/sql/parallel/building.sql:100: ERROR: GEOSUnaryUnion: TopologyException: Input geom 1 is invalid: Self-intersection at or near point -8539739.7009410355 4727139.8920735931 at -8539739.7009410355 4727139.8920735931 CONTEXT: PL/pgSQL function osm_building_block_gen1() line 14 at FOR over SELECT rows Time: 11976201.568 ms (03:19:36.202) xargs: sh: exited with status 255; aborting make: *** [Makefile:397: import-sql] Error 124
Pulled latest master and I'm building the planet. Was going well until the import-sql step

[PoornimaSuresh]

@TomPohys

Running quickstart.sh with north-america/us (after pulling latest master) fails at import-sql with the same error for me as well:
TopologyException: Input geom 1 is invalid: Self-intersection at or near point -8539739.7009410355 4727139.8920735931 at -8539739.7009410355 4727139.8920735931 CONTEXT: PL/pgSQL function osm_building_block_gen1() line 14 at FOR over SELECT rows

[TomPohys]

Hi @sdacunha and @PoornimaSuresh, can you please try to lower the precision of SnapToGrid in update_buildings.sql?

for example:

SELECT (array_agg(dta.osm_id))[1] AS osm_id,
                    ST_Buffer(
                        ST_Union(
                            ST_Buffer(
-                                ST_SnapToGrid(dta.geometry, 0.000001)
+                                ST_SnapToGrid(dta.geometry, 0.001)
                                , zres14, 'join=mitre')
                            )
                        , -zres14, 'join=mitre') AS geometry

[suur]

I'm running into the same issue with import-sql. I tried the fix as suggested above but it made no difference. The import still fails at

psql:/sql/parallel/building.sql:100: ERROR:  GEOSUnaryUnion: TopologyException: Input geom 1 is invalid: Self-intersection at or near point -8532409.9206283446 4763576.7067211652 at -8532409.9206283446 4763576.7067211652
CONTEXT:  PL/pgSQL function osm_building_block_gen1() line 14 at FOR over SELECT rows

[TomPohys]

The first buffer creates some geometries that are not valid. It is not a global problem (founded in Delaware, in Maryland or Czech Republic it is OK). A quick solution can be adding ST_MakeValid but it prolongs an import-sql - quick test on Czech republic prolongs by 20%.

SELECT (array_agg(dta.osm_id))[1] AS osm_id,
                    ST_Buffer(
                        ST_Union(
+                          ST_MakeValid(
                              ST_Buffer(
                                  ST_SnapToGrid(dta.geometry, 0.000001)
                                  , zres14, 'join=mitre')
+                            )
                            )
                        , -zres14, 'join=mitre') AS geometry