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

2
  • The column order in your SELECT is mixed; you attempt to insert osm_polygon.osm_id into the landuse_background.geom column!
    – geozelot
    Jan 6, 2020 at 20:53
  • Thank you @ThingumaBob, that fixed it. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.