How to polygonize MultiLineString?

[nshiab]

Hi!

I am trying to use the extension to polygonize the features in this geojson file:
https://earthquake.usgs.gov/product/shakemap/official20110311054624120_30/atlas/1594161746661/download/cont_mmi.json

The problem is that some features are multi-lines. I tried to use ST_LineMerge, but I think it doesn't work because some multi-lines are made of multiple closed lines.

Here's the code I tried to run on DuckDB Shell:

LOAD httpfs;
LOAD spatial;
CREATE TABLE earthquake AS SELECT * FROM ST_Read('https://earthquake.usgs.gov/product/shakemap/official20110311054624120_30/atlas/1594161746661/download/cont_mmi.json');
CREATE OR REPLACE TABLE earthquake AS SELECT * FROM earthquake WHERE ST_IsClosed(geom) = TRUE;
UPDATE earthquake SET geom = ST_MakePolygon(ST_LineMerge(geom));

But it gives me this error message:
Invalid Input Error: ST_MakePolygon only accepts LINESTRING geometries

Is there a way to break down the multi-lines into multiple rows and then run ST_MakePolygon on all rows?

Or is there another way to polygonize each feature?

Thank you. 🙏

[RaczeQ]

Yes, it is possible to break down multi-lines ;)
Just be careful with linestrings that aren't closed.

WITH raw_data AS (
    SELECT *
    FROM ST_Read('https://earthquake.usgs.gov/product/shakemap/official20110311054624120_30/atlas/1594161746661/download/cont_mmi.json')
),
unnested_linestrings AS (
    SELECT value, UNNEST(ST_Dump(geom), recursive := true)
    FROM raw_data
)
SELECT value, ST_MakePolygon(geom) geometry
FROM unnested_linestrings
WHERE ST_Equals(ST_StartPoint(geom), ST_EndPoint(geom))

┌────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ value  │                                                  geometry                                                   │
│ double │                                                  geometry                                                   │
├────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│    4.5 │ POLYGON ((143.308855 33.256795, 142.943232 33.230508, 142.278463 33.301919, 140.317394 33.806024, 139.386…  │
│    5.0 │ POLYGON ((143.175901 33.75787, 142.943232 33.742985, 142.411417 33.801375, 140.516825 34.281204, 139.3867…  │
│    5.0 │ POLYGON ((139.106436 36.918222, 139.253763 36.992999, 139.320535 37.117778, 139.253763 37.193844, 139.154…  │
│    5.0 │ POLYGON ((138.458037 36.718667, 138.45604 36.767987, 138.401128 36.718667, 138.458037 36.718667))           │
│    5.5 │ POLYGON ((143.076186 34.155695, 142.810278 34.160893, 142.311701 34.238224, 141.248071 34.538499, 140.749…  │
│    5.5 │ POLYGON ((140.52297 39.712, 140.584024 39.811778, 140.550063 39.89456, 140.383871 40.028791, 140.317394 4…  │
│    6.0 │ POLYGON ((142.976471 34.522927, 142.444655 34.595768, 141.347786 34.911581, 140.882448 35.014967, 140.417…  │
│    6.5 │ POLYGON ((143.109424 34.885153, 142.943232 34.868587, 142.544371 34.928726, 141.314548 35.2979, 140.94892…  │
│    7.0 │ POLYGON ((143.042948 35.188411, 142.943232 35.179946, 142.644086 35.226629, 140.815971 35.834881, 140.550…  │
│    7.5 │ POLYGON ((143.142663 35.479221, 142.976471 35.456489, 142.743801 35.488472, 141.148355 36.054676, 140.948…  │
│    7.5 │ POLYGON ((140.383871 37.112449, 140.284156 37.114907, 140.208322 37.217556, 140.215459 37.317333, 140.284…  │
│    7.5 │ POLYGON ((140.18444 36.441507, 139.989383 36.55237, 139.97774 36.618889, 140.018248 36.721327, 140.151202…  │
│    8.0 │ POLYGON ((143.076186 35.740926, 142.843517 35.740875, 142.710563 35.783601, 142.295063 35.953704, 142.050…  │
├────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ 13 rows                                                                                                    2 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

[nshiab]

Thank you, @RaczeQ! It's exactly what I tried to do!

We have an open-source JS/TS library (simple-data-analysis) that we use for our journalistic projects. Under the hood, it uses DuckDB and the spatial extension. I've updated it, thanks to your answer.

import { SimpleNodeDB } from "simple-data-analysis";

const sdb = new SimpleNodeDB({ spatial: true });

await sdb.loadGeoData(
  "earthquake",
  "https://earthquake.usgs.gov/product/shakemap/official20110311054624120_30/atlas/1594161746661/download/cont_mmi.json"
);
await sdb.unnestGeo("earthquake", "geom");
await sdb.isClosedGeo("earthquake", "geom", "closed");
await sdb.filter("earthquake", `closed = true`);
await sdb.linesToPolygons("earthquake", "geom");
await sdb.writeGeoData("earthquake", "./earthquake.geojson");