1

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:

RoadWithPolygon

I want to obtain this:

Clipped

  • What is the WHERE clause for? Aren't the non-intersecting data left untouched anyway by ST_Difference? – Jochen Schwarze May 9 '17 at 10:03
  • The where clause should check the spatial index in order to perform subtractions only in roadnetwork geometries that intersect with the polygon. – Jepessen May 9 '17 at 10:15
  • 1
    What happens if you try it without the where clause? I'm not shure, but do you encounter some sort of inconsistency by changing the type of geometry column (ST_Multi...) and referring to it in a where clause in the same statement? – Jochen Schwarze May 9 '17 at 10:18
  • I obtain more rows as result, that maybe is what I want, but I noticed that I obtain also GEOMETRYCOLLECTION EMPTY rows, that maybe gives me the error. Please see edited question. – Jepessen May 9 '17 at 10:28
  • st_union your linestring and instead of doing an update to the existing table just create a new table and do ST_Difference(public.roadnetwork.geom, public.sub_polygon.geom) – ziggy May 9 '17 at 20:28
1

That's because that only makes sense. If you have a line 0-10, and you remove the range 4-6 you have two lines. You represent that in one geometry as a multiline.

You say,

The roadnetwork table has a geometry column defined as COLUMN geom geometry(LineString,4326);

And then you go on to show in your example..

UPDATE public.roadnetwork
SET geom = ST_Multi()

You can't update public.roadnetwork to a MULTILINESTRING when it's strongly typed on the table to be LineString.

If none of that makes sense look at this..

SELECT ST_AsText(ST_SymDifference(l1,l2)), ST_AsText(l1)
FROM ( VALUES
  (
    ST_MakeLine( ST_MakePoint(0,0),ST_MakePoint(0,10) ),
    ST_MakeLine( ST_MakePoint(0,4),ST_MakePoint(0,6) )
  )
) AS t(l1,l2);

Here we bisect 0,10 removing 4,6. This produces MULTILINESTRING((0 0,0 4),(0 6,0 10)). If you try to insert that into the table typed for a LineString,that holds a LINE(0 10) you would expect an error. And, that's what you're getting.

If none of that makes sense just observe that this doesn't work,

CREATE TABLE foo ( geom geometry(LineString) );

INSERT INTO foo (geom) VALUES ( 
  ST_Multi(
    ST_MakeLine(ST_MakePoint(0,0),ST_MakePoint(0,10))
  )
);
ERROR:  Geometry type (MultiLineString) does not match column type (LineString)

To break a MULTILINESTRING up use ST_Dump

SELECT ST_AsText(d.geom)
FROM ST_GeomFromText('MULTILINESTRING((0 0, 0 1),(0 2,0 3))') AS t
CROSS JOIN LATERAL ST_Dump(t) AS d;
      st_astext      
---------------------
 LINESTRING(0 0,0 1)
 LINESTRING(0 2,0 3)
(2 rows)

Or just set the type of the column to MULTILINESTRING, and elevate all of the LINESTRINGS to MULTILINESTRINGS using ST_Multi

  • I understand the error, but I don't know how is possible to convert MultiLineString into one (or more) LineString in the query in order to re-insert them into the table. – Jepessen May 11 '17 at 7:42
  • @Jepessen updated. – Evan Carroll May 11 '17 at 7:53
  • Thanks a lot for introducing me ST_Dump command... – Jepessen May 11 '17 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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