This is an automated email from the ASF dual-hosted git repository. bchapuis pushed a commit to branch basemaps-schema in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
commit a5634060513d6809ac179c45ba9dee139264a6cf Author: Bertil Chapuis <[email protected]> AuthorDate: Thu Dec 19 22:34:35 2024 +0100 Add create sql script --- basemap/create.js | 60 +++ basemap/import.js | 804 +++++++++++++++++------------------ basemap/layers/aerialway/create.sql | 2 + basemap/layers/aeroway/create.sql | 2 + basemap/layers/amenity/create.sql | 2 + basemap/layers/attraction/create.sql | 2 + basemap/layers/barrier/create.sql | 2 + basemap/layers/boundary/create.sql | 2 + basemap/layers/building/create.sql | 28 ++ basemap/layers/header/create.sql | 8 + basemap/layers/highway/create.sql | 60 +++ basemap/layers/landuse/create.sql | 45 ++ basemap/layers/leisure/create.sql | 61 +++ basemap/layers/linestring/create.sql | 6 + basemap/layers/man_made/create.sql | 1 + basemap/layers/member/create.sql | 8 + basemap/layers/natural/create.sql | 65 +++ basemap/layers/node/create.sql | 12 + basemap/layers/ocean/create.sql | 19 + basemap/layers/point/clean.sql | 25 +- basemap/layers/point/create.sql | 90 ++++ basemap/layers/polygon/create.sql | 16 + basemap/layers/power/create.sql | 2 + basemap/layers/railway/create.sql | 8 + basemap/layers/relation/create.sql | 13 + basemap/layers/route/create.sql | 29 ++ basemap/layers/tourism/create.sql | 2 + basemap/layers/waterway/create.sql | 58 +++ basemap/layers/way/create.sql | 11 + 29 files changed, 1017 insertions(+), 426 deletions(-) diff --git a/basemap/create.js b/basemap/create.js new file mode 100644 index 000000000..1213cd382 --- /dev/null +++ b/basemap/create.js @@ -0,0 +1,60 @@ +/** + 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. + **/ +import config from "./config.js"; + +export default { + "steps": [ + { + "id": "openstreetmap-water-polygons", + "needs": [], + "tasks": [ + "layers/header/create.sql", + "layers/node/create.sql", + "layers/way/create.sql", + "layers/relation/create.sql", + "layers/member/create.sql", + "layers/linestring/create.sql", + "layers/polygon/create.sql", + "layers/aerialway/create.sql", + "layers/aeroway/create.sql", + "layers/amenity/create.sql", + "layers/attraction/create.sql", + "layers/barrier/create.sql", + "layers/boundary/create.sql", + "layers/building/create.sql", + "layers/highway/create.sql", + "layers/landuse/create.sql", + "layers/leisure/create.sql", + "layers/man_made/create.sql", + "layers/natural/create.sql", + //"layers/ocean/create.sql", + "layers/point/create.sql", + "layers/power/create.sql", + "layers/railway/create.sql", + "layers/route/create.sql", + "layers/tourism/create.sql", + "layers/waterway/create.sql", + ].map(file => { + return { + "type": "ExecuteSql", + "file": file, + "database": config.database, + } + }) + }, + ] +} diff --git a/basemap/import.js b/basemap/import.js index f5490ae73..bcc186df4 100644 --- a/basemap/import.js +++ b/basemap/import.js @@ -34,78 +34,78 @@ export default { }, ] }, - { - "id": "openstreetmap-water-polygons", - "needs": [], - "tasks": [ - { - "type": "DownloadUrl", - "source": "https://osmdata.openstreetmap.de/download/water-polygons-split-3857.zip";, - "target": "data/water-polygons-split-3857.zip" - }, - { - "type": "DecompressFile", - "source": "data/water-polygons-split-3857.zip", - "target": "data", - "compression": "zip" - }, - { - "type": "ImportShapefile", - "file": "data/water-polygons-split-3857/water_polygons.shp", - "database": config.database, - "fileSrid": 3857, - "databaseSrid": 3857 - }, - ] - }, - { - "id": "openstreetmap-simplified-water-polygons", - "needs": [], - "tasks": [ - { - "type": "DownloadUrl", - "source": "https://osmdata.openstreetmap.de/download/simplified-water-polygons-split-3857.zip";, - "target": "data/simplified-water-polygons-split-3857.zip" - }, - { - "type": "DecompressFile", - "source": "data/simplified-water-polygons-split-3857.zip", - "target": "data", - "compression": "zip" - }, - { - "type": "ImportShapefile", - "file": "data/simplified-water-polygons-split-3857/simplified_water_polygons.shp", - "database": config.database, - "fileSrid": 3857, - "databaseSrid": 3857 - }, - ] - }, - { - "id": "openstreetmap-ocean", - "needs": [ - "openstreetmap-water-polygons", - "openstreetmap-simplified-water-polygons", - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/ocean/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/ocean/prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/ocean/index.sql", - "database": config.database, - }, - ] - }, + // { + // "id": "openstreetmap-water-polygons", + // "needs": [], + // "tasks": [ + // { + // "type": "DownloadUrl", + // "source": "https://osmdata.openstreetmap.de/download/water-polygons-split-3857.zip";, + // "target": "data/water-polygons-split-3857.zip" + // }, + // { + // "type": "DecompressFile", + // "source": "data/water-polygons-split-3857.zip", + // "target": "data", + // "compression": "zip" + // }, + // { + // "type": "ImportShapefile", + // "file": "data/water-polygons-split-3857/water_polygons.shp", + // "database": config.database, + // "fileSrid": 3857, + // "databaseSrid": 3857 + // }, + // ] + // }, + // { + // "id": "openstreetmap-simplified-water-polygons", + // "needs": [], + // "tasks": [ + // { + // "type": "DownloadUrl", + // "source": "https://osmdata.openstreetmap.de/download/simplified-water-polygons-split-3857.zip";, + // "target": "data/simplified-water-polygons-split-3857.zip" + // }, + // { + // "type": "DecompressFile", + // "source": "data/simplified-water-polygons-split-3857.zip", + // "target": "data", + // "compression": "zip" + // }, + // { + // "type": "ImportShapefile", + // "file": "data/simplified-water-polygons-split-3857/simplified_water_polygons.shp", + // "database": config.database, + // "fileSrid": 3857, + // "databaseSrid": 3857 + // }, + // ] + // }, + // { + // "id": "openstreetmap-ocean", + // "needs": [ + // "openstreetmap-water-polygons", + // "openstreetmap-simplified-water-polygons", + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/ocean/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/ocean/prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/ocean/index.sql", + // "database": config.database, + // }, + // ] + // }, { "id": "openstreetmap-data", "needs": [], @@ -130,335 +130,335 @@ export default { }, ] }, - { - "id": "openstreetmap-nodes", - "needs": [ - "openstreetmap-data" - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "queries/osm_nodes.sql", - "database": config.database, - "parallel": true, - }, - ] - }, - { - "id": "openstreetmap-ways", - "needs": [ - "openstreetmap-data" - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "queries/osm_ways.sql", - "database": config.database, - "parallel": true, - }, - ] - }, - { - "id": "openstreetmap-relations", - "needs": [ - "openstreetmap-data" - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "queries/osm_relations.sql", - "database": config.database, - "parallel": true, - }, - ] - }, - { - "id": "openstreetmap-member", - "needs": [ - "openstreetmap-data" - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/member/prepare.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-point", - "needs": [ - "openstreetmap-nodes" - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/point/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/point/simplify.sql", - "database": config.database, - "parallel": true, - }, - { - "type": "ExecuteSql", - "file": "layers/point/index.sql", - "database": config.database, - "parallel": true, - }, - ] - }, - { - "id": "openstreetmap-linestring", - "needs": [ - "openstreetmap-member" - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/linestring/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/linestring/prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/linestring/index.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-polygon", - "needs": [ - "openstreetmap-member", - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/polygon/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/polygon/prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/polygon/index.sql", - "database": config.database, - }, - ] - }, - { - "id": "openstreetmap-highway", - "needs": [ - "openstreetmap-linestring" - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/highway/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/highway/prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/highway/simplify.sql", - "database": config.database, - "parallel": true, - }, - { - "type": "ExecuteSql", - "file": "layers/highway/index.sql", - "database": config.database, - "parallel": true, - }, - ] - }, - { - "id": "openstreetmap-railway", - "needs": ["openstreetmap-linestring"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/railway/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/railway/prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/railway/simplify.sql", - "database": config.database, - "parallel": true, - }, - { - "type": "ExecuteSql", - "file": "layers/railway/index.sql", - "database": config.database, - "parallel": true, - }, - ] - }, - { - "id": "openstreetmap-route", - "needs": ["openstreetmap-linestring"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/route/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/route/prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/route/simplify.sql", - "database": config.database, - "parallel": true, - }, - { - "type": "ExecuteSql", - "file": "layers/route/index.sql", - "database": config.database, - "parallel": true, - }, - ] - }, - { - "id": "openstreetmap-waterway", - "needs": [ - "openstreetmap-linestring" - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/waterway/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/waterway/prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/waterway/simplify.sql", - "database": config.database, - "parallel": true, - }, - { - "type": "ExecuteSql", - "file": "layers/waterway/index.sql", - "database": config.database, - "parallel": true - }, - ] - }, - { - "id": "openstreetmap-natural", - "needs": ["openstreetmap-polygon"], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/natural/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/natural/prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/natural/simplify.sql", - "database": config.database, - "parallel": true, - }, - { - "type": "ExecuteSql", - "file": "layers/natural/index.sql", - "database": config.database, - "parallel": true - }, - ] - }, - { - "id": "openstreetmap-landuse", - "needs": [ - "openstreetmap-polygon" - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/landuse/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/landuse/prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/landuse/simplify.sql", - "database": config.database, - "parallel": true, - }, - { - "type": "ExecuteSql", - "file": "layers/landuse/index.sql", - "database": config.database, - "parallel": true - }, - ] - }, - { - "id": "openstreetmap-leisure", - "needs": [ - "openstreetmap-polygon" - ], - "tasks": [ - { - "type": "ExecuteSql", - "file": "layers/leisure/clean.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/leisure/prepare.sql", - "database": config.database, - }, - { - "type": "ExecuteSql", - "file": "layers/leisure/simplify.sql", - "database": config.database, - "parallel": true, - }, - { - "type": "ExecuteSql", - "file": "layers/leisure/index.sql", - "database": config.database, - "parallel": true - }, - ] - }, + // { + // "id": "openstreetmap-nodes", + // "needs": [ + // "openstreetmap-data" + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_nodes.sql", + // "database": config.database, + // "parallel": true, + // }, + // ] + // }, + // { + // "id": "openstreetmap-ways", + // "needs": [ + // "openstreetmap-data" + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_ways.sql", + // "database": config.database, + // "parallel": true, + // }, + // ] + // }, + // { + // "id": "openstreetmap-relations", + // "needs": [ + // "openstreetmap-data" + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "queries/osm_relations.sql", + // "database": config.database, + // "parallel": true, + // }, + // ] + // }, + // { + // "id": "openstreetmap-member", + // "needs": [ + // "openstreetmap-data" + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/member/prepare.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-point", + // "needs": [ + // "openstreetmap-nodes" + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/point/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/point/simplify.sql", + // "database": config.database, + // "parallel": true, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/point/index.sql", + // "database": config.database, + // "parallel": true, + // }, + // ] + // }, + // { + // "id": "openstreetmap-linestring", + // "needs": [ + // "openstreetmap-member" + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/linestring/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/linestring/prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/linestring/index.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-polygon", + // "needs": [ + // "openstreetmap-member", + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/polygon/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/polygon/prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/polygon/index.sql", + // "database": config.database, + // }, + // ] + // }, + // { + // "id": "openstreetmap-highway", + // "needs": [ + // "openstreetmap-linestring" + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/highway/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/highway/prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/highway/simplify.sql", + // "database": config.database, + // "parallel": true, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/highway/index.sql", + // "database": config.database, + // "parallel": true, + // }, + // ] + // }, + // { + // "id": "openstreetmap-railway", + // "needs": ["openstreetmap-linestring"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/railway/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/railway/prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/railway/simplify.sql", + // "database": config.database, + // "parallel": true, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/railway/index.sql", + // "database": config.database, + // "parallel": true, + // }, + // ] + // }, + // { + // "id": "openstreetmap-route", + // "needs": ["openstreetmap-linestring"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/route/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/route/prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/route/simplify.sql", + // "database": config.database, + // "parallel": true, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/route/index.sql", + // "database": config.database, + // "parallel": true, + // }, + // ] + // }, + // { + // "id": "openstreetmap-waterway", + // "needs": [ + // "openstreetmap-linestring" + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/waterway/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/waterway/prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/waterway/simplify.sql", + // "database": config.database, + // "parallel": true, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/waterway/index.sql", + // "database": config.database, + // "parallel": true + // }, + // ] + // }, + // { + // "id": "openstreetmap-natural", + // "needs": ["openstreetmap-polygon"], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/natural/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/natural/prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/natural/simplify.sql", + // "database": config.database, + // "parallel": true, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/natural/index.sql", + // "database": config.database, + // "parallel": true + // }, + // ] + // }, + // { + // "id": "openstreetmap-landuse", + // "needs": [ + // "openstreetmap-polygon" + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/landuse/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/landuse/prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/landuse/simplify.sql", + // "database": config.database, + // "parallel": true, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/landuse/index.sql", + // "database": config.database, + // "parallel": true + // }, + // ] + // }, + // { + // "id": "openstreetmap-leisure", + // "needs": [ + // "openstreetmap-polygon" + // ], + // "tasks": [ + // { + // "type": "ExecuteSql", + // "file": "layers/leisure/clean.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/leisure/prepare.sql", + // "database": config.database, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/leisure/simplify.sql", + // "database": config.database, + // "parallel": true, + // }, + // { + // "type": "ExecuteSql", + // "file": "layers/leisure/index.sql", + // "database": config.database, + // "parallel": true + // }, + // ] + // }, ] } diff --git a/basemap/layers/aerialway/create.sql b/basemap/layers/aerialway/create.sql new file mode 100644 index 000000000..6e63c5bbb --- /dev/null +++ b/basemap/layers/aerialway/create.sql @@ -0,0 +1,2 @@ +CREATE VIEW aerialway AS +SELECT id, tags, geom FROM osm_ways WHERE tags ? 'aerialway'; \ No newline at end of file diff --git a/basemap/layers/aeroway/create.sql b/basemap/layers/aeroway/create.sql new file mode 100644 index 000000000..51e156e6b --- /dev/null +++ b/basemap/layers/aeroway/create.sql @@ -0,0 +1,2 @@ +CREATE VIEW aeroway AS +SELECT id, tags, geom FROM osm_ways WHERE tags ? 'aeroway' \ No newline at end of file diff --git a/basemap/layers/amenity/create.sql b/basemap/layers/amenity/create.sql new file mode 100644 index 000000000..653042d82 --- /dev/null +++ b/basemap/layers/amenity/create.sql @@ -0,0 +1,2 @@ +CREATE VIEW amenity AS +SELECT id, tags, geom FROM osm_ways WHERE tags ? 'amenity'; \ No newline at end of file diff --git a/basemap/layers/attraction/create.sql b/basemap/layers/attraction/create.sql new file mode 100644 index 000000000..d2eaee111 --- /dev/null +++ b/basemap/layers/attraction/create.sql @@ -0,0 +1,2 @@ +CREATE VIEW attraction AS +SELECT id, tags, geom FROM osm_ways WHERE tags ? 'attraction'; \ No newline at end of file diff --git a/basemap/layers/barrier/create.sql b/basemap/layers/barrier/create.sql new file mode 100644 index 000000000..aaef0318a --- /dev/null +++ b/basemap/layers/barrier/create.sql @@ -0,0 +1,2 @@ +CREATE VIEW barrier AS +SELECT id, tags, geom FROM osm_ways WHERE tags ? 'barrier'; \ No newline at end of file diff --git a/basemap/layers/boundary/create.sql b/basemap/layers/boundary/create.sql new file mode 100644 index 000000000..ee651eca6 --- /dev/null +++ b/basemap/layers/boundary/create.sql @@ -0,0 +1,2 @@ +CREATE VIEW boundary AS +SELECT id, tags, geom FROM osm_ways WHERE tags ? 'boundary'; \ No newline at end of file diff --git a/basemap/layers/building/create.sql b/basemap/layers/building/create.sql new file mode 100644 index 000000000..8ea3159e7 --- /dev/null +++ b/basemap/layers/building/create.sql @@ -0,0 +1,28 @@ +CREATE VIEW building AS +SELECT + id, + tags + || jsonb_build_object('extrusion:base', + CASE + WHEN tags ? 'min_height' + THEN convert_to_number(tags ->> 'min_height', 0) + WHEN tags ? 'building:min_height' + THEN convert_to_number(tags ->> 'building:min_height', 0) + WHEN tags ? 'building:min_level' + THEN convert_to_number(tags ->> 'building:min_level', 0) * 3 + ELSE 0 + END) + || jsonb_build_object('extrusion:height', + CASE + WHEN tags ? 'height' + THEN convert_to_number(tags ->> 'height', 6) + WHEN tags ? 'building:height' + THEN convert_to_number(tags ->> 'building:height', 6) + WHEN tags ? 'building:levels' + THEN convert_to_number(tags ->> 'building:levels', 2) * 3 + ELSE 6 + END) as tags, + geom +FROM osm_polygon +WHERE (tags ? 'building' OR tags ? 'building:part') + AND ((NOT tags ? 'layer') OR convert_to_number(tags ->> 'layer', 0) >= 0) \ No newline at end of file diff --git a/basemap/layers/header/create.sql b/basemap/layers/header/create.sql new file mode 100644 index 000000000..73cab3580 --- /dev/null +++ b/basemap/layers/header/create.sql @@ -0,0 +1,8 @@ +CREATE TABLE IF NOT EXISTS osm_headers +( + replication_sequence_number bigint PRIMARY KEY, + replication_timestamp timestamp without time zone, + replication_url text, + source text, + writing_program text +); \ No newline at end of file diff --git a/basemap/layers/highway/create.sql b/basemap/layers/highway/create.sql new file mode 100644 index 000000000..f4bda38a2 --- /dev/null +++ b/basemap/layers/highway/create.sql @@ -0,0 +1,60 @@ +-- 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') + ), + -- 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; diff --git a/basemap/layers/landuse/create.sql b/basemap/layers/landuse/create.sql new file mode 100644 index 000000000..90b018745 --- /dev/null +++ b/basemap/layers/landuse/create.sql @@ -0,0 +1,45 @@ + + + +CREATE MATERIALIZED VIEW osm_landuse_filtered AS +SELECT + tags -> 'landuse' AS landuse, + st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom +FROM osm_polygon +WHERE geom IS NOT NULL + AND st_area(geom) > 78270 / power(2, 12) * 100 + AND tags ->> 'landuse' IN ('commercial', 'construction', 'industrial', 'residential', 'retail', 'farmland', 'forest', 'meadow', 'greenhouse_horticulture', 'meadow', 'orchard', 'plant_nursery','vineyard', 'basin', 'salt_pond', 'brownfield', 'cemetery', 'grass', 'greenfield', 'landfill', 'military', 'quarry', 'railway'); + +CREATE MATERIALIZED VIEW osm_landuse_clustered AS +SELECT + landuse, + geom, + st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY landuse) AS cluster +FROM osm_landuse_filtered +WHERE geom IS NOT NULL; + +CREATE MATERIALIZED VIEW osm_landuse_grouped AS +SELECT + landuse, + st_collect(geom) AS geom +FROM osm_landuse_clustered +GROUP BY landuse, cluster; + +CREATE MATERIALIZED VIEW osm_landuse_buffered AS +SELECT + landuse, + st_buffer(geom, 0, 'join=mitre') AS geom +FROM osm_landuse_grouped; + +CREATE MATERIALIZED VIEW osm_landuse_exploded AS +SELECT + landuse, + (st_dump(geom)).geom AS geom +FROM osm_landuse_buffered; + +CREATE MATERIALIZED VIEW osm_landuse AS +SELECT + row_number() OVER () AS id, + jsonb_build_object('landuse', landuse) AS tags, + geom +FROM osm_landuse_exploded; \ No newline at end of file diff --git a/basemap/layers/leisure/create.sql b/basemap/layers/leisure/create.sql new file mode 100644 index 000000000..e42b14e70 --- /dev/null +++ b/basemap/layers/leisure/create.sql @@ -0,0 +1,61 @@ +-- 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_leisure_filtered AS +SELECT + tags -> 'leisure' AS leisure, + st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom +FROM osm_polygon +WHERE geom IS NOT NULL + AND st_area(geom) > 78270 / power(2, 12) * 100 + AND tags ->> 'leisure' IN ('garden', 'golf_course', 'marina', 'nature_reserve', 'park', 'pitch', 'sport_center', 'stadium', 'swimming_pool', 'track'); +CREATE INDEX IF NOT EXISTS osm_leisure_filtered_geom_idx ON osm_leisure_filtered USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_filtered_tags_idx ON osm_leisure_filtered (leisure); + +CREATE MATERIALIZED VIEW osm_leisure_clustered AS +SELECT + leisure, + geom, + st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY leisure) AS cluster +FROM osm_leisure_filtered +WHERE geom IS NOT NULL; +CREATE INDEX IF NOT EXISTS osm_leisure_clustered_geom_idx ON osm_leisure_clustered USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_leisure_clustered_tags_idx ON osm_leisure_clustered (leisure); + +CREATE MATERIALIZED VIEW osm_leisure_grouped AS +SELECT + leisure, + st_collect(geom) AS geom +FROM osm_leisure_clustered +GROUP BY leisure, cluster; + +CREATE MATERIALIZED VIEW osm_leisure_buffered AS +SELECT + leisure, + st_buffer(geom, 0, 'join=mitre') AS geom +FROM osm_leisure_grouped; + +CREATE MATERIALIZED VIEW osm_leisure_exploded AS +SELECT + leisure, + (st_dump(geom)).geom AS geom +FROM osm_leisure_buffered; + +CREATE MATERIALIZED VIEW osm_leisure AS +SELECT + row_number() OVER () AS id, + jsonb_build_object('leisure', leisure) AS tags, + geom +FROM osm_leisure_exploded; diff --git a/basemap/layers/linestring/create.sql b/basemap/layers/linestring/create.sql new file mode 100644 index 000000000..d8c0b7d22 --- /dev/null +++ b/basemap/layers/linestring/create.sql @@ -0,0 +1,6 @@ +CREATE MATERIALIZED VIEW osm_linestring AS +SELECT id, tags, geom, changeset +FROM osm_ways LEFT JOIN osm_member ON id = member_ref +WHERE ST_GeometryType(osm_ways.geom) = 'ST_LineString' + AND tags != '{}' + AND member_ref IS NULL; \ No newline at end of file diff --git a/basemap/layers/man_made/create.sql b/basemap/layers/man_made/create.sql new file mode 100644 index 000000000..c7ccfd363 --- /dev/null +++ b/basemap/layers/man_made/create.sql @@ -0,0 +1 @@ +SELECT id, tags, geom FROM osm_ways WHERE tags ? 'man_made' \ No newline at end of file diff --git a/basemap/layers/member/create.sql b/basemap/layers/member/create.sql new file mode 100644 index 000000000..e8ea265f2 --- /dev/null +++ b/basemap/layers/member/create.sql @@ -0,0 +1,8 @@ +CREATE MATERIALIZED VIEW osm_member AS +SELECT DISTINCT member_ref as member_ref +FROM osm_relations, + unnest(member_types, member_refs) AS way(member_type, member_ref) +WHERE geom IS NOT NULL + AND member_type = 1 + AND tags ->> 'type' = 'multipolygon' + AND NOT tags ->> 'natural' = 'coastline'; \ No newline at end of file diff --git a/basemap/layers/natural/create.sql b/basemap/layers/natural/create.sql new file mode 100644 index 000000000..888c17de0 --- /dev/null +++ b/basemap/layers/natural/create.sql @@ -0,0 +1,65 @@ +-- 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. + +-- ('grassland', 'heath', 'scrub', 'wood', 'bay', 'beach', 'glacier', 'mud', 'shingle', 'shoal', 'strait', 'water', 'wetland', 'bare_rock', 'sand', 'scree'); + +CREATE MATERIALIZED VIEW osm_natural_filtered AS +SELECT + tags -> 'natural' AS natural_value, + st_simplifypreservetopology(geom, 78270 / power(2, 12)) AS geom +FROM osm_polygon +WHERE geom IS NOT NULL + AND st_area(geom) > 78270 / power(2, 12) * 100 + AND tags ->> 'natural' IN ('grassland', 'heath', 'scrub', 'wood', 'bay', 'beach', 'glacier', 'mud', 'shingle', 'shoal', 'strait', 'water', 'wetland', 'bare_rock', 'sand', 'scree'); +CREATE INDEX IF NOT EXISTS osm_natural_filtered_geom_idx ON osm_natural_filtered USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_natural_filtered_tags_idx ON osm_natural_filtered (natural_value); + +CREATE MATERIALIZED VIEW osm_natural_clustered AS +SELECT + natural_value, + geom, + st_clusterdbscan(geom, 0, 0) OVER(PARTITION BY natural_value) AS cluster +FROM osm_natural_filtered +WHERE geom IS NOT NULL; +CREATE INDEX IF NOT EXISTS osm_natural_clustered_geom_idx ON osm_natural_clustered USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_natural_clustered_tags_idx ON osm_natural_clustered (natural_value); + +CREATE MATERIALIZED VIEW osm_natural_grouped AS +SELECT + natural_value, + st_collect(geom) AS geom +FROM osm_natural_clustered +GROUP BY natural_value, cluster; + +CREATE MATERIALIZED VIEW osm_natural_buffered AS +SELECT + natural_value, + st_buffer(geom, 0, 'join=mitre') AS geom +FROM osm_natural_grouped; + +CREATE MATERIALIZED VIEW osm_natural_exploded AS +SELECT + natural_value, + (st_dump(geom)).geom AS geom +FROM osm_natural_buffered; + +CREATE MATERIALIZED VIEW osm_natural AS +SELECT + row_number() OVER () AS id, + jsonb_build_object('natural', natural_value) AS tags, + geom +FROM osm_natural_exploded; +CREATE INDEX IF NOT EXISTS osm_natural_geom_idx ON osm_natural_filtered USING GIST (geom); +CREATE INDEX IF NOT EXISTS osm_natural_tags_idx ON osm_natural_filtered USING GIN (natural_value); diff --git a/basemap/layers/node/create.sql b/basemap/layers/node/create.sql new file mode 100644 index 000000000..c44b3d12f --- /dev/null +++ b/basemap/layers/node/create.sql @@ -0,0 +1,12 @@ +CREATE TABLE IF NOT EXISTS osm_nodes +( + id int8 PRIMARY KEY, + version int, + uid int, + timestamp timestamp without time zone, + changeset int8, + tags jsonb, + lon float, + lat float, + geom geometry(point) +); \ No newline at end of file diff --git a/basemap/layers/ocean/create.sql b/basemap/layers/ocean/create.sql new file mode 100644 index 000000000..77399e228 --- /dev/null +++ b/basemap/layers/ocean/create.sql @@ -0,0 +1,19 @@ +-- 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_ocean AS +SELECT row_number() OVER () as id, '{"ocean":"water"}'::jsonb as tags, st_setsrid(geometry, 3857) AS geom FROM water_polygons_shp; + +CREATE MATERIALIZED VIEW osm_ocean_simplified AS +SELECT row_number() OVER () as id, '{"ocean":"water"}'::jsonb as tags, st_setsrid(geometry, 3857) AS geom FROM simplified_water_polygons_shp; diff --git a/basemap/layers/point/clean.sql b/basemap/layers/point/clean.sql index 3203a3e99..f6dfa4e40 100644 --- a/basemap/layers/point/clean.sql +++ b/basemap/layers/point/clean.sql @@ -13,27 +13,4 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -DROP INDEX IF EXISTS osm_point_tags_index; -DROP INDEX IF EXISTS osm_point_geom_index; - -DROP VIEW IF EXISTS osm_point_z20 CASCADE; -DROP VIEW IF EXISTS osm_point_z19 CASCADE; -DROP VIEW IF EXISTS osm_point_z18 CASCADE; -DROP VIEW IF EXISTS osm_point_z17 CASCADE; -DROP VIEW IF EXISTS osm_point_z16 CASCADE; -DROP VIEW IF EXISTS osm_point_z15 CASCADE; -DROP VIEW IF EXISTS osm_point_z14 CASCADE; - -DROP MATERIALIZED VIEW IF EXISTS osm_point_z13 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z12 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z11 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z10 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z9 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z8 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z7 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z6 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z5 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z4 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z3 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z2 CASCADE; -DROP MATERIALIZED VIEW IF EXISTS osm_point_z1 CASCADE; \ No newline at end of file +DROP VIEW IF EXISTS point CASCADE; \ No newline at end of file diff --git a/basemap/layers/point/create.sql b/basemap/layers/point/create.sql new file mode 100644 index 000000000..5077eaef4 --- /dev/null +++ b/basemap/layers/point/create.sql @@ -0,0 +1,90 @@ +CREATE VIEW point AS +SELECT id, tags, geom +FROM osm_nodes +WHERE tags != '{}'; + +CREATE VIEW osm_point_z20 AS SELECT * FROM point; +CREATE VIEW osm_point_z19 AS SELECT * FROM point; +CREATE VIEW osm_point_z18 AS SELECT * FROM point; +CREATE VIEW osm_point_z17 AS SELECT * FROM point; +CREATE VIEW osm_point_z16 AS SELECT * FROM point; +CREATE VIEW osm_point_z15 AS SELECT * FROM point; +CREATE VIEW osm_point_z14 AS SELECT * FROM point; + +CREATE MATERIALIZED VIEW osm_point_z13 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['region','province','district','county','municipality','city','town','village','quarter','hamlet']) + OR (tags->>'natural') = ANY (ARRAY['peak','volcano','spring']) + OR (tags->>'highway') = 'motorway_junction' + OR (tags->>'tourism') = 'wilderness_hut' + OR (tags->>'waterway') = 'waterfall' + OR (tags->>'railway') = 'level_crossing'; + +CREATE MATERIALIZED VIEW osm_point_z12 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['region','province','district','county','municipality','city','town','village']) + OR (tags->>'natural') = ANY (ARRAY['peak','volcano']) + OR (tags->>'highway') = 'motorway_junction' + OR (tags->>'tourism') = 'wilderness_hut' + OR (tags->>'waterway') = 'waterfall'; + +CREATE MATERIALIZED VIEW osm_point_z11 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town','village']) + OR (tags->>'natural') = ANY (ARRAY['peak','volcano']) + OR (tags->>'highway') = 'motorway_junction'; + +CREATE MATERIALIZED VIEW osm_point_z10 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']) + OR (tags->>'natural') = ANY (ARRAY['peak','volcano']) + OR (tags->>'highway') = 'motorway_junction'; + +CREATE MATERIALIZED VIEW osm_point_z9 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']); + +CREATE MATERIALIZED VIEW osm_point_z8 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['country','state','region','province','district','county','municipality','city','town']); + +CREATE MATERIALIZED VIEW osm_point_z7 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']); + +CREATE MATERIALIZED VIEW osm_point_z6 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']); + +CREATE MATERIALIZED VIEW osm_point_z5 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['country','city','sea','state','county']); + +CREATE MATERIALIZED VIEW osm_point_z4 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['country','city','sea']); + +CREATE MATERIALIZED VIEW osm_point_z3 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = ANY (ARRAY['country','city','sea']); + +CREATE MATERIALIZED VIEW osm_point_z2 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = 'country'; + +CREATE MATERIALIZED VIEW osm_point_z1 AS +SELECT id, tags, geom +FROM point +WHERE (tags->>'place') = 'country'; diff --git a/basemap/layers/polygon/create.sql b/basemap/layers/polygon/create.sql new file mode 100644 index 000000000..500cb1604 --- /dev/null +++ b/basemap/layers/polygon/create.sql @@ -0,0 +1,16 @@ +CREATE MATERIALIZED VIEW osm_polygon AS +SELECT id, tags, geom +FROM osm_ways LEFT JOIN osm_member ON id = member_ref +WHERE ST_GeometryType(osm_ways.geom) = 'ST_Polygon' + AND tags != '{}' + AND member_ref IS NULL +UNION +SELECT id, tags, geom +FROM osm_relations +WHERE ST_GeometryType(osm_relations.geom) = 'ST_Polygon' + AND tags != '{}' +UNION +SELECT id, tags, (st_dump(geom)).geom as geom +FROM osm_relations +WHERE ST_GeometryType(osm_relations.geom) = 'ST_MultiPolygon' + AND tags != '{}'; \ No newline at end of file diff --git a/basemap/layers/power/create.sql b/basemap/layers/power/create.sql new file mode 100644 index 000000000..771556645 --- /dev/null +++ b/basemap/layers/power/create.sql @@ -0,0 +1,2 @@ +CREATE VIEW power AS +SELECT id, tags, geom FROM osm_ways WHERE tags ->> 'power' IN ('cable', 'line', 'minor_line', 'plant', 'substation'); \ No newline at end of file diff --git a/basemap/layers/railway/create.sql b/basemap/layers/railway/create.sql new file mode 100644 index 000000000..9265f962d --- /dev/null +++ b/basemap/layers/railway/create.sql @@ -0,0 +1,8 @@ +CREATE MATERIALIZED VIEW osm_railway AS +SELECT id, tags, geom +FROM (SELECT min(id) as id, + jsonb_build_object('railway', tags -> 'railway', 'service', tags -> 'service') as tags, + (st_dump(st_linemerge(st_collect(geom)))).geom as geom + FROM osm_ways + WHERE tags ->> 'railway' IN ('light_rail', 'monorail', 'rail', 'subway', 'tram') + GROUP BY tags -> 'railway', tags -> 'service') AS mergedDirective; \ No newline at end of file diff --git a/basemap/layers/relation/create.sql b/basemap/layers/relation/create.sql new file mode 100644 index 000000000..cd768f414 --- /dev/null +++ b/basemap/layers/relation/create.sql @@ -0,0 +1,13 @@ +CREATE TABLE IF NOT EXISTS osm_relations +( + id int8 PRIMARY KEY, + version int, + uid int, + timestamp timestamp without time zone, + changeset int8, + tags jsonb, + member_refs bigint[], + member_types int[], + member_roles text[], + geom geometry +); \ No newline at end of file diff --git a/basemap/layers/route/create.sql b/basemap/layers/route/create.sql new file mode 100644 index 000000000..8340d3b39 --- /dev/null +++ b/basemap/layers/route/create.sql @@ -0,0 +1,29 @@ +-- 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_route AS +SELECT id, tags, geom +FROM ( + SELECT + min(id) as id, + jsonb_build_object('route', tags -> 'route') as tags, + (st_dump(st_linemerge(st_collect(geom)))).geom as geom + FROM osm_ways + WHERE tags ->> 'route' IN ('light_rail', 'monorail', 'rail', 'subway', 'tram') + AND NOT tags ? 'service' + GROUP BY tags -> 'route' +) AS mergedDirective; + +CREATE INDEX IF NOT EXISTS osm_route_geom_index ON osm_route USING SPGIST (geom); diff --git a/basemap/layers/tourism/create.sql b/basemap/layers/tourism/create.sql new file mode 100644 index 000000000..a0d8a429e --- /dev/null +++ b/basemap/layers/tourism/create.sql @@ -0,0 +1,2 @@ +CREATE VIEW osm_tourism AS +SELECT id, tags, geom FROM osm_relations_z$zoom WHERE tags ? 'tourism'; \ No newline at end of file diff --git a/basemap/layers/waterway/create.sql b/basemap/layers/waterway/create.sql new file mode 100644 index 000000000..d3117d546 --- /dev/null +++ b/basemap/layers/waterway/create.sql @@ -0,0 +1,58 @@ +-- 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_waterway AS +WITH + -- Filter the linestrings + filtered AS ( + SELECT + tags -> 'waterway' AS waterway, + geom AS geom + FROM osm_linestring + WHERE tags ->> 'waterway' IN ('river', 'stream', 'canal', 'drain', 'ditch') + AND NOT tags ? 'intermittent' + ), + -- Cluster the linestrings + clustered AS ( + SELECT + waterway AS waterway, + geom as geom, + ST_ClusterDBSCAN(geom, 0, 1) OVER (PARTITION BY waterway) AS cluster + FROM + filtered + ), + -- Merge the linestrings into a single geometry per cluster + merged AS ( + SELECT + waterway AS waterway, + ST_LineMerge(ST_Collect(geom)) AS geom + FROM + clustered + GROUP BY + waterway, cluster + ), + -- Explode the merged linestrings into individual linestrings + exploded AS ( + SELECT + waterway AS waterway, + (ST_Dump(geom)).geom AS geom + FROM + merged + ) +SELECT + row_number() OVER () AS id, + jsonb_build_object('waterway', waterway) AS tags, + geom AS geom +FROM exploded; diff --git a/basemap/layers/way/create.sql b/basemap/layers/way/create.sql new file mode 100644 index 000000000..077a87aa2 --- /dev/null +++ b/basemap/layers/way/create.sql @@ -0,0 +1,11 @@ +CREATE TABLE IF NOT EXISTS osm_ways +( + id int8 PRIMARY KEY, + version int, + uid int, + timestamp timestamp without time zone, + changeset int8, + tags jsonb, + nodes int8[], + geom geometry +);
