ilyabo · October 29, 2025 09:07
diff --git a/app-corrupt-linestrings-repro.py b/app-corrupt-linestrings-repro.py
 import duckdb
 import flask
 import subprocess
 import os
 from pathlib import Path

 # Download and unpack shapefile if not already present
 data_dir = Path("data")
 data_dir.mkdir(exist_ok=True)

 shp_file = data_dir / "ne_10m_roads.shp"

 if not shp_file.exists():
    print("Downloading Natural Earth roads data...")
    zip_url = "https://naciscdn.org/naturalearth/10m/cultural/ne_10m_roads.zip"
    zip_file = data_dir / "ne_10m_roads.zip"
    unzip_dir = data_dir / "ne_10m_roads"
    
    # Download the ZIP file
    subprocess.run(
        ["curl", "-L", "-o", str(zip_file), zip_url],
        check=True
    )
    
    # Create unzip directory
    unzip_dir.mkdir(exist_ok=True)
    
    # Unzip into subdirectory
    print("Extracting shapefile...")
    subprocess.run(
        ["unzip", "-o", str(zip_file), "-d", str(unzip_dir)],
        check=True
    )
    
    print(f"Shapefile extracted to {unzip_dir}")
 else:
    print(f"Shapefile already exists at {shp_file}")

 # Initialize Flask app
 app = flask.Flask(__name__)

 # Setup a global DuckDB connection with spatial extension loaded
 # Connect to a persistent database file with the geometry data
 config = {"allow_unsigned_extensions": "true"}
 con = duckdb.connect(":memory:", False, config)

 # Install spatial from wherever you built it
 #con.execute("INSTALL spatial from <some path>")

 con.execute("INSTALL spatial")
 con.execute("load spatial")

 print("Creating roads table...")
 con.execute("""
 CREATE OR REPLACE TABLE roads AS SELECT * FROM ST_Read('./data/ne_10m_roads/ne_10m_roads.shp');
 ALTER TABLE roads ADD COLUMN tiles_geometry GEOMETRY;
 UPDATE roads SET tiles_geometry = ST_Transform(geom, 'EPSG:4326', 'EPSG:3857', always_xy := true);
 """)



 # Tile endpoint to serve vector tiles
 @app.route('/tiles/<int:z>/<int:x>/<int:y>.pbf')
 def get_tile(z, x, y):

    # Query to get the tile data from DuckDB
    # - Note that the geometry in table `t1` is assumed to be projected to `EPSG:3857` (Web Mercator)
    # - You may want to create an R-Tree index on the geometry column, or create a separate bounding box struct column
    #   to perform range-filtering, or somehow pre-filter the geometries some other way before feeding them into 
    #   ST_AsMVTGeom if your dataset is large (and on disk)

    # Use con.cursor() to avoid threading issues with Flask
    with con.cursor() as local_con:
        tile_blob = local_con.execute("""
            SELECT ST_AsMVT({
                "geometry": ST_AsMVTGeom(
                    tiles_geometry,
                    ST_Extent(ST_TileEnvelope($1, $2, $3))
                    )
                })
            FROM roads 
            WHERE ST_Intersects(tiles_geometry, ST_TileEnvelope($1, $2, $3))
            """, [z, x, y]).fetchone()

        # Send the tile data as a response
        tile = tile_blob[0] if tile_blob and tile_blob[0] else b''
        return flask.Response(tile, mimetype='application/x-protobuf')


 # HTML content for the index page
 INDEX_HTML = """
 <!DOCTYPE html>
 <html>
 <head>
    <meta charset="utf-8">
    <title>Vector Tile Viewer</title>
    <meta name="viewport" content="initial-scale=1,maximum-scale=1,user-scalable=no">
    <script src='https://unpkg.com/[email protected]/dist/maplibre-gl.js'></script>
    <link href='https://unpkg.com/[email protected]/dist/maplibre-gl.css' rel='stylesheet' />
    <style>
        body { margin: 0; padding: 0; }
        #map { position: absolute; top: 0; bottom: 0; width: 100%; }
    </style>
 </head>
 <body>
 <div id="map"></div>
 <script>
    const map = new maplibregl.Map({
        container: 'map',
        style: {
            version: 8,
            sources: {
                'roads': {
                    type: 'vector',
                    tiles: [`${window.location.origin}/tiles/{z}/{x}/{y}.pbf`],
                    minzoom: 0
                },
                // Also use a public open source basemap
                'osm': {
                    type: 'raster',
                    tiles: [
                        'https://a.tile.openstreetmap.org/{z}/{x}/{y}.png',
                        'https://b.tile.openstreetmap.org/{z}/{x}/{y}.png',
                        'https://c.tile.openstreetmap.org/{z}/{x}/{y}.png'
                    ],
                    tileSize: 256,
                    minzoom: 0
                }
            },
            layers: [
                {
                    id: 'background',
                    type: 'background',
                    paint: { 'background-color': '#a0c8f0' }
                },
                {
                    id: 'osm',
                    type: 'raster',
                    source: 'osm',
                    minzoom: 0,
                    maxzoom: 19
                },
                {
                    id: 'roads-stroke',
                    type: 'line',
                    source: 'roads',
                    'source-layer': 'layer',
                    paint: {
                        'line-color': 'blue',
                        'line-width': 2
                    }
                }
            ]
        },
        center: [0, 0],
        zoom: 1
    });

    map.addControl(new maplibregl.NavigationControl());

    // Add click handler to show feature properties
    map.on('click', 'roads-stroke', (e) => {
        const coordinates = e.lngLat;
        const properties = e.features[0].properties;

        let popupContent = '<h3>Building Properties</h3>';
        for (const [key, value] of Object.entries(properties)) {
            popupContent += `<p><strong>${key}:</strong> ${value}</p>`;
        }

        new maplibregl.Popup()
            .setLngLat(coordinates)
            .setHTML(popupContent)
            .addTo(map);
    });

    // Change cursor on hover
    map.on('mouseenter', 'roads-stroke', () => {
        map.getCanvas().style.cursor = 'pointer';
    });

    map.on('mouseleave', 'roads-stroke', () => {
        map.getCanvas().style.cursor = '';
    });
 </script>
 </body>
 </html>
 """

 # Serve the static HTML file for the index page
 @app.route("/")
 def index():
    return flask.Response(INDEX_HTML, mimetype='text/html')

 if __name__ == '__main__':
    # Start on localhost
    app.run(debug=True)
	import duckdb
	import flask
	import subprocess
	import os
	from pathlib import Path

	# Download and unpack shapefile if not already present
	data_dir = Path("data")
	data_dir.mkdir(exist_ok=True)

	shp_file = data_dir / "ne_10m_roads.shp"

	if not shp_file.exists():
	print("Downloading Natural Earth roads data...")
	zip_url = "https://naciscdn.org/naturalearth/10m/cultural/ne_10m_roads.zip"
	zip_file = data_dir / "ne_10m_roads.zip"
	unzip_dir = data_dir / "ne_10m_roads"

	# Download the ZIP file
	subprocess.run(
	["curl", "-L", "-o", str(zip_file), zip_url],
	check=True
	)

	# Create unzip directory
	unzip_dir.mkdir(exist_ok=True)

	# Unzip into subdirectory
	print("Extracting shapefile...")
	subprocess.run(
	["unzip", "-o", str(zip_file), "-d", str(unzip_dir)],
	check=True
	)

	print(f"Shapefile extracted to {unzip_dir}")
	else:
	print(f"Shapefile already exists at {shp_file}")

	# Initialize Flask app
	app = flask.Flask(__name__)

	# Setup a global DuckDB connection with spatial extension loaded
	# Connect to a persistent database file with the geometry data
	config = {"allow_unsigned_extensions": "true"}
	con = duckdb.connect(":memory:", False, config)

	# Install spatial from wherever you built it
	#con.execute("INSTALL spatial from <some path>")

	con.execute("INSTALL spatial")
	con.execute("load spatial")

	print("Creating roads table...")
	con.execute("""
	CREATE OR REPLACE TABLE roads AS SELECT * FROM ST_Read('./data/ne_10m_roads/ne_10m_roads.shp');
	ALTER TABLE roads ADD COLUMN tiles_geometry GEOMETRY;
	UPDATE roads SET tiles_geometry = ST_Transform(geom, 'EPSG:4326', 'EPSG:3857', always_xy := true);
	""")



	# Tile endpoint to serve vector tiles
	@app.route('/tiles/<int:z>/<int:x>/<int:y>.pbf')
	def get_tile(z, x, y):

	# Query to get the tile data from DuckDB
	# - Note that the geometry in table `t1` is assumed to be projected to `EPSG:3857` (Web Mercator)
	# - You may want to create an R-Tree index on the geometry column, or create a separate bounding box struct column
	# to perform range-filtering, or somehow pre-filter the geometries some other way before feeding them into
	# ST_AsMVTGeom if your dataset is large (and on disk)

	# Use con.cursor() to avoid threading issues with Flask
	with con.cursor() as local_con:
	tile_blob = local_con.execute("""
	SELECT ST_AsMVT({
	"geometry": ST_AsMVTGeom(
	tiles_geometry,
	ST_Extent(ST_TileEnvelope($1, $2, $3))
	)
	})
	FROM roads
	WHERE ST_Intersects(tiles_geometry, ST_TileEnvelope($1, $2, $3))
	""", [z, x, y]).fetchone()

	# Send the tile data as a response
	tile = tile_blob[0] if tile_blob and tile_blob[0] else b''
	return flask.Response(tile, mimetype='application/x-protobuf')


	# HTML content for the index page
	INDEX_HTML = """
	<!DOCTYPE html>
	<html>
	<head>
	<meta charset="utf-8">
	<title>Vector Tile Viewer</title>
	<meta name="viewport" content="initial-scale=1,maximum-scale=1,user-scalable=no">
	<script src='https://unpkg.com/[email protected]/dist/maplibre-gl.js'></script>
	<link href='https://unpkg.com/[email protected]/dist/maplibre-gl.css' rel='stylesheet' />
	<style>
	body { margin: 0; padding: 0; }
	#map { position: absolute; top: 0; bottom: 0; width: 100%; }
	</style>
	</head>
	<body>
	<div id="map"></div>
	<script>
	const map = new maplibregl.Map({
	container: 'map',
	style: {
	version: 8,
	sources: {
	'roads': {
	type: 'vector',
	tiles: [`${window.location.origin}/tiles/{z}/{x}/{y}.pbf`],
	minzoom: 0
	},
	// Also use a public open source basemap
	'osm': {
	type: 'raster',
	tiles: [
	'https://a.tile.openstreetmap.org/{z}/{x}/{y}.png',
	'https://b.tile.openstreetmap.org/{z}/{x}/{y}.png',
	'https://c.tile.openstreetmap.org/{z}/{x}/{y}.png'
	],
	tileSize: 256,
	minzoom: 0
	}
	},
	layers: [
	{
	id: 'background',
	type: 'background',
	paint: { 'background-color': '#a0c8f0' }
	},
	{
	id: 'osm',
	type: 'raster',
	source: 'osm',
	minzoom: 0,
	maxzoom: 19
	},
	{
	id: 'roads-stroke',
	type: 'line',
	source: 'roads',
	'source-layer': 'layer',
	paint: {
	'line-color': 'blue',
	'line-width': 2
	}
	}
	]
	},
	center: [0, 0],
	zoom: 1
	});

	map.addControl(new maplibregl.NavigationControl());

	// Add click handler to show feature properties
	map.on('click', 'roads-stroke', (e) => {
	const coordinates = e.lngLat;
	const properties = e.features[0].properties;

	let popupContent = '<h3>Building Properties</h3>';
	for (const [key, value] of Object.entries(properties)) {
	popupContent += `<p><strong>${key}:</strong> ${value}</p>`;
	}

	new maplibregl.Popup()
	.setLngLat(coordinates)
	.setHTML(popupContent)
	.addTo(map);
	});

	// Change cursor on hover
	map.on('mouseenter', 'roads-stroke', () => {
	map.getCanvas().style.cursor = 'pointer';
	});

	map.on('mouseleave', 'roads-stroke', () => {
	map.getCanvas().style.cursor = '';
	});
	</script>
	</body>
	</html>
	"""

	# Serve the static HTML file for the index page
	@app.route("/")
	def index():
	return flask.Response(INDEX_HTML, mimetype='text/html')

	if __name__ == '__main__':
	# Start on localhost
	app.run(debug=True)