This is an automated email from the ASF dual-hosted git repository. bchapuis pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
commit ee17c3cc96624dda4bc40f808daf48b174dd4d2c Author: jwaddle <[email protected]> AuthorDate: Mon Dec 11 14:11:49 2023 +0100 Add postgresql join example (#812) --- examples/postgresql-join/init.sql | 46 ++++++++++++++++++++++++++++++++++ examples/postgresql-join/style.json | 41 ++++++++++++++++++++++++++++++ examples/postgresql-join/tileset.json | 26 +++++++++++++++++++ examples/postgresql-join/workflow.json | 15 +++++++++++ 4 files changed, 128 insertions(+) diff --git a/examples/postgresql-join/init.sql b/examples/postgresql-join/init.sql new file mode 100644 index 00000000..1c26f8c1 --- /dev/null +++ b/examples/postgresql-join/init.sql @@ -0,0 +1,46 @@ +CREATE EXTENSION IF NOT EXISTS postgis; + +-- Drop and create schema +DROP SCHEMA IF EXISTS baremaps CASCADE; +CREATE SCHEMA baremaps; + +-- Create table baremaps.tree_type +CREATE TABLE IF NOT EXISTS baremaps.tree_type ( + id SERIAL PRIMARY KEY, + type VARCHAR(255) NOT NULL +); + +-- Populate baremaps.tree_type with 10 different types of trees +INSERT INTO baremaps.tree_type (type) VALUES + ('Oak'), + ('Maple'), + ('Pine'), + ('Birch'), + ('Spruce'), + ('Willow'), + ('Cherry'), + ('Poplar'), + ('Cypress'), + ('Cedar'); + +-- Create table baremaps.point_trees +CREATE TABLE IF NOT EXISTS baremaps.point_trees ( + id SERIAL PRIMARY KEY, + geom GEOMETRY(Point, 3857) NOT NULL, + size INTEGER, + tree_type_id INTEGER REFERENCES baremaps.tree_type(id) +); + +-- Populate baremaps.point_trees with 1000 random points in France +INSERT INTO baremaps.point_trees (geom, size, tree_type_id) +SELECT + ST_Transform( + ST_SetSRID(ST_MakePoint( + random() * (5.5 - 0.5) + 0.5, -- Longitude range for France + random() * (51.1 - 41.1) + 41.1 -- Latitude range for France + ), 4326) + ,3857), + floor(random() * 100) + 1, -- Random size between 1 and 100 + floor(random() * 10) + 1 -- Random tree_type_id between 1 and 10 +FROM generate_series(1, 1000); -- Number of points to generate + diff --git a/examples/postgresql-join/style.json b/examples/postgresql-join/style.json new file mode 100644 index 00000000..153da781 --- /dev/null +++ b/examples/postgresql-join/style.json @@ -0,0 +1,41 @@ +{ + "version": 8, + "sources": { + "baremaps": { + "type": "vector", + "url": "http://localhost:9000/tiles.json"; + } + }, + "center": [ + 2.6231, + 48.8404 + ], + "zoom": 10, + "layers": [ + { + "id": "trees", + "type": "circle", + "source": "baremaps", + "source-layer": "trees", + "minzoom": 0, + "maxzoom": 20, + "paint": { + "circle-radius": 6, + "circle-color": [ + "match", + ["get", "type"], + "Oak", "#33a02c", + "Maple", "#1f78b4", + "Pine", "#e31a1c", + "Birch", "#ff7f00", + "Spruce", "#6a3d9a", + "Poplar", "#fdbf6f", + "Cypress", "#cab2d6", + "Cedar", "#ffff99", + "#000000" + ] + } + } + ] +} + diff --git a/examples/postgresql-join/tileset.json b/examples/postgresql-join/tileset.json new file mode 100644 index 00000000..adf84dab --- /dev/null +++ b/examples/postgresql-join/tileset.json @@ -0,0 +1,26 @@ +{ + "tilejson": "2.1.0", + "tiles": [ + "http://localhost:9000/tiles/{z}/{x}/{y}.mvt"; + ], + "database": "jdbc:postgresql://localhost:5432/baremaps?&user=baremaps&password=baremaps", + "bounds": [ + -180, + -90, + 180, + 90 + ], + "vector_layers": [ + { + "id": "trees", + "queries": [ + { + "minzoom": 0, + "maxzoom": 20, + "sql": "SELECT pt.id::integer as id, jsonb_build_object('type', tt.type, 'size', pt.size) as tags, pt.geom as geom FROM baremaps.point_trees pt JOIN baremaps.tree_type tt ON pt.tree_type_id = tt.id" + } + ] + } + ] +} + diff --git a/examples/postgresql-join/workflow.json b/examples/postgresql-join/workflow.json new file mode 100644 index 00000000..600eb049 --- /dev/null +++ b/examples/postgresql-join/workflow.json @@ -0,0 +1,15 @@ +{ + "steps": [ + { + "id": "postgresql-join", + "needs": [], + "tasks": [ + { + "type": "ExecuteSqlScript", + "file": "./init.sql", + "database": "jdbc:postgresql://localhost:5432/baremaps?&user=baremaps&password=baremaps" + } + ] + } + ] +}
