I have a database with OpenStreetMap polygons, points and relations in separate tables. Now I wanted to build selection queries for the different features and created schemas for each zoom level (5k, 10k, 20k and so on). However, I'm unable to insert multipolygons into the "landuse_background" table, even though the geometry column is configured for multipolygons:
CREATE SCHEMA "5k";
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA "5k";
CREATE TABLE "5k"."landuse_background" (
"id" BIGINT NOT NULL UNIQUE PRIMARY KEY,
"geom" geometry(MULTIPOLYGON, 4326) NOT NULL,
"name" TEXT
);
As a test, I wanted to insert every polygon as a multipolygon (so that I can later on combine them):
INSERT INTO "5k"."landuse_background" (
SELECT osm_id as id, name, ST_Multi(geom)
FROM "public"."osm_polygon"
GROUP BY osm_id
);
However, I get the error:
ERROR: parse error - invalid geometry
If I only execute the "select" part of my query, I get valid polygons:
SELECT
osm_id as id,
name,
ST_AsText(ST_Multi(geom)) as geom,
ST_IsValidReason(ST_Multi(geom)) as is_valid
FROM public.osm_polygon
GROUP BY osm_id;
I get valid geometry:
8023823 "Schlossgarten" "MULTIPOLYGON(((8.3999983 49.0136811,8.3999971 49.0136871,8.4000414 49.0136908, (...))))" "Valid Geometry"
8085105 "Alter Flugplatz" "MULTIPOLYGON(((8.3824917 49.0242842,8.3820936 49.0217043,8.3821084 49.0216129, (...))))" "Valid Geometry"
19782878 "Hermann-Ehlers-Kolleg" "MULTIPOLYGON(((8.3971125 49.0156717,8.3971956 49.015771,8.3971811 49.0157767, (...))))" "Valid Geometry"
20909486 "Augustin-Bea-Haus" "MULTIPOLYGON(((8.3958855 49.0162626,8.3958992 49.0162606,8.3958896 49.0162402, (...))))" "Valid Geometry"
20911412 "Reinhold-Schneider-Haus" "MULTIPOLYGON(((8.3965158 49.0161899,8.396547 49.016189,8.3965608 49.0162083, (...))))" "Valid Geometry"
23138236 "Domus 7" "MULTIPOLYGON(((8.3952316 49.0161717,8.3952569 49.016172,8.3952871 49.0161686, (...))))" "Valid Geometry"
ALL geometries are valid, selecting invalid polygons returns zero rows.
So I thought, it might be an issue with the CREATE TABLE
statement and checked the column types of the landuse_background
table:
SELECT * FROM information_schema.columns
WHERE table_schema = '5k'
AND table_name = 'landuse_background';
table_schema table_name column_name data_type udt_schema udt_name
"5k" "landuse_background" "id" "bigint" "pg_catalog" "int8"
"5k" "landuse_background" "geom" "USER-DEFINED" "public" "geometry"
"5k" "landuse_background" "name" "text" "pg_catalog" "text"
The only noticable thing is that the geom
column has a data type of USER-DEFINED
instead of the expected MultiPolygon
. I've tried several identifiers in the CREATE TABLE
statement, such as MultiPolygon
, MULTIPOLYGON
, "MultiPolygon"
, but none seem to work.
I've already looked at the similar answers
- PostGIS Update parse error – invalid geometry
- ERROR: parse error - invalid geometry (Postgis)
- parse error - invalid geometry even though ST_ISVALID returns true
... but they don't match my problem.
PostgreSQL version: 9.6.6, PostGIS version: 2.3.1 r15264
SELECT
is mixed; you attempt to insertosm_polygon.osm_id
into thelanduse_background.geom
column!