(incubator-baremaps) 01/01: Exclude daylight geometries from simplified roadnetwork

[bc...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

bchapuis pushed a commit to branch license-override
in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git

commit a67b9dc2ee777e45ee55af764fa64d6a3a89d082
Author: Bertil Chapuis <bc...@gmail.com>
AuthorDate: Wed Feb 7 10:19:24 2024 +0100

    Exclude daylight geometries from simplified roadnetwork
---
 daylight/layers/highway/prepare.sql | 63 +++++++++++++++++++++++++++++++++++++
 daylight/workflow.js                |  2 +-
 2 files changed, 64 insertions(+), 1 deletion(-)

diff --git a/daylight/layers/highway/prepare.sql b/daylight/layers/highway/prepare.sql
new file mode 100644
index 00000000..6873f05e
--- /dev/null
+++ b/daylight/layers/highway/prepare.sql
@@ -0,0 +1,63 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements.  See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to you under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License.  You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+CREATE MATERIALIZED VIEW osm_highway AS
+WITH
+    -- Filter the linestrings
+    filtered AS (
+        SELECT
+                tags -> 'highway' AS highway,
+                tags -> 'construction' AS construction,
+                geom AS geom
+        FROM osm_linestring
+        WHERE tags ->> 'highway' IN ('motorway', 'motorway_link', 'trunk', 'trunk_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'tertiary', 'tertiary_link', 'unclassified', 'residential', 'construction')
+        AND changeset != 1000000000
+    ),
+    -- Cluster the linestrings by highway type
+    clustered AS (
+        SELECT
+                highway AS highway,
+                construction AS construction,
+                geom as geom,
+                ST_ClusterDBSCAN(geom, 0, 1) OVER (PARTITION BY highway) AS cluster
+        FROM
+            filtered
+    ),
+    -- Merge the linestrings into a single geometry per cluster
+    merged AS (
+        SELECT
+            highway AS highway,
+            construction AS construction,
+            ST_LineMerge(ST_Collect(geom)) AS geom
+        FROM
+            clustered
+        GROUP BY
+            highway, construction, cluster
+    ),
+    -- Explode the merged linestrings into individual linestrings
+    exploded AS (
+        SELECT
+            highway AS highway,
+            construction AS construction,
+            (ST_Dump(geom)).geom AS geom
+        FROM
+            merged
+    )
+SELECT
+    row_number() OVER () AS id,
+    jsonb_build_object('highway', highway, 'construction', construction) AS tags,
+    geom AS geom
+FROM exploded;
+
+CREATE INDEX IF NOT EXISTS osm_highway_geom_index ON osm_highway USING SPGIST (geom);
diff --git a/daylight/workflow.js b/daylight/workflow.js
index aef8e1a9..1efc9ac0 100644
--- a/daylight/workflow.js
+++ b/daylight/workflow.js
@@ -263,7 +263,7 @@ export default {
                 },
                 {
                     "type": "ExecuteSql",
-                    "file": "../basemap/layers/highway/prepare.sql",
+                    "file": "./layers/highway/prepare.sql",
                     "database": config.database,
                 },
                 {