Add support for DuckDB vector tiles #1234
Conversation
There was a problem hiding this comment.
Pull Request Overview
This PR adds support for visualizing large vector datasets through DuckDB-powered vector tile serving. The implementation enables efficient rendering of millions of features by serving them as Mapbox Vector Tiles (MVT) through a local Flask server backed by DuckDB.
Key Changes
- Added
add_duckdb_layer()method to MapLibre GL map class for adding DuckDB-backed vector tile layers - Implemented
init_duckdb_tiles()function to initialize DuckDB databases with spatial data and automatic Web Mercator transformation - Created
start_duckdb_tile_server()function to serve vector tiles via Flask with automatic port allocation
Reviewed Changes
Copilot reviewed 2 out of 2 changed files in this pull request and generated 6 comments.
| File | Description |
|---|---|
| leafmap/maplibregl.py | Added add_duckdb_layer() method with comprehensive configuration options and automatic bounds fitting |
| leafmap/common.py | Implemented DuckDB database initialization with CRS detection/transformation and Flask-based vector tile server |
Tip: Customize your code reviews with copilot-instructions.md. Create the file or learn how to get started.
leafmap/common.py
Outdated
| prop_assigns = ", ".join( | ||
| [f'"{prop}": {prop}' for prop in prop_list] | ||
| ) |
There was a problem hiding this comment.
Property names are not sanitized before being used in the SQL query. If property names come from untrusted sources or contain special characters, this could lead to SQL injection or invalid SQL. Consider validating or sanitizing property names, or using DuckDB's parameter binding for property names if supported.
leafmap/common.py
Outdated
| if properties is None: | ||
| # Get column names | ||
| columns = con.execute( | ||
| f"SELECT column_name FROM information_schema.columns WHERE table_name = '{table_name}' AND column_name != '{geom_column}'" |
There was a problem hiding this comment.
The table_name and geom_column parameters are directly interpolated into SQL queries without sanitization. This could allow SQL injection if these values come from untrusted sources. Consider using parameterized queries or validating that these values contain only alphanumeric characters and underscores.
| query = f""" | ||
| SELECT ST_AsMVT({{ | ||
| {prop_assigns}, | ||
| "geom": ST_AsMVTGeom( | ||
| {geom_column}, | ||
| ST_Extent(ST_TileEnvelope($1, $2, $3)) | ||
| ) | ||
| }}) | ||
| FROM {table_name} | ||
| WHERE ST_Intersects({geom_column}, ST_TileEnvelope($1, $2, $3)) | ||
| """ |
There was a problem hiding this comment.
The table_name and geom_column are directly interpolated into the SQL query. If these values originate from user input or untrusted sources, this creates a SQL injection vulnerability. Use parameter binding or validate these identifiers to ensure they are safe.
leafmap/common.py
Outdated
| con.execute("LOAD spatial;") | ||
|
|
||
| # First, get column names from the source to exclude geometry | ||
| temp_result = con.execute(f"SELECT * FROM ST_Read('{input_path}') LIMIT 0") |
There was a problem hiding this comment.
The input_path is directly interpolated into the SQL query without sanitization. If this path comes from user input, it could potentially be exploited for SQL injection. Consider using DuckDB's parameter binding if available for file paths, or validate the path to ensure it points to a legitimate file.
leafmap/common.py
Outdated
| # Try alternative approach without transformation | ||
| try: | ||
| # First, get column names to exclude geometry | ||
| temp_result = con.execute(f"SELECT * FROM ST_Read('{input_path}') LIMIT 0") |
There was a problem hiding this comment.
The input_path is directly interpolated into the SQL query in the fallback error handling block. This creates the same SQL injection risk as the primary code path. Ensure consistent input validation or parameterization across all code paths.
leafmap/maplibregl.py
Outdated
| bounds_query = f""" | ||
| SELECT | ||
| ST_X(ST_Transform(ST_Point(ST_XMin(ST_Extent({geom_column})), ST_YMin(ST_Extent({geom_column}))), 'EPSG:3857', 'EPSG:4326')) as minx, | ||
| ST_Y(ST_Transform(ST_Point(ST_XMin(ST_Extent({geom_column})), ST_YMin(ST_Extent({geom_column}))), 'EPSG:3857', 'EPSG:4326')) as miny, | ||
| ST_X(ST_Transform(ST_Point(ST_XMax(ST_Extent({geom_column})), ST_YMax(ST_Extent({geom_column}))), 'EPSG:3857', 'EPSG:4326')) as maxx, | ||
| ST_Y(ST_Transform(ST_Point(ST_XMax(ST_Extent({geom_column})), ST_YMax(ST_Extent({geom_column}))), 'EPSG:3857', 'EPSG:4326')) as maxy | ||
| FROM {table_name} | ||
| """ |
There was a problem hiding this comment.
The geom_column and table_name parameters are directly interpolated into the SQL query without sanitization. If these values come from user input, this could lead to SQL injection. Consider validating these identifiers or using parameterized queries.
|
🚀 Deployed on https://68f859978841341208678f91--opengeos.netlify.app |
for more information, see https://pre-commit.ci
Fix #1203
This is the most wanted feature I have been waiting for years!