How do I explode a MultiLineString using ST_Dump() in PostGIS?

Question

I want to do what this blog post does: explode a MultiLineString into individual LineStrings. I'm using CartoDB but don't get individual LineStrings as the result of my query. I still get a MultiLineSting. I've tried to simplify it by taking out parts I don't understand (like the COALESCE() part) but still haven't gotten it to work.

My code

SELECT
  COALESCE((simple.trip_id || '.' || simple.path[1]::text)::float, simple.trip_id) as id,
  simple.simple_geom as geom,
  ST_GeometryType(simple.simple_geom) as geom_type,
  ST_AsEWKT(simple.simple_geom) as geom_wkt
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 my_table
  ) as dumped
) AS simple

If anyone could help break down what the SQL above is doing and/or how to tweak it, that'd be great. I'm not sure how the ST_Dump function works.

---

Additional Details

Following the steps in the blog post,

1. I create an empty table called complex in CartoDB, a Postgresql table (via UI, not SQL command).

2. I create two LineStrings. I first tried inserting with SQL, like the example, but get an error

   INSERT INTO complex (name, the_geom)
   VALUES ( 
     'Bob',
     ST_GeomFromEWKT('MULTILINESTRING((498376.89 651569.6,498372.28 651571.89),(498372.28 651571.89,498371.77 651576.05))')
   )
   
   >>>Error: "Geometry SRID (0) does not match column SRID (4326)"
   

3. Instead, I draw a MultiLine with 3 points (2 line segments). Then, I draw another Line with 2 points (1 line segment). When I look at the_geom, I see both lines are type MultiLineString. My table is

   SELECT * FROM complex
   
   cartodb_id | name | the_geom 
   -----------+------+----------
   1 | "Bob" | {"type":"MultiLineString","coordinates":[[[-39.90234375,44.46515101],[-25.83984375,44.71551373],[-25.83984375,35.17380832]]]}
   2 | "Harry" | {"type":"MultiLineString","coordinates":[[[-39.90234375,44.46515101],[-25.83984375,44.71551373]]]}
   

4. I then run the following and get,

   SELECT *, ST_GeometryType(the_geom) as geom_type, ST_AsEWKT(the_geom) as geom_wkt from complex
   
   cartodb_id | name | the_geom | geom_wkt | the_geom_webmercartor 
   -----------+----------+----------+-----------------------
   1 | "Bob" | {"type":"MultiLineString","coordinates":[[[-39.90234375,44.46515101],[-25.83984375,44.71551373],[-25.83984375,35.17380832]]]} | SRID=4326;MULTILINESTRING((-39.90234375 44.46515101,-25.83984375 44.71551373,-25.83984375 35.17380832)) | "Bob" | {"type":"MultiLineString","coordinates":[[[-39.90234375,44.46515101],[-25.83984375,44.71551373],[-25.83984375,35.17380832]]]}
   2 | "Harry" | {"type":"MultiLineString","coordinates":[[[-39.90234375,44.46515101],[-25.83984375,44.71551373]]]} | SRID=4326;MULTILINESTRING((-39.90234375 44.46515101,-25.83984375 44.71551373)) | {"type":"MultiLineString","coordinates":[[[-39.90234375,44.46515101],[-25.83984375,44.71551373]]]}
   

The blog post indicated I should get one geom_type ST_MultiLineString and one ST_LineString. I am only getting MultiLineStrings.

5. Then I run the ST_Dump function, but I only get a table with 2 rows. Supposedly they are Linestrings, but id=1.1 has 3 points in the_geom. I should be getting a table with 3 rows (with all LineStrings).

   SELECT
     COALESCE((simple.cartodb_id || '.' || simple.path[1]::text)::float, simple.cartodb_id) as id,
     simple.name,
     simple.simple_geom as geom,
     ST_GeometryType(simple.simple_geom) as geom_type,
     ST_AsEWKT(simple.simple_geom) as geom_wkt
   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
   
    id | name | the_geom | geom_type | geom_wkt 
   ----+------+----------+-----------+----------
   1.1 | Bob | LINESTRING(-39.90234375 44.46515101,-25.83984375 44.71551373,-25.83984375 35.17380832) | ST_LineString | SRID=4326;LINESTRING(-39.90234375 44.46515101,-25.83984375 44.71551373,-25.83984375 35.17380832) 
   2.1 | Harry | LINESTRING(-39.90234375 44.46515101,-25.83984375 44.71551373) | ST_LineString | SRID=4326;LINESTRING(-39.90234375 44.46515101,-25.83984375 44.71551373)
   

Questions

A. Why am I not getting three rows, each with a LineString made of 2 points? How do I correct my SQL?

B. How do I also get the_geom_webmercator in my results table so it shows up on the map?

Answer 1

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 

Or

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

1. 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))') )

2. 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