I've two tables, one with a roadnetwork and another one with some polygon. The roadnetwork table has a geometry column defined as
COLUMN geom geometry(LineString,4326);
while the polygon table has a geometry column defined as
COLUMN geom geometry(MultiPolygon,4326);
I want to perform a Boolean operation, by subtracting the polygon geometry from the roadnetwork geometry, in order to make an hole inside my roadnetwork.
I've tried the following query, that I've found in stackoverflow:
UPDATE public.roadnetwork
SET geom = ST_Multi(ST_Difference(public.roadnetwork.geom, public.sub_polygon.geom))
FROM public.sub_polygon
WHERE ST_Intersects(public.roadnetwork.geom, public.sub_polygon.geom);
But I got the following error:
subtractpolygon.sql:4: ERROR: Geometry type (MultiLineString) does not match column type (LineString)
How can I subtract the polygons defined in polygon
table from the roadnetwork
table?
EDIT:
If I run this query:
SELECT ST_AsText(ST_Difference(public.roadnetwork.geom, public.sub_polygon.geom))
FROM public.roadnetwork, public.sub_polygon
WHERE ST_Intersects(public.roadnetwork.geom, public.sub_polygon.geom)
I obtain some results, in which there are both LINESTRING
and GEOMETRYCOLLECTION EMPTY
:
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION EMPTY
LINESTRING(51.5118811558203 25.3205662647387,51.512059 25.3202912,51.512134 25.3201413,51.5121684 25.320028,51.5121684 25.319968,51.5121396 25.3198288)
GEOMETRYCOLLECTION EMPTY
LINESTRING(51.5128903 25.3196673,51.5129743 25.3198392,51.5130642 25.3199292,51.5132841 25.3200641,51.5136939 25.320329,51.5139688 25.3205439,51.5143176 25.3208249,51.5145508 25.321047,51.5146999055034 25.3211917269465)
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION EMPTY
LINESTRING(51.5170740202354 25.331309846686,51.5170939 25.3329918,51.5171189 25.3333467,51.5171813 25.3336378,51.5172913 25.3339226,51.5174162 25.3341775,51.5175362 25.3343724,51.5176661 25.3345324,51.5178627 25.3346364)
I think that when I update the geom
column I got the error because I've empty GEOMETRYCOLLECTION
in query results, so it cannot be inserted in geom
table that wants LineString
. How can I change the query in order to filter unwanted data?
EDIT2
I've updated my query in order to remove empty geometry collections that don't affect my results:
WITH RESULTS as (
SELECT ST_Difference(public.roadnetwork.geom, public.sub_polygon.geom)
FROM public.roadnetwork, public.sub_polygon
),
FILTERED_RESULTS as (
SELECT * FROM RESULTS WHERE RESULTS.st_difference <> '0107000020E610000000000000'
)
UPDATE public.roadnetwork
SET geom = FILTERED_RESULTS.st_difference FROM FILTERED_RESULTS;
The problem is that now I've the following error
Geometry type (MultiLineString) does not match column type (LineString)
In this case I can't filter MultiLineString
because I need these data. Probably they are results of ST_Difference
because I've not them in my roadnetrowk table. So I need to find a way to process FILTERED_RESULTS
in my query in order to replace MultiLinestring
with corresponding LineString
. I've tried ST_LineMerge
but with no change in my data.
Problem in graphic way
I've this road network and this polygon:
I want to obtain this:
GEOMETRYCOLLECTION EMPTY
rows, that maybe gives me the error. Please see edited question. – Jepessen May 9 '17 at 10:28