CODEWITHSUNDEEP
postgismultilinedifferencemultilinestring
Jepessen
Jepessen

Reputation: 12415

Convert MULTILINESTRING to LINESTRING in query result

I've a roadfollowing table, containing column

COLUMN geom geometry(LineString,4326);

I've also a sub_polygon table, containing a column

COLUMN geom geometry(MultiPolygon,4326);

I want to subtract the polygons stored in sub_polygon from linestrings stored in roadfollowing table, and update the table with these new data.

I've tried to perform following query:

WITH RESULTS as (
SELECT ST_Difference(public.roadnetwork.geom, public.sub_polygon.geom)
FROM public.roadnetwork, public.sub_polygon
),

 FILTERED_RESULTS as (
  SELECT RESULTS.st_difference FROM RESULTS where GeometryType(RESULTS.st_difference) <> 'GEOMETRYCOLLECTION'
)
 UPDATE public.roadnetwork
 SET geom = FILTERED_RESULTS.st_difference
 FROM FILTERED_RESULTS;

but I obtain following error:

ERROR: Geometry type (MultiLineString) does not match column type (LineString)

I've modified the query in order to check results in string format:

WITH RESULTS as (
SELECT ST_Difference(public.roadnetwork.geom, public.sub_polygon.geom)
FROM public.roadnetwork, public.sub_polygon
),

 FILTERED_RESULTS as (
  SELECT ST_AsText(RESULTS.st_difference) FROM RESULTS where GeometryType(RESULTS.st_difference) <> 'GEOMETRYCOLLECTION'
)

SELECT * from FILTERED_RESULTS;

and I can see that there are some MULTILINESTRING in results, that cannot be copied in roadnetwork.geom column, because data are not consistent:

...
MULTILINESTRING((51.5054201 25.3462475,51.505411 25.3462656,51.5052981 25.3464467,51.5051894 25.3466039,51.5049763 25.3469023,51.5048058 25.347141,51.5046538 25.347324,51.5044476 25.3475493,51.5041983 25.3478035,51.5038722 25.3481104,51.5035605 25.3483885,51.509695 25.3489269,51.5026179 25.3492445,51.5022888 25.349556),(51.5022888 25.349556,51.5022898 25.3495551),(51.5022888 25.349556,51.5017303 25.3500517))
LINESTRING(51.5017303 25.3500517,51.5014725 25.3502989,51.5013472 25.3504121)
LINESTRING(51.5013472 25.3504121,51.501175 25.3505679)
...

How can I update my query in order to convert MULTILINESTRING to LINESTRING so I can update successfully my table?

Upvotes: 2

Views: 10032

Answers (1)

fradal83
fradal83

Reputation: 2022

You could use st_dump to expand MultiLineStrings to LineStrings.

Something like this

WITH RESULTS as (
SELECT ST_Difference(public.roadnetwork.geom, public.sub_polygon.geom)
FROM public.roadnetwork, public.sub_polygon
),

 FILTERED_RESULTS as (
  SELECT RESULTS.st_difference FROM RESULTS where GeometryType(RESULTS.st_difference) <> 'GEOMETRYCOLLECTION'
),

expanded_results as (
    select (a.p_geom).geom
    from (SELECT ST_Dump(FILTERED_RESULTS.st_difference))
),

SELECT * from expanded_results;

Upvotes: 1

Related Questions