block by ThomasG77 f1a460ade7c5a27ad2fd719d055473ce

Exemples de consommation Geo DVF Parquet via Duckdb

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');"