ThomasG77/generate-qpv-parquet.sh

## generate-qpv-parquet.sh
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

## query-duckdb-pip.html
<!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/duckdb-wasm@1.30.0/+esm';
      // import * as duckdbduckdbWasm from "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.1-dev106.0/+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>
	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
	<!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/duckdb-wasm@1.30.0/+esm';
	// import * as duckdbduckdbWasm from "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.1-dev106.0/+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>