{"id":1384,"date":"2026-10-23T07:49:20","date_gmt":"2026-10-23T07:49:20","guid":{"rendered":"https:\/\/helpwithmygisassignment.com\/blog\/?p=1384"},"modified":"2026-10-23T07:49:32","modified_gmt":"2026-10-23T07:49:32","slug":"query-openstreetmap-data-python-duckdb","status":"publish","type":"post","link":"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/","title":{"rendered":"Querying OpenStreetMap Data at Scale with Python and DuckDB"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_89 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Querying_OpenStreetMap_Data_at_Scale_with_Python_and_DuckDB\" >Querying OpenStreetMap Data at Scale with Python and DuckDB<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#1_Introduction_Why_Combine_OpenStreetMap_with_DuckDB\" >1. Introduction: Why Combine OpenStreetMap with DuckDB?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#2_Setup_and_Prerequisites\" >2. Setup and Prerequisites<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#21_Install_Python_libraries\" >2.1 Install Python libraries<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#22_Recommended_directory_structure\" >2.2 Recommended directory structure<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#23_Install_and_load_the_spatial_extension\" >2.3 Install and load the spatial extension<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#3_Step-by-Step_Code_Walkthrough\" >3. Step-by-Step Code Walkthrough<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Step_1_Download_a_regional_OpenStreetMap_PBF_extract\" >Step 1: Download a regional OpenStreetMap PBF extract<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Step_2_Query_raw_PBF_elements_directly_using_DuckDB\" >Step 2: Query raw PBF elements directly using DuckDB<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Extract_a_particular_tag_category\" >Extract a particular tag category<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Step_3_Extract_geometries_with_Pyrosm\" >Step 3: Extract geometries with Pyrosm<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Step_4_Write_reusable_GeoParquet_layers\" >Step 4: Write reusable GeoParquet layers<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Step_5_Query_large_vector_layers_using_optimized_spatial_SQL\" >Step 5: Query large vector layers using optimized spatial SQL<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Query_A_Count_buildings_by_classification\" >Query A: Count buildings by classification<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Query_B_Extract_buildings_inside_a_bounding_box\" >Query B: Extract buildings inside a bounding box<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Query_C_Extract_primary_road_classes\" >Query C: Extract primary road classes<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Query_D_Summarize_amenities_by_type\" >Query D: Summarize amenities by type<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Step_6_Query_the_raw_PBF_directly_for_point_amenities\" >Step 6: Query the raw PBF directly for point amenities<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#Step_7_Export_filtered_layers_to_GeoParquet_and_GeoPackage\" >Step 7: Export filtered layers to GeoParquet and GeoPackage<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-20\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#4_Performance_Optimization_Strategies\" >4. Performance Optimization Strategies<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-21\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#5_Benchmark_Comparison_DuckDB_PBF_Parsers_and_Desktop_GIS\" >5. Benchmark Comparison: DuckDB, PBF Parsers, and Desktop GIS<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-22\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#How_to_benchmark_DuckDB_against_a_desktop_GIS\" >How to benchmark DuckDB against a desktop GIS<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-23\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/#6_Summary\" >6. Summary<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"Querying_OpenStreetMap_Data_at_Scale_with_Python_and_DuckDB\"><\/span>Querying OpenStreetMap Data at Scale with Python and DuckDB<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<h2><span class=\"ez-toc-section\" id=\"1_Introduction_Why_Combine_OpenStreetMap_with_DuckDB\"><\/span>1. Introduction: Why Combine OpenStreetMap with DuckDB?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-1528\" src=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_y1npj7y1npj7y1np-1024x457.jpg\" alt=\"Query OpenStreetMap Data Python DuckDB workflow showing spatial map layers and code editor\" width=\"1024\" height=\"457\" srcset=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_y1npj7y1npj7y1np-1024x457.jpg 1024w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_y1npj7y1npj7y1np-300x134.jpg 300w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_y1npj7y1npj7y1np-768x343.jpg 768w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_y1npj7y1npj7y1np-1536x686.jpg 1536w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_y1npj7y1npj7y1np.jpg 1541w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/p>\n<p>To <b data-path-to-node=\"2,0,1,0,1,0\" data-index-in-node=\"4\">Query OpenStreetMap Data Python DuckDB<\/b> effectively, regional extracts provide detailed, openly accessible geographic data covering roads, buildings, and amenities. OpenStreetMap (OSM) provides detailed, openly accessible geographic data covering roads, buildings, amenities, land use, and countless other geographic features. For large-scale GIS projects, however, obtaining the data is only the beginning.<\/p>\n<p>Regional <code>.osm.pbf<\/code> files can contain millions of nodes, ways, and relations. Extracting every building in a country, selecting road classes across an entire region, or counting amenities by category can place substantial demands on memory and processing time when handled through traditional <a href=\"https:\/\/helpwithmygisassignment.com\/blog\/building-real-time-spatial-dashboards-in-qgis-with-geonode-and-postgis\/\">desktop GIS<\/a> workflows.<\/p>\n<p>A typical desktop workflow involves opening an OSM extract, converting it into usable geometry layers, applying filters, and exporting the resulting data. Repeating those operations for multiple study areas can be inefficient, particularly when the same source data supports dozens of different analyses.<\/p>\n<p>Python and DuckDB offer a more flexible approach.<\/p>\n<ul>\n<li><strong>Python and Pyrosm<\/strong> parse OSM PBF files into meaningful geographic feature layers, including roads, building footprints, and points of interest.<\/li>\n<li><strong>DuckDB<\/strong> executes SQL queries locally, using columnar processing, parallel execution, and spatial functions to filter, aggregate, transform, and export large datasets.<\/li>\n<li><strong>GeoParquet<\/strong> provides a reusable, compressed format for subsequent queries without repeatedly parsing the original PBF file.<\/li>\n<\/ul>\n<p>DuckDB also has a native <code>ST_ReadOSM()<\/code> table function that reads compressed OSM PBF files directly. It uses multithreaded parsing, but returns raw OSM elements rather than automatically constructing building polygons or road geometries. For complete geometric layers, a PBF extraction tool such as Pyrosm is often the more straightforward solution.<\/p>\n<p><strong>The central principle:<\/strong> use an OSM-aware parser for feature reconstruction where necessary, then use DuckDB for repeated analytical queries over the resulting spatial data.<\/p>\n<p>This distinction matters for performance. Filtering a prepared GeoParquet dataset may be very fast, but parsing a multi-gigabyte PBF and reconstructing its geometries can still take minutes or longer, depending on the data, hardware, and extraction requirements.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"2_Setup_and_Prerequisites\"><\/span>2. Setup and Prerequisites<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"21_Install_Python_libraries\"><\/span>2.1 Install Python libraries<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Use Python 3.10 or later in a dedicated virtual environment. The following installation provides DuckDB, Pyrosm, GeoPandas, PyArrow, and the HTTP client used for downloading data.<\/p>\n<pre><code>python -m venv .venv\r\n\r\n# Windows\r\n.venv\\Scripts\\activate\r\n\r\n# Linux or macOS\r\nsource .venv\/bin\/activate\r\n\r\npython -m pip install --upgrade pip\r\npython -m pip install duckdb pyrosm geopandas pyarrow requests<\/code><\/pre>\n<p>Pyrosm reads local OSM PBF files and converts selected feature classes into GeoPandas GeoDataFrames. DuckDB provides the SQL execution engine, while GeoPandas and PyArrow support spatial data preparation and GeoParquet interoperability.<\/p>\n<p>For a production environment, record package versions in a requirements file or lockfile so that future runs can reproduce the same software environment.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"22_Recommended_directory_structure\"><\/span>2.2 Recommended directory structure<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Organize the workflow into separate locations for source data, intermediate layers, and final outputs.<\/p>\n<pre><code>osm-duckdb-project\/\r\n\u251c\u2500\u2500 data\/\r\n\u2502   \u251c\u2500\u2500 kenya-latest.osm.pbf\r\n\u2502   \u251c\u2500\u2500 buildings.parquet\r\n\u2502   \u251c\u2500\u2500 roads.parquet\r\n\u2502   \u2514\u2500\u2500 pois.parquet\r\n\u251c\u2500\u2500 output\/\r\n\u2502   \u251c\u2500\u2500 nairobi_buildings.parquet\r\n\u2502   \u2514\u2500\u2500 nairobi_buildings.gpkg\r\n\u251c\u2500\u2500 osm_analysis.duckdb\r\n\u251c\u2500\u2500 requirements.txt\r\n\u2514\u2500\u2500 process_osm.py<\/code><\/pre>\n<p>The downloaded PBF remains the source of truth, while the GeoParquet files become reusable analytical datasets. The DuckDB database file is optional; DuckDB can also query files directly without importing every record into a permanent database table.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"23_Install_and_load_the_spatial_extension\"><\/span>2.3 Install and load the spatial extension<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>DuckDB&#8217;s spatial extension supplies functions for geometric operations and GDAL-based vector file input and output. Install it once and load it whenever a connection needs the spatial functions.<\/p>\n<pre><code>import duckdb\r\n\r\n# Create or open a persistent local database.\r\ncon = duckdb.connect(\"osm_analysis.duckdb\")\r\n\r\n# Download the extension if it is not already installed.\r\ncon.execute(\"INSTALL spatial\")\r\n\r\n# Load spatial functions for this connection.\r\ncon.execute(\"LOAD spatial\")\r\n\r\n# Adjust these values to match the available machine resources.\r\ncon.execute(\"SET threads = 8\")\r\ncon.execute(\"SET memory_limit = '8GB'\")\r\n\r\nprint(con.execute(\"SELECT version()\").fetchone()[0])<\/code><\/pre>\n<p>The example assumes a machine with enough resources for eight threads and an 8 GB DuckDB memory limit. Adjust the settings rather than copying them blindly onto a smaller computer.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"3_Step-by-Step_Code_Walkthrough\"><\/span>3. Step-by-Step Code Walkthrough<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Step_1_Download_a_regional_OpenStreetMap_PBF_extract\"><\/span>Step 1: Download a regional OpenStreetMap PBF extract<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Use regional extracts from <a href=\"https:\/\/download.geofabrik.de\/\" target=\"_blank\" rel=\"noopener\">Geofabrik&#8217;s OpenStreetMap download portal<\/a>. Select a region that matches the study area and download its <code>.osm.pbf<\/code> file.<\/p>\n<p>For example, Kenya&#8217;s extract follows the regional naming convention:<\/p>\n<p><code>https:\/\/download.geofabrik.de\/africa\/kenya-latest.osm.pbf<\/code><\/p>\n<p>Download it programmatically using streaming so that the entire compressed file is not unnecessarily loaded into Python memory.<\/p>\n<pre><code>from pathlib import Path\r\n\r\nimport requests\r\n\r\nDATA_DIR = Path(\"data\")\r\nDATA_DIR.mkdir(parents=True, exist_ok=True)\r\n\r\nPBF_URL = (\r\n    \"https:\/\/download.geofabrik.de\/africa\/\"\r\n    \"kenya-latest.osm.pbf\"\r\n)\r\n\r\nPBF_PATH = DATA_DIR \/ \"kenya-latest.osm.pbf\"\r\n\r\n\r\ndef download_pbf(url: str, destination: Path) -&gt; None:\r\n    \"\"\"Download a PBF file in chunks and fail on HTTP errors.\"\"\"\r\n\r\n    if destination.exists() and destination.stat().st_size &gt; 0:\r\n        print(f\"Using existing file: {destination}\")\r\n        return\r\n\r\n    partial_path = destination.with_suffix(\r\n        destination.suffix + \".part\"\r\n    )\r\n\r\n    try:\r\n        with requests.get(\r\n            url,\r\n            stream=True,\r\n            timeout=(30, 600),\r\n        ) as response:\r\n            response.raise_for_status()\r\n\r\n            with partial_path.open(\"wb\") as file:\r\n                for chunk in response.iter_content(\r\n                    chunk_size=1024 * 1024\r\n                ):\r\n                    if chunk:\r\n                        file.write(chunk)\r\n\r\n        # Publish the completed download only after success.\r\n        partial_path.replace(destination)\r\n\r\n    except Exception:\r\n        partial_path.unlink(missing_ok=True)\r\n        raise\r\n\r\n    print(\r\n        f\"Downloaded {destination}: \"\r\n        f\"{destination.stat().st_size \/ 1e9:.2f} GB\"\r\n    )\r\n\r\n\r\ndownload_pbf(PBF_URL, PBF_PATH)<\/code><\/pre>\n<p>The downloader streams the response in 1 MB chunks and removes an incomplete temporary file if the request fails. It does not automatically resume an interrupted download; for very large extracts, a download manager or a verified command-line downloader may be preferable.<\/p>\n<p>Regional extracts are periodically updated, so retain the download date and source URL as part of the processing metadata. The Geofabrik index provides the current extract list and related metadata.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_2_Query_raw_PBF_elements_directly_using_DuckDB\"><\/span>Step 2: Query raw PBF elements directly using DuckDB<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Before reconstructing roads or polygons, DuckDB can inspect the raw OSM structure directly.<\/p>\n<p>Its <code>ST_ReadOSM()<\/code> function exposes eight principal fields:<\/p>\n<table>\n<tbody>\n<tr>\n<th>Field<\/th>\n<th>Meaning<\/th>\n<\/tr>\n<tr>\n<td><code>kind<\/code><\/td>\n<td>Element type: node, way, relation, or changeset<\/td>\n<\/tr>\n<tr>\n<td><code>id<\/code><\/td>\n<td>OSM element identifier<\/td>\n<\/tr>\n<tr>\n<td><code>tags<\/code><\/td>\n<td>Map of OSM tag names and values<\/td>\n<\/tr>\n<tr>\n<td><code>refs<\/code><\/td>\n<td>Referenced element IDs<\/td>\n<\/tr>\n<tr>\n<td><code>lat<\/code><\/td>\n<td>Node latitude<\/td>\n<\/tr>\n<tr>\n<td><code>lon<\/code><\/td>\n<td>Node longitude<\/td>\n<\/tr>\n<tr>\n<td><code>ref_roles<\/code><\/td>\n<td>Roles of relation members<\/td>\n<\/tr>\n<tr>\n<td><code>ref_types<\/code><\/td>\n<td>Types of relation members<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The important limitation is that ways and relations do not automatically arrive as completed line or polygon geometries. Their references must be resolved against other elements to construct those geometries.<\/p>\n<p>Create a view over the PBF so that raw elements can be queried using SQL.<\/p>\n<pre><code>from pathlib import Path\r\nimport duckdb\r\n\r\ncon = duckdb.connect(\"osm_analysis.duckdb\")\r\n\r\ncon.execute(\"INSTALL spatial\")\r\ncon.execute(\"LOAD spatial\")\r\n\r\n# Use an absolute path and safely quote the SQL string literal.\r\npbf_sql = (\r\n    str(Path(PBF_PATH).resolve())\r\n    .replace(\"\\\\\", \"\/\")\r\n    .replace(\"'\", \"''\")\r\n)\r\n\r\n# The source file is read when the view is queried.\r\ncon.execute(f\"\"\"\r\n    CREATE OR REPLACE VIEW osm_raw AS\r\n    SELECT *\r\n    FROM ST_ReadOSM('{pbf_sql}')\r\n\"\"\")\r\n\r\n# Inspect a small sample of raw elements.\r\nsample = con.execute(\"\"\"\r\n    SELECT kind, id, tags, refs, lat, lon\r\n    FROM osm_raw\r\n    LIMIT 10\r\n\"\"\").df()\r\n\r\nprint(sample)<\/code><\/pre>\n<p>For a first analytical query, count the different types of OSM elements.<\/p>\n<pre><code>SELECT\r\n    kind,\r\n    COUNT(*) AS element_count\r\nFROM osm_raw\r\nGROUP BY kind\r\nORDER BY element_count DESC;<\/code><\/pre>\n<p>This reveals the broad composition of the source extract without requiring a desktop GIS interface.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Extract_a_particular_tag_category\"><\/span>Extract a particular tag category<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>OSM stores thematic attributes as key-value tags. For example, an amenity can have a tag such as <code>amenity=school<\/code> or <code>amenity=hospital<\/code>.<\/p>\n<p>The following query counts tagged <strong>node amenities<\/strong> by category.<\/p>\n<pre><code>SELECT\r\n    tags['amenity'] AS amenity,\r\n    COUNT(*) AS feature_count\r\nFROM osm_raw\r\nWHERE kind = 'node'\r\n  AND tags['amenity'] IS NOT NULL\r\nGROUP BY tags['amenity']\r\nORDER BY feature_count DESC\r\nLIMIT 20;<\/code><\/pre>\n<p>Map access with <code>tags['amenity']<\/code> retrieves the value associated with that tag. This is much easier than manually parsing tag strings.<\/p>\n<p><strong>Important:<\/strong> this query counts only amenity nodes. Many amenities are mapped as closed ways or relations, often because they occupy a building or site. Use an OSM-aware geometry extraction workflow when the requirement is to obtain the complete amenity feature layer.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_3_Extract_geometries_with_Pyrosm\"><\/span>Step 3: Extract geometries with Pyrosm<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>For production-ready building footprints, road networks, and POIs, Pyrosm provides a more direct route from PBF to GeoDataFrames.<\/p>\n<p>Its API supports feature-specific extraction methods such as <code>get_buildings()<\/code>, <code>get_network()<\/code>, and <code>get_pois()<\/code>. The current API also documents an out-of-core engine and parallel workers for large extracts.<\/p>\n<pre><code>from pathlib import Path\r\n\r\nfrom pyrosm import OSM\r\n\r\nDATA_DIR = Path(\"data\")\r\nDATA_DIR.mkdir(exist_ok=True)\r\n\r\nPBF_PATH = DATA_DIR \/ \"kenya-latest.osm.pbf\"\r\n\r\n# Use parallel decoding for large input extracts.\r\nosm = OSM(str(PBF_PATH), workers=\"auto\")\r\n\r\n# Extract building footprints.\r\nbuildings = osm.get_buildings()\r\n\r\n# Extract a driving network with selected attributes.\r\nroads = osm.get_network(\r\n    network_type=\"driving\",\r\n    tags_to_keep=[\r\n        \"highway\",\r\n        \"name\",\r\n        \"surface\",\r\n        \"maxspeed\",\r\n        \"oneway\",\r\n        \"bridge\",\r\n        \"tunnel\",\r\n    ],\r\n)\r\n\r\n# Extract points of interest.\r\npois = osm.get_pois()\r\n\r\nprint(\"Buildings:\", len(buildings))\r\nprint(\"Road features:\", len(roads))\r\nprint(\"POIs:\", len(pois))<\/code><\/pre>\n<p>There are several reasons to use this step instead of manually building every geometry in SQL:<\/p>\n<ol start=\"1\">\n<li>Pyrosm resolves the references needed to create line and polygon features.<\/li>\n<li>It understands OSM feature-selection rules for common thematic layers.<\/li>\n<li>It returns attributes in a format that is ready for spatial analysis.<\/li>\n<li>Its out-of-core engine can reduce memory pressure on large datasets.<\/li>\n<\/ol>\n<p>For particularly large extracts, test one layer first and monitor memory usage. Even with out-of-core parsing, the final GeoDataFrame or export operation may be constrained by the number and complexity of the requested features.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_4_Write_reusable_GeoParquet_layers\"><\/span>Step 4: Write reusable GeoParquet layers<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Once the feature layers have been extracted, store them as GeoParquet. This avoids repeatedly decoding the source PBF for every new query.<\/p>\n<pre><code>from pathlib import Path\r\n\r\nDATA_DIR = Path(\"data\")\r\n\r\nlayers = {\r\n    \"buildings\": buildings,\r\n    \"roads\": roads,\r\n    \"pois\": pois,\r\n}\r\n\r\nfor layer_name, gdf in layers.items():\r\n    if gdf is None or gdf.empty:\r\n        print(f\"Skipping empty layer: {layer_name}\")\r\n        continue\r\n\r\n    if gdf.crs is None:\r\n        raise ValueError(\r\n            f\"{layer_name} has no CRS; assign the correct CRS \"\r\n            \"before exporting.\"\r\n        )\r\n\r\n    output_path = DATA_DIR \/ f\"{layer_name}.parquet\"\r\n\r\n    # GeoPandas writes spatial metadata alongside the data.\r\n    gdf.to_parquet(\r\n        output_path,\r\n        index=False,\r\n        compression=\"zstd\",\r\n    )\r\n\r\n    print(f\"Saved {layer_name}: {output_path}\")<\/code><\/pre>\n<p>GeoParquet combines Parquet&#8217;s columnar storage with geospatial metadata. DuckDB 1.5 supports GeoParquet reading and writing natively, including CRS information in normal read\/write round trips.<\/p>\n<p>This separation creates two distinct processing stages:<\/p>\n<ul>\n<li><strong>PBF parsing:<\/strong> decode OSM entities and construct feature geometries.<\/li>\n<li><strong>Analytical querying:<\/strong> filter and aggregate prepared feature layers with SQL.<\/li>\n<\/ul>\n<p>The second stage can be repeated many times without reconstructing the same geometries.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_5_Query_large_vector_layers_using_optimized_spatial_SQL\"><\/span>Step 5: Query large vector layers using optimized spatial SQL<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Load the prepared GeoParquet files as views. This keeps the files on disk and lets DuckDB read the required columns when queries execute.<\/p>\n<pre><code>from pathlib import Path\r\n\r\nimport duckdb\r\n\r\ncon = duckdb.connect(\"osm_analysis.duckdb\")\r\n\r\ncon.execute(\"INSTALL spatial\")\r\ncon.execute(\"LOAD spatial\")\r\n\r\nDATA_DIR = Path(\"data\").resolve().as_posix()\r\n\r\ncon.execute(f\"\"\"\r\n    CREATE OR REPLACE VIEW buildings AS\r\n    SELECT *\r\n    FROM read_parquet('{DATA_DIR}\/buildings.parquet')\r\n\"\"\")\r\n\r\ncon.execute(f\"\"\"\r\n    CREATE OR REPLACE VIEW roads AS\r\n    SELECT *\r\n    FROM read_parquet('{DATA_DIR}\/roads.parquet')\r\n\"\"\")\r\n\r\ncon.execute(f\"\"\"\r\n    CREATE OR REPLACE VIEW pois AS\r\n    SELECT *\r\n    FROM read_parquet('{DATA_DIR}\/pois.parquet')\r\n\"\"\")\r\n\r\nprint(\r\n    con.execute(\"SELECT COUNT(*) FROM buildings\").fetchone()[0]\r\n)<\/code><\/pre>\n<p>On older DuckDB versions, GeoParquet handling can differ. Verify your installed version and inspect the schema if the geometry column is exposed as WKB rather than DuckDB&#8217;s native geometry type.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Query_A_Count_buildings_by_classification\"><\/span>Query A: Count buildings by classification<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code>SELECT\r\n    building,\r\n    COUNT(*) AS building_count\r\nFROM buildings\r\nWHERE building IS NOT NULL\r\nGROUP BY building\r\nORDER BY building_count DESC;<\/code><\/pre>\n<p>This provides a classification summary for the extracted building layer. The actual categories depend on how buildings have been mapped in OSM; missing or unusual tag values should not be interpreted as evidence that a building does not exist.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Query_B_Extract_buildings_inside_a_bounding_box\"><\/span>Query B: Extract buildings inside a bounding box<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Suppose the study area is a rectangular extent around Nairobi. The following illustrative bounds use longitude and latitude in EPSG:4326.<\/p>\n<pre><code>SELECT\r\n    id,\r\n    building,\r\n    name,\r\n    geometry\r\nFROM buildings\r\nWHERE ST_Intersects(\r\n    geometry,\r\n    ST_MakeEnvelope(\r\n        36.60, -1.50,\r\n        37.10, -1.00\r\n    )\r\n);<\/code><\/pre>\n<p><code>ST_Intersects<\/code> performs the geometry-aware spatial test. The envelope values are only an example and should be replaced with the exact study-area coordinates.<\/p>\n<p>For larger analyses, read the geometry and only the attribute columns that are needed. Columnar formats help DuckDB avoid reading unrelated attribute columns. Parquet&#8217;s filter and projection pushdown can substantially reduce unnecessary I\/O, although the extent of the benefit depends on file layout and filter selectivity.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Query_C_Extract_primary_road_classes\"><\/span>Query C: Extract primary road classes<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code>SELECT\r\n    id,\r\n    highway,\r\n    name,\r\n    surface,\r\n    maxspeed,\r\n    geometry\r\nFROM roads\r\nWHERE highway IN (\r\n    'motorway',\r\n    'trunk',\r\n    'primary',\r\n    'secondary'\r\n);<\/code><\/pre>\n<p>This query returns the selected road classes from the prepared network layer. Because the network has already been extracted and stored, the query no longer needs to reconstruct each line from its original OSM node references.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Query_D_Summarize_amenities_by_type\"><\/span>Query D: Summarize amenities by type<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code>SELECT\r\n    amenity,\r\n    COUNT(*) AS poi_count\r\nFROM pois\r\nWHERE amenity IS NOT NULL\r\nGROUP BY amenity\r\nORDER BY poi_count DESC\r\nLIMIT 25;<\/code><\/pre>\n<p>The precise fields available depend on how the parser has extracted and normalized the tags. Use <code>DESCRIBE SELECT * FROM pois<\/code> to inspect the actual schema before relying on any particular field.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_6_Query_the_raw_PBF_directly_for_point_amenities\"><\/span>Step 6: Query the raw PBF directly for point amenities<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Not every analysis needs a separate Pyrosm extraction. For tag audits and node-only analyses, DuckDB can create point geometries directly from raw PBF node coordinates.<\/p>\n<pre><code>from pathlib import Path\r\nimport duckdb\r\n\r\ncon = duckdb.connect(\"osm_analysis.duckdb\")\r\ncon.execute(\"INSTALL spatial\")\r\ncon.execute(\"LOAD spatial\")\r\n\r\npbf_sql = (\r\n    str(Path(PBF_PATH).resolve())\r\n    .replace(\"\\\\\", \"\/\")\r\n    .replace(\"'\", \"''\")\r\n)\r\n\r\noutput_dir = Path(\"output\")\r\noutput_dir.mkdir(parents=True, exist_ok=True)\r\n\r\noutput_sql = (\r\n    str((output_dir \/ \"node_amenities.parquet\").resolve())\r\n    .replace(\"\\\\\", \"\/\")\r\n    .replace(\"'\", \"''\")\r\n)\r\n\r\ncon.execute(f\"\"\"\r\n    COPY (\r\n        SELECT\r\n            id AS osm_id,\r\n            tags['amenity'] AS amenity,\r\n            tags['name'] AS name,\r\n            ST_SetSRID(\r\n                ST_Point(lon, lat),\r\n                4326\r\n            ) AS geometry\r\n        FROM ST_ReadOSM('{pbf_sql}')\r\n        WHERE kind = 'node'\r\n          AND tags['amenity'] IS NOT NULL\r\n          AND lon IS NOT NULL\r\n          AND lat IS NOT NULL\r\n    )\r\n    TO '{output_sql}'\r\n    (FORMAT PARQUET, COMPRESSION ZSTD)\r\n\"\"\")\r\n\r\nprint(\"Exported node amenities to GeoParquet.\")<\/code><\/pre>\n<p>On DuckDB 1.5, an output containing a native geometry column is written with GeoParquet metadata.<\/p>\n<p>This method is useful for rapid exploratory analysis, but it is not a complete substitute for extracting all amenity geometries. To avoid overlooking schools, hospitals, and other amenities represented by polygons or relations, use the full POI extraction workflow when completeness matters.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Step_7_Export_filtered_layers_to_GeoParquet_and_GeoPackage\"><\/span>Step 7: Export filtered layers to GeoParquet and GeoPackage<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>GeoParquet is generally convenient for repeated analytical queries. GeoPackage is useful when the result needs to be opened in QGIS or another desktop GIS application.<\/p>\n<p>First, export a filtered building layer to GeoParquet.<\/p>\n<pre><code>from pathlib import Path\r\nimport duckdb\r\n\r\ncon = duckdb.connect(\"osm_analysis.duckdb\")\r\ncon.execute(\"INSTALL spatial\")\r\ncon.execute(\"LOAD spatial\")\r\n\r\noutput_dir = Path(\"output\")\r\noutput_dir.mkdir(exist_ok=True)\r\n\r\nparquet_path = (\r\n    output_dir \/ \"nairobi_buildings.parquet\"\r\n).resolve().as_posix().replace(\"'\", \"''\")\r\n\r\ncon.execute(f\"\"\"\r\n    COPY (\r\n        SELECT\r\n            id,\r\n            building,\r\n            name,\r\n            geometry\r\n        FROM buildings\r\n        WHERE ST_Intersects(\r\n            geometry,\r\n            ST_MakeEnvelope(\r\n                36.60, -1.50,\r\n                37.10, -1.00\r\n            )\r\n        )\r\n    )\r\n    TO '{parquet_path}'\r\n    (FORMAT PARQUET, COMPRESSION ZSTD)\r\n\"\"\")<\/code><\/pre>\n<p>Next, export the same selection to GeoPackage using DuckDB&#8217;s GDAL-based vector export.<\/p>\n<pre><code>gpkg_path = (\r\n    output_dir \/ \"nairobi_buildings.gpkg\"\r\n).resolve().as_posix().replace(\"'\", \"''\")\r\n\r\ncon.execute(f\"\"\"\r\n    COPY (\r\n        SELECT\r\n            id,\r\n            building,\r\n            name,\r\n            geometry\r\n        FROM buildings\r\n        WHERE ST_Intersects(\r\n            geometry,\r\n            ST_MakeEnvelope(\r\n                36.60, -1.50,\r\n                37.10, -1.00\r\n            )\r\n        )\r\n    )\r\n    TO '{gpkg_path}'\r\n    WITH (\r\n        FORMAT GDAL,\r\n        DRIVER 'GPKG',\r\n        LAYER_NAME 'nairobi_buildings',\r\n        SRS 'EPSG:4326'\r\n    )\r\n\"\"\")\r\n\r\nprint(\"GeoParquet and GeoPackage exports completed.\")<\/code><\/pre>\n<p>DuckDB&#8217;s GDAL-based <code>COPY<\/code> supports vector formats such as GeoPackage and allows the output layer name and spatial reference to be specified. The <code>SRS<\/code> option sets CRS metadata; it does <strong>not<\/strong> reproject coordinates, so the declared CRS must agree with the coordinates in the source geometry.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"4_Performance_Optimization_Strategies\"><\/span>4. Performance Optimization Strategies<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Large-scale vector data extraction benefits from careful control of memory, file structure, and query design.<\/p>\n<p><strong>Use column projection.<\/strong> Prefer <code>SELECT id, highway, geometry<\/code> to <code>SELECT *<\/code> when only a few fields are required.<\/p>\n<p><strong>Filter early.<\/strong> Restrict tags, road classes, or the geographic extent as early as practical. A selective attribute predicate can avoid returning a large volume of unnecessary records.<\/p>\n<p><strong>Cache prepared layers.<\/strong> Do not repeatedly parse a multi-gigabyte PBF if several analyses use the same extracted buildings or roads. Save validated GeoParquet layers and query them repeatedly.<\/p>\n<p><strong>Use parallelism appropriately.<\/strong> DuckDB supports parallel analytical execution, and Pyrosm offers parallel decoding for larger inputs. More threads are not always faster: CPU limits, disk throughput, memory pressure, and geometry complexity influence performance.<\/p>\n<p><strong>Choose the correct geometry workflow.<\/strong> Use <code>ST_ReadOSM()<\/code> for raw tag-level analysis and direct node queries. Use Pyrosm or another OSM-aware parser when complete line, polygon, or relation geometries are needed.<\/p>\n<p><strong>Inspect query plans.<\/strong> Use <code>EXPLAIN ANALYZE<\/code> to inspect a query&#8217;s execution plan and measure the time spent in different operators.<\/p>\n<pre><code>EXPLAIN ANALYZE\r\nSELECT\r\n    highway,\r\n    COUNT(*) AS road_count\r\nFROM roads\r\nWHERE highway IN ('primary', 'secondary')\r\nGROUP BY highway;<\/code><\/pre>\n<p>This is more informative than judging performance based only on whether a query appears fast in a terminal.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"5_Benchmark_Comparison_DuckDB_PBF_Parsers_and_Desktop_GIS\"><\/span>5. Benchmark Comparison: DuckDB, PBF Parsers, and Desktop GIS<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-1529\" src=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_gv7jswgv7jswgv7j-1024x572.jpg\" alt=\"Query OpenStreetMap Data Python DuckDB performance comparison using DuckDB spatial SQL and GeoParquet\" width=\"1024\" height=\"572\" srcset=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_gv7jswgv7jswgv7j-1024x572.jpg 1024w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_gv7jswgv7jswgv7j-300x167.jpg 300w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_gv7jswgv7jswgv7j-768x429.jpg 768w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_gv7jswgv7jswgv7j.jpg 1376w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/p>\n<p>Performance comparisons must distinguish PBF decoding, geometry reconstruction, SQL querying, and final export. They are different workloads and should not be treated as equivalent.<\/p>\n<p>A useful point of reference is the Pyrosm project&#8217;s June 2026 benchmarking study. It compared multiple OSM extraction tools using a MacBook Air M5 with 24 GB of RAM and a 10-core processor. In a matched building-extraction workload that retained the building geometry and <code>building<\/code> tag, the study reported the following median times:<\/p>\n<table>\n<tbody>\n<tr>\n<th>Input extract<\/th>\n<th>Pyrosm out-of-core<\/th>\n<th>QuackOSM (DuckDB-based)<\/th>\n<\/tr>\n<tr>\n<td>Finland, approximately 726 MB<\/td>\n<td>28 seconds<\/td>\n<td>42 seconds<\/td>\n<\/tr>\n<tr>\n<td>Spain, approximately 1.46 GB<\/td>\n<td>54 seconds<\/td>\n<td>77 seconds<\/td>\n<\/tr>\n<tr>\n<td>South America, approximately 4.03 GB<\/td>\n<td>221 seconds<\/td>\n<td>313 seconds<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>These are published benchmark results for a particular extraction workload, hardware configuration, and set of software versions. QuackOSM is a DuckDB-based OSM extraction tool; these numbers are <strong>not<\/strong> a direct benchmark of an arbitrary SQL query using DuckDB&#8217;s <code>ST_ReadOSM()<\/code> function, nor are they a comparison against QGIS.<\/p>\n<p>The same study found that Pyrosm&#8217;s out-of-core engine remained practical on very large extracts, while performance varied among tools and workloads. It also emphasized the importance of matching selected tags and output geometries for fair comparisons.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"How_to_benchmark_DuckDB_against_a_desktop_GIS\"><\/span>How to benchmark DuckDB against a desktop GIS<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>A fair local comparison should use the same prepared input layer, exact filter, hardware, and output format.<\/p>\n<p>For example, time a selective road query in DuckDB:<\/p>\n<pre><code>from statistics import median\r\nfrom time import perf_counter\r\n\r\nquery = \"\"\"\r\n    SELECT COUNT(*)\r\n    FROM roads\r\n    WHERE highway IN ('primary', 'secondary')\r\n\"\"\"\r\n\r\n# Warm up the query once, then time repeated executions.\r\ncon.execute(query).fetchone()\r\n\r\nelapsed_seconds = []\r\n\r\nfor _ in range(5):\r\n    start = perf_counter()\r\n    con.execute(query).fetchone()\r\n    elapsed_seconds.append(perf_counter() - start)\r\n\r\nprint(\"Individual runs:\", elapsed_seconds)\r\nprint(\"Median seconds:\", median(elapsed_seconds))<\/code><\/pre>\n<p>Then repeat the equivalent attribute filter in QGIS using the same prepared dataset and measure the processing operation independently. If you compare file exports, time the export in both environments as well. For a cold-read comparison, use a controlled procedure for managing operating-system and filesystem caches; otherwise, identify the results explicitly as warm-cache measurements.<\/p>\n<table>\n<tbody>\n<tr>\n<th>Workflow stage<\/th>\n<th>DuckDB + Python<\/th>\n<th>Desktop GIS<\/th>\n<\/tr>\n<tr>\n<td>Raw PBF decoding<\/td>\n<td><code>ST_ReadOSM()<\/code> or an external OSM-aware parser<\/td>\n<td>Depends on the chosen import\/OGR workflow<\/td>\n<\/tr>\n<tr>\n<td>Geometry reconstruction<\/td>\n<td>Pyrosm or SQL reference resolution<\/td>\n<td>Depends on the import or conversion tool<\/td>\n<\/tr>\n<tr>\n<td>Repeated attribute queries<\/td>\n<td>SQL over GeoParquet<\/td>\n<td>Attribute filtering or processing tools<\/td>\n<\/tr>\n<tr>\n<td>Spatial selections<\/td>\n<td>DuckDB spatial functions<\/td>\n<td>Native GIS spatial processing<\/td>\n<\/tr>\n<tr>\n<td>Export<\/td>\n<td><code>COPY<\/code> to GeoParquet or GDAL-supported formats<\/td>\n<td>Save\/export layer tools<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>DuckDB is especially attractive when the workload requires many repeated SQL queries, attribute aggregations, joins, and exports over large columnar datasets. A desktop GIS remains valuable for visual inspection, cartographic work, interactive editing, and workflows that benefit from its user interface.<\/p>\n<p>There is no universal, defensible claim that DuckDB will always beat QGIS by a particular factor. To make that comparison, benchmark both tools on the actual hardware and datasets involved.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"6_Summary\"><\/span>6. Summary<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Querying OpenStreetMap data at scale with Python and DuckDB is most effective when the extraction and analytical stages are designed separately.<\/p>\n<p>Use regional Geofabrik PBF files as the source data. Use <code>ST_ReadOSM()<\/code> for direct access to raw OSM tags and node coordinates, or Pyrosm to reconstruct building footprints, roads, and other complete geometric feature layers. Export the extracted layers to GeoParquet, then run repeated SQL queries with DuckDB. Finally, write the selected features to GeoParquet for continued analysis or GeoPackage for interoperability with desktop GIS.<\/p>\n<p>This workflow reduces repeated parsing, avoids unnecessary imports into a server-based spatial database, and makes large-scale vector analysis reproducible. It also sets realistic performance expectations: <strong>the key benefit is fast, repeatable analytical querying over prepared spatial data\u2014not a guarantee that every country-sized PBF extraction will finish in seconds.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Querying OpenStreetMap Data at Scale with Python and DuckDB 1. Introduction: Why Combine OpenStreetMap with DuckDB? To Query OpenStreetMap Data Python DuckDB effectively, regional extracts provide detailed, openly accessible geographic data covering roads, buildings, and amenities. OpenStreetMap (OSM) provides detailed, openly accessible geographic data covering roads, buildings, amenities, land use, and countless other geographic features. &#8230; <a title=\"Querying OpenStreetMap Data at Scale with Python and DuckDB\" class=\"read-more\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/query-openstreetmap-data-python-duckdb\/\" aria-label=\"Read more about Querying OpenStreetMap Data at Scale with Python and DuckDB\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":1529,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,4],"tags":[],"class_list":["post-1384","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized","category-gis-articles"],"_links":{"self":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts\/1384","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/comments?post=1384"}],"version-history":[{"count":3,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts\/1384\/revisions"}],"predecessor-version":[{"id":1531,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts\/1384\/revisions\/1531"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/media\/1529"}],"wp:attachment":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/media?parent=1384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/categories?post=1384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/tags?post=1384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}