1

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.

  1. 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?

  • The coalesce part simply creates a new id by combining the initial id with a path id (which will be unique for each linestring form the dumped multipolygon). The query above has one extra subquery, which doesn't aid understanding, but should still work correctly. What issue are you actually seeing in CartoDB. – John Powell Jun 8 '15 at 13:03
  • Thanks for your help John and simplexio. I've updated my post with step-by-step details to show the trouble I'm having with running the ST_Dump function in CartoDB. Once I figure this part out, perhaps then I'll deal with how to offset overlapping line segments in another question/post. – Travis Feast Jun 9 '15 at 14:13
  • In step 5, I can get the_geom_webmercator as an output by adding (to the top SELECT query): ST_Transform((simple.geom_dump).geom,3857) as the_geom_webmercator, ... But, am I using the wrong geometry? ST_Transform((simple.simple_geom).geom,3857) produces an error: "column notation .geom applied to type geometry, which is not a composite type". Not sure what is happening here... – Travis Feast Jun 28 '15 at 19:51
  • CartoDB generates any line as a MultiLineString. This is probably that comes from postgres but I don't know the details. In step 3 you're making an incorrect assumption. You can set the SRID for your query this way: INSERT INTO complex (name, the_geom) VALUES ( 'Bob', ST_SetSRID(ST_GeomFromEWKT('MULTILINESTRING((498376.89 651569.6,498372.28 651571.89),(498372.28 651571.89,498371.77 651576.05))'), 4326) ), but a line with 3 points is not a MultiLineString in the sense that the geometry is formed by 3 lines, it's just a single line with 3 vertices. – iriberri Jul 14 '15 at 10:07
2
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

  • Thanks @simplexio for your answer. Currently, I'm having trouble applying the ST_Dump function (I've updated my question to include more details). I took a look at your method for using ST_Dump with creating two tables and using path, but I can't quite follow what you did. If you could provide additional clarity to your description and/or respond to questions A & B above, that would be helpful. – Travis Feast Jun 10 '15 at 14:54
  • SELECT ST_Dump(geom) returns rows path and geoms. SELECT id, ST_Dump(geom) from x where id = 1 returns – simplexio Jun 11 '15 at 6:44

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.