100%

Exemples de consommation de DVF via parquet et Duckdb

✓ Published0🌍 Public

Last edited Nov 19, 2025

Created on Nov 19, 2025

This example demonstrates how to extract and filter French property transaction data (DVF) from a cloud-hosted Parquet file using DuckDB's SQL engine, then export the filtered results as GeoJSON for specific geographic areas. It shows three distinct query scopes: a single commune (Nantes), a multi-commune agglomeration, and a department-level export. The workflow relies on DuckDB's `INSTALL spatial` and `LOAD spatial` extensions, which enable reading the Parquet file directly from the `data.gouv.fr` URL. The `COPY` command with `FORMAT gdal` and `DRIVER 'GeoJSON'` handles the conversion, while explicit type casting (e.g., `::DOUBLE`) ensures correct numeric fields like `valeur_fonciere` and coordinates (longitude/latitude), and preserves the geometry column for spatial output.

AI-generated description

Exemples de consommation de DVF via parquet et Duckdb

# Une commune
duckdb :memory: "INSTALL spatial;LOAD spatial;COPY (SELECT id_mutation, date_mutation, numero_disposition, nature_mutation, valeur_fonciere::DOUBLE AS valeur_fonciere, adresse_numero, adresse_suffixe, adresse_nom_voie, adresse_code_voie, code_postal, code_commune, nom_commune, code_departement, ancien_code_commune, ancien_nom_commune, id_parcelle, ancien_id_parcelle, numero_volume, lot1_numero, lot1_surface_carrez::DOUBLE AS lot1_surface_carrez, lot2_numero, lot2_surface_carrez::DOUBLE AS lot2_surface_carrez, lot3_numero, lot3_surface_carrez::DOUBLE AS lot3_surface_carrez, lot4_numero, lot4_surface_carrez::DOUBLE AS lot4_surface_carrez, lot5_numero, lot5_surface_carrez::DOUBLE AS lot5_surface_carrez, nombre_lots, code_type_local, type_local, surface_reelle_bati::DOUBLE AS surface_reelle_bati, nombre_pieces_principales, code_nature_culture, nature_culture, code_nature_culture_speciale, nature_culture_speciale, surface_terrain::DOUBLE AS surface_terrain, longitude::DOUBLE AS longitude, latitude::DOUBLE AS latitude, geometry FROM 'https://object.data.gouv.fr/dataeng-open/dvf.parquet' WHERE code_commune = '44109') TO '/tmp/44109.geojson'
WITH (FORMAT gdal, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES', SRS 'EPSG:4326');"

# Plusieurs communes
duckdb :memory: "INSTALL spatial;LOAD spatial;COPY (SELECT id_mutation, date_mutation, numero_disposition, nature_mutation, valeur_fonciere::DOUBLE AS valeur_fonciere, adresse_numero, adresse_suffixe, adresse_nom_voie, adresse_code_voie, code_postal, code_commune, nom_commune, code_departement, ancien_code_commune, ancien_nom_commune, id_parcelle, ancien_id_parcelle, numero_volume, lot1_numero, lot1_surface_carrez::DOUBLE AS lot1_surface_carrez, lot2_numero, lot2_surface_carrez::DOUBLE AS lot2_surface_carrez, lot3_numero, lot3_surface_carrez::DOUBLE AS lot3_surface_carrez, lot4_numero, lot4_surface_carrez::DOUBLE AS lot4_surface_carrez, lot5_numero, lot5_surface_carrez::DOUBLE AS lot5_surface_carrez, nombre_lots, code_type_local, type_local, surface_reelle_bati::DOUBLE AS surface_reelle_bati, nombre_pieces_principales, code_nature_culture, nature_culture, code_nature_culture_speciale, nature_culture_speciale, surface_terrain::DOUBLE AS surface_terrain, longitude::DOUBLE AS longitude, latitude::DOUBLE AS latitude, geometry FROM 'https://object.data.gouv.fr/dataeng-open/dvf.parquet' WHERE code_commune IN ('44009', '44018', '44020', '44024', '44026', '44035', '44047', '44074', '44094', '44101', '44109', '44114', '44120', '44143', '44150', '44162', '44166', '44171', '44172', '44190', '44194', '44198', '44204', '44215')) TO '/tmp/nantes-agglomeration.geojson'
WITH (FORMAT gdal, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES', SRS 'EPSG:4326');"

# Plusieurs communes
duckdb :memory: "INSTALL spatial;LOAD spatial;COPY (SELECT id_mutation, date_mutation, numero_disposition, nature_mutation, valeur_fonciere::DOUBLE AS valeur_fonciere, adresse_numero, adresse_suffixe, adresse_nom_voie, adresse_code_voie, code_postal, code_commune, nom_commune, code_departement, ancien_code_commune, ancien_nom_commune, id_parcelle, ancien_id_parcelle, numero_volume, lot1_numero, lot1_surface_carrez::DOUBLE AS lot1_surface_carrez, lot2_numero, lot2_surface_carrez::DOUBLE AS lot2_surface_carrez, lot3_numero, lot3_surface_carrez::DOUBLE AS lot3_surface_carrez, lot4_numero, lot4_surface_carrez::DOUBLE AS lot4_surface_carrez, lot5_numero, lot5_surface_carrez::DOUBLE AS lot5_surface_carrez, nombre_lots, code_type_local, type_local, surface_reelle_bati::DOUBLE AS surface_reelle_bati, nombre_pieces_principales, code_nature_culture, nature_culture, code_nature_culture_speciale, nature_culture_speciale, surface_terrain::DOUBLE AS surface_terrain, longitude::DOUBLE AS longitude, latitude::DOUBLE AS latitude, geometry FROM 'https://object.data.gouv.fr/dataeng-open/dvf.parquet' WHERE code_commune IN ('44009', '44018', '44020', '44024', '44026', '44035', '44047', '44074', '44094', '44101', '44109', '44114', '44120', '44143', '44150', '44162', '44166', '44171', '44172', '44190', '44194', '44198', '44204', '44215')) TO '/tmp/nantes-agglomeration.geojson'
WITH (FORMAT gdal, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES', SRS 'EPSG:4326');"

# Un département
duckdb :memory: "INSTALL spatial;LOAD spatial;COPY (SELECT id_mutation, date_mutation, numero_disposition, nature_mutation, valeur_fonciere::DOUBLE AS valeur_fonciere, adresse_numero, adresse_suffixe, adresse_nom_voie, adresse_code_voie, code_postal, code_commune, nom_commune, code_departement, ancien_code_commune, ancien_nom_commune, id_parcelle, ancien_id_parcelle, numero_volume, lot1_numero, lot1_surface_carrez::DOUBLE AS lot1_surface_carrez, lot2_numero, lot2_surface_carrez::DOUBLE AS lot2_surface_carrez, lot3_numero, lot3_surface_carrez::DOUBLE AS lot3_surface_carrez, lot4_numero, lot4_surface_carrez::DOUBLE AS lot4_surface_carrez, lot5_numero, lot5_surface_carrez::DOUBLE AS lot5_surface_carrez, nombre_lots, code_type_local, type_local, surface_reelle_bati::DOUBLE AS surface_reelle_bati, nombre_pieces_principales, code_nature_culture, nature_culture, code_nature_culture_speciale, nature_culture_speciale, surface_terrain::DOUBLE AS surface_terrain, longitude::DOUBLE AS longitude, latitude::DOUBLE AS latitude, geometry FROM 'https://object.data.gouv.fr/dataeng-open/dvf.parquet' WHERE code_departement = '59') TO '/tmp/departement-59.geojson'
WITH (FORMAT gdal, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES', SRS 'EPSG:4326');"

# Une commune filtrée par date
duckdb :memory: "INSTALL spatial;LOAD spatial;COPY (SELECT id_mutation, date_mutation, numero_disposition, nature_mutation, valeur_fonciere::DOUBLE AS valeur_fonciere, adresse_numero, adresse_suffixe, adresse_nom_voie, adresse_code_voie, code_postal, code_commune, nom_commune, code_departement, ancien_code_commune, ancien_nom_commune, id_parcelle, ancien_id_parcelle, numero_volume, lot1_numero, lot1_surface_carrez::DOUBLE AS lot1_surface_carrez, lot2_numero, lot2_surface_carrez::DOUBLE AS lot2_surface_carrez, lot3_numero, lot3_surface_carrez::DOUBLE AS lot3_surface_carrez, lot4_numero, lot4_surface_carrez::DOUBLE AS lot4_surface_carrez, lot5_numero, lot5_surface_carrez::DOUBLE AS lot5_surface_carrez, nombre_lots, code_type_local, type_local, surface_reelle_bati::DOUBLE AS surface_reelle_bati, nombre_pieces_principales, code_nature_culture, nature_culture, code_nature_culture_speciale, nature_culture_speciale, surface_terrain::DOUBLE AS surface_terrain, longitude::DOUBLE AS longitude, latitude::DOUBLE AS latitude, geometry FROM 'https://object.data.gouv.fr/dataeng-open/dvf.parquet' WHERE code_commune = '44109' AND date_mutation BETWEEN '2023-08-12'::DATE AND '2024-02-03'::DATE) TO '/tmp/44109-by-date.geojson'
WITH (FORMAT gdal, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES', SRS 'EPSG:4326');"

# Filtrage pour une année
duckdb :memory: "INSTALL spatial;LOAD spatial;COPY (SELECT id_mutation, date_mutation, numero_disposition, nature_mutation, valeur_fonciere::DOUBLE AS valeur_fonciere, adresse_numero, adresse_suffixe, adresse_nom_voie, adresse_code_voie, code_postal, code_commune, nom_commune, code_departement, ancien_code_commune, ancien_nom_commune, id_parcelle, ancien_id_parcelle, numero_volume, lot1_numero, lot1_surface_carrez::DOUBLE AS lot1_surface_carrez, lot2_numero, lot2_surface_carrez::DOUBLE AS lot2_surface_carrez, lot3_numero, lot3_surface_carrez::DOUBLE AS lot3_surface_carrez, lot4_numero, lot4_surface_carrez::DOUBLE AS lot4_surface_carrez, lot5_numero, lot5_surface_carrez::DOUBLE AS lot5_surface_carrez, nombre_lots, code_type_local, type_local, surface_reelle_bati::DOUBLE AS surface_reelle_bati, nombre_pieces_principales, code_nature_culture, nature_culture, code_nature_culture_speciale, nature_culture_speciale, surface_terrain::DOUBLE AS surface_terrain, longitude::DOUBLE AS longitude, latitude::DOUBLE AS latitude, geometry FROM 'https://object.data.gouv.fr/dataeng-open/dvf.parquet' WHERE code_commune = '44109' AND date_mutation BETWEEN '2022-01-01'::DATE AND '2022-12-31'::DATE) TO '/tmp/france-2022.geojson'
WITH (FORMAT gdal, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES', SRS 'EPSG:4326');"

Similar vizzes

Loading thumbnail…

Export DVF geolocalisées en Geoparquet

This example converts French land-registry transaction data (DVF) from compressed CSV files into a single spatially-indexed GeoParquet file. It downloads yearly datasets from data.gouv.fr, loads them into a DuckDB database, and creates a point geometry for each transaction before exporting with a Hilbert-curve spatial ordering. The script leverages DuckDB’s spatial extension, the `ST_Point` and `ST_Hilbert` functions, and parquet compression via zstd.

TThomasG77

85% match

Loading thumbnail…

Query remote Geoparquet in browser

This example demonstrates querying a remote GeoParquet file directly from a web browser using DuckDB-Wasm. The interactive map displays French priority neighborhoods (QPVs) and identifies which one contains a specific geographic point (2.389798, 48.909514). It uses DuckDB-Wasm’s SQL engine with the spatial extension to run an `ST_contains` query, filtering results by geometric intersection. The workflow converts the matched polygons to GeoJSON via the GDAL driver and exports them as a downloadable file. The dataset was prepared from a government GeoJSON source using DuckDB and gpio utilities.

TThomasG77

74% match

Loading thumbnail…

Get area stats for parcelles and batiments for each parcelle

This example maps French cadastral data for the commune 91114, calculating the built surface area of buildings (batiments) on each land parcel (parcelle). It uses DuckDB’s spatial SQL functions—`ST_intersects`, `ST_intersection`, and `ST_Area`—to query a remote GeoParquet file from data.gouv.fr, then aggregates results with a `LEFT JOIN` to include parcels without buildings. The final query outputs a CSV of per-parcelle statistics, including total building footprint and parcel geometry attributes.

TThomasG77

69% match

Loading thumbnail…

Recipe to transform latest RNA data to parquet or download data (for 2nd case, uncomment line starting with #)

This example shows how to extract the two most recent RNA datasets from the French open-data platform data.gouv.fr and convert them into compressed Parquet files for efficient analysis. It uses the data.gouv.fr API to fetch dataset metadata, filtering resources by type and sorting by creation date to select the latest files. The scripts then leverage DuckDB’s `read_csv` function with the community `zipfs` extension to read CSV files directly from remote ZIP archives, and write the result to Parquet with Zstandard compression. Equivalent implementations are provided in Bash using `curl`, `jq`, and the DuckDB CLI, and in Python using the `duckdb` library and `urllib`.

TThomasG77

66% match

Loading thumbnail…

GDAL VSICURL tests to remote BDTOPO Geopackage France

This example tests GDAL’s VSICURL virtual file system by streaming a remote GeoPackage of French administrative data, specifically focusing on mairie (town hall) geometries. It demonstrates two ogr2ogr commands that query the BDTOPO dataset using SQL joins to extract mairie footprints as multipolygons and their centroids as points, converting them to GeoJSON with EPSG:4326. The visualization shows how both polygon boundaries and point locations are derived from the same remote source, highlighting the ability to perform spatial SQL operations directly on cloud-hosted vector data without local downloads.

TThomasG77

63% match

Loading thumbnail…

Lien Adresse et parcelles

This example shows the relationship between French addresses and land parcels, producing flat CSV files per department and for the whole country from the IGN BAN PLUS dataset. The workflow uses a bash pipeline combining Python with `xml.etree.ElementTree` to fix VRT files, GDAL’s `ogr2ogr` and `ogrinfo` for data conversion and indexing, `jq` to parse layer names, and `xsv` to concatenate departmental rows into a nationwide file. Results are gzip-compressed and organized into `france` and `departements` directories.

TThomasG77

61% match

Loading thumbnail…

Copie données WFS depuis le géoportail de l'urbanisme

This example automates the bulk download of France’s urban planning WFS data from the Géoportail de l’Urbanisme, specifically extracting the GPKG files. It shows how to list available remote directories via `lftp` over SFTP, identify the latest dated export, and mirror the chosen folder with parallel transfers. The workflow uses shell commands with `timeout`, `ssh`, and `lftp` to handle authentication and file retrieval. It relies on public credentials and a documented PDF manual, making the process reproducible for users needing recent, structured land-use datasets.

TThomasG77

60% match

Loading thumbnail…

Consommation CSW Observatoire des territoires

This visualization catalogs the datasets available from the French government’s Observatoire des Territoires geospatial service. It displays a table of metadata—titles like “ZRR - Zone de revitalisation rurale” and “QPV - Quartiers prioritaires”—paired with their identifiers and direct URLs. Built with a Python script using the OWSLib library’s `CatalogueServiceWeb`, it queries the CSW endpoint, paginates through records, and exports the results to a CSV. The output is then presented in a Vue.js interface, showing the structured list of available territorial indicators.

TThomasG77

59% match