basemap/layers/highway/create.sql

-- 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.
-- Zoom levels 20-13
CREATE
    OR REPLACE VIEW osm_highway AS SELECT
        id,
        tags,
        geom
    FROM
        osm_way
    WHERE
        osm_way.geom IS NOT NULL
        AND tags ? 'highway';

CREATE
    OR REPLACE VIEW osm_highway_z20 AS SELECT
        id,
        tags,
        geom
    FROM
        osm_highway;

CREATE
    OR REPLACE VIEW osm_highway_z19 AS SELECT
        id,
        tags,
        geom
    FROM
        osm_highway;

CREATE
    OR REPLACE VIEW osm_highway_z18 AS SELECT
        id,
        tags,
        geom
    FROM
        osm_highway;

CREATE
    OR REPLACE VIEW osm_highway_z17 AS SELECT
        id,
        tags,
        geom
    FROM
        osm_highway;

CREATE
    OR REPLACE VIEW osm_highway_z16 AS SELECT
        id,
        tags,
        geom
    FROM
        osm_highway;

CREATE
    OR REPLACE VIEW osm_highway_z15 AS SELECT
        id,
        tags,
        geom
    FROM
        osm_highway;

CREATE
    OR REPLACE VIEW osm_highway_z14 AS SELECT
        id,
        tags,
        geom
    FROM
        osm_highway;

CREATE
    OR REPLACE VIEW osm_highway_z13 AS SELECT
        id,
        tags,
        geom
    FROM
        osm_highway
    WHERE
        tags ->> 'highway' IN(
            'motorway',
            'motorway_link',
            'trunk',
            'trunk_link',
            'primary',
            'primary_link',
            'secondary',
            'secondary_link',
            'tertiary',
            'tertiary_link',
            'unclassified',
            'residential'
        );

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_filtered CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_filtered AS SELECT
        tags -> 'highway' AS highway,
        geom AS geom
    FROM
        osm_highway
    WHERE
        tags ->> 'highway' IN(
            'motorway',
            'motorway_link',
            'trunk',
            'trunk_link',
            'primary',
            'primary_link',
            'secondary',
            'secondary_link',
            'tertiary',
            'tertiary_link',
            'unclassified',
            'residential'
        );

DROP
    INDEX IF EXISTS osm_highway_filtered_geom;

CREATE
    INDEX IF NOT EXISTS osm_highway_filtered_geom ON
    osm_highway_filtered
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_clustered CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_clustered AS SELECT
        highway AS highway,
        geom AS geom,
        ST_ClusterDBSCAN(
            geom,
            0,
            1
        ) OVER(
            PARTITION BY highway
        ) AS cluster
    FROM
        osm_highway_filtered;

DROP
    INDEX IF EXISTS osm_highway_clustered_geom;

CREATE
    INDEX IF NOT EXISTS osm_highway_clustered_geom ON
    osm_highway_clustered
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_simplified CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_simplified AS WITH merged AS(
        SELECT
            highway AS highway,
            ST_LineMerge(
                ST_Collect(geom)
            ) AS geom
        FROM
            osm_highway_clustered
        GROUP BY
            highway,
            cluster
    ),
    exploded AS(
        SELECT
            highway AS highway,
            (
                ST_Dump(geom)
            ).geom AS geom
        FROM
            merged
    ) SELECT
        ROW_NUMBER() OVER() AS id,
        jsonb_build_object(
            'highway',
            highway
        ) AS tags,
        geom AS geom
    FROM
        exploded;

DROP
    INDEX IF EXISTS osm_highway_simplified_geom;

CREATE
    INDEX IF NOT EXISTS osm_highway_simplified_geom ON
    osm_highway_simplified
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z12 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z12 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 12 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 12 )), 2 )
        );

DROP
    INDEX IF EXISTS osm_highway_z12_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z12_geom_idx ON
    osm_highway_z12
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z11 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z11 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 11 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 11 )), 2 )
        )
        AND tags ->> 'highway' IN(
            'motorway',
            'motorway_link',
            'trunk',
            'trunk_link',
            'primary',
            'primary_link',
            'secondary',
            'secondary_link',
            'tertiary',
            'tertiary_link',
            'unclassified',
            'residential'
        );

DROP
    INDEX IF EXISTS osm_highway_z11_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z11_geom_idx ON
    osm_highway_z11
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z10 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z10 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 10 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 10 )), 2 )
        )
        AND tags ->> 'highway' IN(
            'motorway',
            'motorway_link',
            'trunk',
            'trunk_link',
            'primary',
            'primary_link',
            'secondary',
            'secondary_link',
            'tertiary',
            'tertiary_link'
        );

DROP
    INDEX IF EXISTS osm_highway_z10_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z10_geom_idx ON
    osm_highway_z10
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z9 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z9 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 9 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 9 )), 2 )
        )
        AND tags ->> 'highway' IN(
            'motorway',
            'motorway_link',
            'trunk',
            'trunk_link',
            'primary',
            'primary_link',
            'secondary',
            'secondary_link'
        );

DROP
    INDEX IF EXISTS osm_highway_z9_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z9_geom_idx ON
    osm_highway_z9
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z8 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z8 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 8 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 8 )), 2 )
        )
        AND tags ->> 'highway' IN(
            'motorway',
            'trunk',
            'primary'
        );

DROP
    INDEX IF EXISTS osm_highway_z8_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z8_geom_idx ON
    osm_highway_z8
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z7 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z7 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 7 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 7 )), 2 )
        )
        AND tags ->> 'highway' IN(
            'motorway',
            'trunk',
            'primary'
        );

DROP
    INDEX IF EXISTS osm_highway_z7_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z7_geom_idx ON
    osm_highway_z7
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z6 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z6 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 6 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 6 )), 2 )
        )
        AND tags ->> 'highway' IN(
            'motorway',
            'trunk',
            'primary'
        );

DROP
    INDEX IF EXISTS osm_highway_z6_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z6_geom_idx ON
    osm_highway_z6
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z5 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z5 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 5 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 5 )), 2 )
        )
        AND tags ->> 'highway' IN('motorway');

DROP
    INDEX IF EXISTS osm_highway_z5_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z5_geom_idx ON
    osm_highway_z5
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z4 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z4 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 4 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 4 )), 2 )
        )
        AND tags ->> 'highway' IN('motorway');

DROP
    INDEX IF EXISTS osm_highway_z4_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z4_geom_idx ON
    osm_highway_z4
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z3 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z3 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 3 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 3 )), 2 )
        )
        AND tags ->> 'highway' IN('motorway');

DROP
    INDEX IF EXISTS osm_highway_z3_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z3_geom_idx ON
    osm_highway_z3
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z2 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z2 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 2 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 2 )), 2 )
        )
        AND tags ->> 'highway' IN('motorway');

DROP
    INDEX IF EXISTS osm_highway_z2_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z2_geom_idx ON
    osm_highway_z2
        USING GIST(geom);

DROP
    MATERIALIZED VIEW IF EXISTS osm_highway_z1 CASCADE;

CREATE
    MATERIALIZED VIEW IF NOT EXISTS osm_highway_z1 AS SELECT
        id,
        tags,
        st_simplifypreservetopology(
            geom,
            78270 / POWER( 2, 1 )
        ) AS geom
    FROM
        osm_highway_simplified
    WHERE
        geom IS NOT NULL
        AND NOT ST_IsEmpty(geom)
        AND(
            st_area(
                st_envelope(geom)
            )> POWER(( 78270 / POWER( 2, 1 )), 2 )
        )
        AND tags ->> 'highway' IN('motorway');

DROP
    INDEX IF EXISTS osm_highway_z1_geom_idx;

CREATE
    INDEX IF NOT EXISTS osm_highway_z1_geom_idx ON
    osm_highway_z1
        USING GIST(geom);