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
The following commit(s) were added to refs/heads/main by this push:
new b487cfc3 Add join postgresql with ol (#812)
b487cfc3 is described below
commit b487cfc300dafcd2e0175db7062efaf7181e6c49
Author: jwaddle <[email protected]>
AuthorDate: Mon Dec 11 14:11:49 2023 +0100
Add join postgresql with ol (#812)
* feat(example): add join postgreSQL join example
* chore(review)
---
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"
+ }
+ ]
+ }
+ ]
+}
