1

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

... but they don't match my problem.

PostgreSQL version: 9.6.6, PostGIS version: 2.3.1 r15264

asked Jan 6, 2020 at 18:58
2
  • The column order in your SELECT is mixed; you attempt to insert osm_polygon.osm_id into the landuse_background.geom column! Commented Jan 6, 2020 at 20:53
  • Thank you @ThingumaBob, that fixed it. Commented Jan 6, 2020 at 21:46

1 Answer 1

1

The error was that the column order in the SELECT query was mixed, it has to be SELECT id, geom, name, not SELECT id, name, geom. Thanks to @ThingumaBob for the solution.

answered Jan 6, 2020 at 21:51

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.