[GIS] Removing unwanted Linestrings from Multilinestring in Postgis

postgispostgresql

I have a Multilinestring layer and I have noticed that in some of my geometries there are unwanted Linestrings. They are composed of two points (start and end) but both are the exact same points. That is wreaking havoc when i try to read this layer from SQLServer.

Example of one geometry:

   "MULTILINESTRING((-3.00712325415935 43.3437538570123,-3.00711888681593 43.3437506773148),
   (-3.00693096810649 43.34361446115,-3.00692660078307 43.3436112814451),
   (-3.00692660078307 43.3436112814451,-3.00692660078307 43.3436112814451),
   (-3.00692660078307 43.3436112814451,-3.0068846515836 43.3436419720614),
   (-3.00692660078307 43.3436112814451,-3.0069222334601 43.34360810174),
   (-3.00691127288165 43.3436001834041,-3.00690690556026 43.3435970036984))"

Notice third Linestring that has the same start and end points. How could I remove these unwanted Linestrings from my geometries?

One approach could be to remove the linestrings which st_length=0 but I don't know how to build the query. I suppose I need to use st_linemerge first to split the multi geometry into single parts?

I need some expert help on this…

Best Answer

You can use ST_Dump to pull out the individual Linestrings, test those for length > 0, and then recreate the Multilinestring using ST_Collect (rather than ST_Union or ST_LineMerge, which involve actual spatial processing calculations, rather than simply combining the underlying geometries into their multi version, as pointed out by @dbaston).

SELECT 
     ST_AsText(ST_Collect(geom)) 
  FROM 
    (SELECT    
        (ST_Dump(
           ST_GeomFromText('MULTILINESTRING(
             (-3.00712325415935 43.3437538570123,-3.00711888681593 43.3437506773148),
             (-3.00693096810649 43.34361446115,-3.00692660078307 43.3436112814451),
             (-3.00692660078307 43.3436112814451,-3.00692660078307 43.3436112814451),
             (-3.00692660078307 43.3436112814451,-3.0068846515836 43.3436419720614),
             (-3.00692660078307 43.3436112814451,-3.0069222334601 43.34360810174),
             (-3.00691127288165 43.3436001834041,-3.00690690556026 43.3435970036984))'))
       ).geom) g
WHERE ST_Length(g.geom) > 0;

which produces:

MULTILINESTRING(
    (-3.00691127288165 43.3436001834041,-3.00690690556026 43.3435970036984),
    (-3.00712325415935 43.3437538570123,-3.00711888681593 43.3437506773148),
    (-3.00692660078307 43.3436112814451,-3.0069222334601 43.34360810174),
    (-3.00693096810649 43.34361446115,-3.00692660078307 43.3436112814451),
    (-3.00692660078307 43.3436112814451,-3.0068846515836 43.3436419720614))

ie, with the line with the repeated coordinates gone. Obviously, you will want to remove the ST_AsText which is just for illustration. Note, ST_Dump is a set returning function, so you have to wrap it in parentheses followed by .geom, to get the geometry back, ie,

(ST_Dump(geom)).geom

which feels a bit strange at first.

You could do something similar to the above using a count of distinct points, but I think, testing on the length as you suggested, is the cleanest.

EDIT. It actually turns out that in this case, you don't even need to test for the length of the linestring, as ST_Union ignores the point when building the MultiLinestring back up. There are other ways to ignore certain geometry types when doing a union, such as testing with ST_GeometryType in the where clause.

Related Solutions

[GIS] Cannot st_linemerge a multilinestring because its not properly ordered

After analysing your MultiLineString, I can say that st_linemerge cannot merge it, not because it's not properly ordered, but because it's impossible to be ordered and merged into a single LineString containing all its 12 parts.

Therefore, st_linemerge works as expected, as stated in st_LineMerge, and, because merging cannot be done, it returns the original MultiLineString. (see st_linemerge_original_multilinestring.sql)

Now, the explanation:

1) Your MultiLineString contains 12 parts (LineStrings):

enter image description here

2) To understand better how to reorder the parts (or if the parts can be reordered) I've choosed to work "visually", in QGIS. Here's a portion of the result, after I've finished to open the PostGIS table containing the MultiLineString: enter image description here

It's easy to see that this MultiLineString' shape, cannot pass the st_linemerge geometry validation tests. Here's the shapefile, if you want to see it in QIS.

3) I've gone further with splitting the MultiLineString in LineStrings, enter image description here

and after some makeover, i've obtained a more clearer picture of the twelve individual LineStrings: enter image description here

This is the shapefile, also, for you to analyse it in QIS.

To proove that st_linemerge works well, when you make a correct choice of the parts, please, include in the command the following LineStrings: 1,2,4,5,7,8,10 and 12. Here's the sql query: st_linemerge_only_eight_linestrings.sql Running this, you'll get a single, long, merged LineString.

Of course, there are a lot of combinations, but I see no way to reorder the actual twelve parts, to fit together into a valid LineString.

[GIS] How to explode a MultiLineString using ST_Dump() in PostGIS

SELECT a.id, (ST_Dump(a.the_geom)).path path , (ST_Dump(a.the_geom)).geom geom ,ST_GeometryType((ST_Dump(a.the_geom)).geom ) geomtype INTO b FROM a

Create table with id, path , geom and geomtype columns. id point to original geom , path points to where geom was in original geom and geom is geometry.

Now you can add geom2 column to table (type geometry) and create new line with line-offset. After that you can create MULTILINE again using id
(not tested)

ALTER TABLE b add geom2 geometry
UPDATE b SET geom2 = line-offset(geom, 2) -- something like that 


SELECT id, ST_Linemerge(st_collect(geom)) from b group by id -- creates linestring or multiline

SELECT id, ST_MULTI(st_collect(geom)) from b group by id -- creates Multiline

if order is has to same

SELECT b.id, ST_Multi(ST_Collect(b.geom)) FROM SELECT (id, path, geom from b order by id, path ) b GROUP BY b.id

EDIT: ST_Collect should be replaced with st_union or other. St_Multi and st_linemerge do not like GEOMETRYCOLLECTIONS

EDIT : about Additional Details

INSERT INTO complex (name, the_geom) VALUES ( 'Bob', ST_GeomFromEWKT('SRID=4236;MULTILINESTRING((498376.89 651569.6,498372.28 651571.89),(498372.28 651571.89,498371.77 651576.05))') )
LINESTRING can be MULTILINESTRING , it is just a MULTI collection with one LINE

ANSWERS: Question A: im not sure, but here is tested example.

SELECT * INTO complex FROM (
 SELECT  'Harry' as name  , ST_GeomFromEWKT('SRID=4236;MULTILINESTRING((498376.89
    651569.6,498372.28 651571.89))')as the_geom 
    UNION 
 SELECT 'Bob' as name , ST_GeomFromEWKT('SRID=4236;MULTILINESTRING((498376.89
        651569.6,498372.28 651571.89),(498372.28 651571.89,498371.77 651576.05))') as the_geom 
        ) a

now this return 3 rows, Bob has 2 and Harry 1

SELECT *,  ST_AsEWKT(simple_geom)
-- into test
 FROM (
  SELECT
    dumped.*,
    (dumped.geom_dump).geom as simple_geom,
    (dumped.geom_dump).path as path
  FROM (
    SELECT *, ST_Dump(the_geom) AS geom_dump FROM complex
  ) as dumped
) AS simple   

Now we have test table with 3 rows .

SELECT st_asEWKT((st_union(the_geom))) as geom  from test group by name 
OR
SELECT st_asEWKT(st_linemerge(st_union(the_geom))) as geom  from test group by name 

First one returns multi type,  second tries to merge lines. In this case it will return 2 linestrings if it does not succeed it will return multilinestrings

Question B:

SELECT *, ST_Transform(geom,3857) as the_geom_webmercator from xxx

You need to set correct SRID to geoms first. Create them using EWKT with srid (like that example) or using ST_SetSRID(geom, srid) , example

UPDATE xxx set geom = ST_SetSRID(geom)

.. i hope this helps

Best Answer

Related Solutions

[GIS] Cannot st_linemerge a multilinestring because its not properly ordered

[GIS] How to explode a MultiLineString using ST_Dump() in PostGIS

Related Question