The intersection of crowdsourced cartography and advanced business intelligence has opened new avenues for recreational planning and environmental documentation, as demonstrated by a recent project integrating OpenStreetMap (OSM) data with Microsoft Power BI. This initiative aims to map "wild swimming" spots—natural, often remote locations in rivers and mountains suitable for bathing—across Spain and Portugal. By leveraging the Overpass API and Power BI’s visualization capabilities, the project provides a framework for enthusiasts to not only discover these locations but also identify data gaps, such as missing photographic documentation, and contribute back to the global mapping community.
The Evolution of Crowdsourced Cartography and OpenStreetMap
OpenStreetMap, often described as the "Wikipedia of maps," has evolved since its inception in 2004 into the world’s most comprehensive free geographic database. Unlike proprietary mapping services, OSM relies on a global community of over 10 million registered users who contribute data about everything from local footpaths to complex transit networks. The reliability of this data is maintained through a decentralized peer-review system, where changes are immediately visible but subject to community monitoring and validation tools.
In the context of specialized recreation like wild swimming, OSM serves as a critical repository. Wild swimming, a subset of "blue health" activities—recreation centered around water—has seen a significant surge in popularity over the last decade. Studies by environmental psychologists suggest that natural water bodies provide unique psychological benefits compared to chlorinated pools. However, these locations are often poorly documented in traditional commercial maps. The use of specific "tags" in OSM allows for the categorization of these spots with high granularity, enabling users to distinguish between established swimming facilities and undeveloped natural bathing places.
Technical Framework: Nodes, Ways, and Relations
To extract meaningful data for a visualization project, one must first understand the hierarchical structure of OSM. The database is built upon three fundamental primitives:
- Nodes: These represent specific points on the Earth’s surface, defined by latitude and longitude. In the wild swimming project, a node might represent a small diving spot or a specific entry point into a river.
- Ways: These are ordered lists of nodes that form polylines (for roads or rivers) or polygons (for buildings or lakes). A swimming area that covers a specific section of a lake is often mapped as a "way" that forms a closed loop.
- Relations: These are complex structures that group multiple nodes and ways to represent entities like bus routes or administrative boundaries.
The descriptive power of OSM comes from its "tags," which are key-value pairs (e.g., leisure=bathing_place). For this project, the primary tags of interest include name, description, leisure, and image. The leisure tag is particularly vital, as the community distinguishes between leisure=swimming_area (enclosed natural water areas often inside a facility) and leisure=bathing_place (natural water bodies used for public bathing without formal facilities).
Querying the Landscape: The Overpass API and Overpass QL
Retrieving specific datasets from the billions of objects in the OSM database requires the Overpass API, a read-only API optimized for selective data extraction. Users interact with this API using Overpass Query Language (Overpass QL), a C-style syntax that allows for complex spatial filtering.
For the Iberian Peninsula project, the query logic was designed to filter for bathing and swimming tags within the geographic boundaries of Spain (ISO code ES) and Portugal (ISO code PT). The query utilizes the nwr (nodes, ways, relations) command to ensure all relevant geometries are captured. A critical component of the query is the out center modifier; because Power BI’s mapping visuals typically require a single point of latitude and longitude, this modifier calculates the geometric center of any ways or relations, converting areas into manageable points for visualization.
To facilitate easier integration with Power BI, the query can be set to output data in CSV (Comma-Separated Values) format. This tabular structure is more efficient for Business Intelligence (BI) tools than the default XML or JSON formats, as it minimizes the transformation steps required during the data ingestion phase.
Data Integration and Transformation in Power BI
Power BI serves as the analytical engine for this project, transforming raw geographic data into an interactive dashboard. The integration process follows a standard ETL (Extract, Transform, Load) workflow:
Extraction via Web Connector
The "Web" data source in Power BI is used to connect directly to the Overpass API interpreter. By using the "Advanced" mode, developers can break down the API URL into constituent parts, making the query easier to maintain and update. This direct connection ensures that the report can be refreshed to reflect the most recent updates made to the OSM database.
Transformation in Power Query
Once the data is ingested, the Power Query Editor is used to clean the dataset. This includes promoting the first row to headers and ensuring that latitude and longitude columns are recognized as decimal numbers. A key custom transformation in this project involves the creation of a conditional column to check for the presence of the image tag. Using a simple DAX (Data Analysis Expressions) or Power Query M-code statement—if [image] <> "" then "With Photo" else "Without Photo"—the dataset is segmented to highlight areas where photographic documentation is lacking.
Geospatial Visualization
The final visualization utilizes the Azure Maps visual within Power BI. By plotting the locations and color-coding them based on the presence of photos, the map becomes a tool for "data activism." Users can quickly identify clusters of swimming spots that have never been photographed, providing a clear roadmap for future excursions aimed at documenting these sites.
The Role of Tooltips and Photographic Documentation
A sophisticated feature of the Power BI dashboard is the use of custom report page tooltips. When a user hovers over a mapped location, a secondary hidden page appears, displaying the site’s name, description, and a dynamically loaded image from the URL stored in the OSM image tag.
However, photo linking remains a subject of debate within the OSM community. Unlike Wikimedia Commons, OSM does not host image files directly. Mappers must link to externally hosted images. The project highlights a common challenge: commercial services like Google Photos often provide links to viewing pages rather than direct image files, which are required for Power BI visuals and preferred by OSM guidelines. This necessitates the use of independent web hosting or dedicated open-image platforms to ensure long-term data accessibility.
Analysis of Implications and Broader Impact
The integration of OSM and Power BI represents a significant shift in how public data is consumed and utilized. There are several broader implications for this technical approach:
1. Democratization of GIS
Historically, Geographic Information Systems (GIS) required expensive software and specialized training. The ability to pull live data from OSM into a standard business tool like Power BI lowers the barrier to entry for local governments, non-profits, and hobbyists to perform sophisticated spatial analysis.
2. Community-Driven Tourism and Conservation
By documenting wild swimming spots, these tools can help distribute tourism more evenly across rural areas, supporting local economies in the Iberian interior. Conversely, increased visibility can lead to over-tourism, highlighting the need for "responsible mapping" where sensitive environmental data is handled with care.
3. Data Integrity and "The Power of the Crowd"
The project serves as a feedback loop. When a user finds an error in the Power BI report, they are encouraged to log into OSM and correct the source data. This "citizen science" approach ensures that the map improves over time, benefiting all third-party applications that rely on OSM data, from navigation apps to emergency response services.
Chronology of the Project Development
- Conceptualization: Identification of the need for a unified map of natural bathing spots in Iberia.
- Data Sourcing: Evaluation of OSM tags and the selection of
leisure=bathing_placeandleisure=swimming_areaas the primary filters. - Query Development: Testing of Overpass QL scripts in the Overpass Turbo web interface to refine geographic boundaries.
- BI Integration: Establishing the live connection between the Overpass API and Power BI Desktop.
- Visual Enhancement: Development of Azure Maps layers and custom tooltip pages for image rendering.
- Community Contribution: Ongoing phase where the developer and users upload photos to independent servers and update the
imagetags on OSM.
Official Responses and Community Standards
While there is no single "official" body governing the use of OSM in BI tools, the OpenStreetMap Foundation (OSMF) provides clear guidelines on data attribution. Any project using this data must credit OpenStreetMap contributors, a standard strictly adhered to in professional implementations.
Technically, Microsoft has continued to improve Azure Maps integration within Power BI, though users have noted occasional bugs regarding filter synchronization. Industry analysts suggest that as more organizations move toward "Location Intelligence," the demand for seamless integration between open-source spatial data and proprietary analytical tools will only increase.
The Iberian wild swimming project stands as a practical proof-of-concept for this integration. It moves beyond simple data consumption, creating a functional ecosystem where data is extracted, visualized, and ultimately improved through community participation. This model provides a blueprint for similar initiatives in fields ranging from urban planning to environmental monitoring.
