I want to do what this blog post does: explode a multilinestringMultiLineString into individual linestringsLineStrings. I'm using CartoDB but don't get individual linestringsLineStrings as the result of my query. I still get a multilinestingMultiLineSting. 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.
Ultimately~~Ultimately, I want to explode my multilinestringsMultiLineStrings simply to run an intersection function on it so that I can use line-offset in CartoCSSCartoCSS to offset the segments of line features where they overlap. I also looked at this as a resource.~~
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,
I create an empty table called complex in CartoDB (via UI, not SQL command)
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)"
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]]]}
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.
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?
