Created
December 1, 2025 14:36
-
-
Save ThomasG77/98d2e99ed9d42ef73b092268ae774313 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| wget https://static.data.gouv.fr/resources/quartiers-prioritaires-de-la-politique-de-la-ville-qpv/20250206-161839/qp2024-france-hexagonale-outre-mer-wgs84-epsg4326.geojson | |
| duckdb :memory: "INSTALL spatial;LOAD spatial;COPY (SELECT * FROM ST_Read('qp2024-france-hexagonale-outre-mer-wgs84-epsg4326.geojson')) TO 'qpv-2024.parquet' (FORMAT 'parquet', COMPRESSION 'zstd');" | |
| rm qp2024-france-hexagonale-outre-mer-wgs84-epsg4326.geojson | |
| gpio add bbox qpv-2024.parquet qpv-2024-with-bbox.parquet | |
| rm qpv-2024.parquet | |
| gpio sort hilbert qpv-2024-with-bbox.parquet qpv-2024.parquet | |
| rm qpv-2024-with-bbox.parquet | |
| gpio check all qpv-2024.parquet |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <meta http-equiv="content-type" content="text/html; charset=UTF-8"> | |
| <title>Query remote Geoparquet in browser</title> | |
| <meta name="viewport" content="width=device-width, initial-scale=1"> | |
| </head> | |
| <body> | |
| <script> | |
| const getDb = async () => { | |
| const duckdb = window.duckdbduckdbWasm; | |
| // @ts-ignore | |
| if (window._db) return window._db; | |
| const JSDELIVR_BUNDLES = duckdb.getJsDelivrBundles(); | |
| // Select a bundle based on browser checks | |
| const bundle = await duckdb.selectBundle(JSDELIVR_BUNDLES); | |
| const worker_url = URL.createObjectURL( | |
| new Blob([`importScripts("${bundle.mainWorker}");`], { | |
| type: "text/javascript", | |
| }) | |
| ); | |
| // Instantiate the asynchronous version of DuckDB-wasm | |
| const worker = new Worker(worker_url); | |
| // const logger = null //new duckdb.ConsoleLogger(); | |
| const logger = new duckdb.ConsoleLogger(); | |
| const db = new duckdb.AsyncDuckDB(logger, worker); | |
| await db.instantiate(bundle.mainModule, bundle.pthreadWorker); | |
| URL.revokeObjectURL(worker_url); | |
| window._db = db; | |
| // console.log('duckdb.DuckDBDataProtocol.HTTP', duckdb.DuckDBDataProtocol.HTTP); | |
| return db; | |
| }; | |
| </script> | |
| <div id="map"></div> | |
| <script type="module"> | |
| import * as duckdbduckdbWasm from 'https://cdn.jsdelivr.net/npm/@duckdb/[email protected]/+esm'; | |
| // import * as duckdbduckdbWasm from "https://cdn.jsdelivr.net/npm/@duckdb/[email protected]/+esm"; | |
| window.duckdbduckdbWasm = duckdbduckdbWasm; | |
| const db = await getDb() | |
| // Create a new connection | |
| await db.open({ | |
| query: { | |
| castBigIntToDouble: true, | |
| castDecimalToDouble: true, | |
| castDurationToTime64: true, | |
| castTimestampToDate: true, | |
| queryPollingInterval: 1000, | |
| }, | |
| }) | |
| const conn = await db.connect(); | |
| // ..., by specifying URLs in the SQL text | |
| await conn.query(` | |
| INSTALL spatial | |
| `) | |
| await conn.query(` | |
| LOAD spatial | |
| `) | |
| const myquery = await conn.query(` | |
| SELECT code_qp, lib_qp, insee_reg, lib_reg, insee_dep, lib_dep, insee_com, lib_com, siren_epci FROM "https://labs.webgeodatavore.com/partage/qpv-2024.parquet" | |
| WHERE ST_contains(CAST(geom AS GEOMETRY), ST_Point(2.389798, 48.909514)) | |
| `) | |
| // console.log(myquery.getChildAt(0)?.toArray()) | |
| // console.log(myquery.toArray()); | |
| const data = JSON.parse(JSON.stringify(myquery.toArray())) | |
| // If data length = 0, no match else the qpv matching | |
| console.log('data', data) | |
| // Below not working as expected as in | |
| // https://github.com/duckdb/duckdb-wasm/issues/1840#issuecomment-2391526951 | |
| await db.registerFileHandle('tmp_results.geojson', new Uint8Array(), 0, true); // Duck/Spatial needs this but can't create is for some reason? | |
| await db.registerFileHandle('results.geojson', new Uint8Array(), 0, true); // The file name you want to use without the 'tmp_' prefix | |
| await conn.query(` | |
| copy ( | |
| SELECT code_qp, lib_qp, insee_reg, lib_reg, insee_dep, lib_dep, insee_com, lib_com, siren_epci, geom FROM "https://labs.webgeodatavore.com/partage/qpv-2024.parquet" | |
| WHERE ST_contains(CAST(geom AS GEOMETRY), ST_Point(2.389798, 48.909514)) | |
| ) to 'results.geojson' | |
| with ( | |
| FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES' | |
| );` | |
| ); | |
| const geojson_buffer = await db.copyFileToBuffer('results.geojson'); | |
| const link = URL.createObjectURL(new Blob([geojson_buffer])); | |
| console.log("geojson url: ", link); | |
| </script> | |
| </body> | |
| </html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment