HOT Tasking Manager project analysis

HOT Tasking Manager project analysis#

In this notebook we demonstrate how to combine two datasets: HOT Tasking Manager project data and OSM contributions.

We want to investigate all contributions that have been made in OSM via HOT’s Tasking Manager. We will furthermore filter these contributions by using the OSM Changeset information.

These are the steps you see further down:

  • Set the connection parameters.

  • Prepare your input parameters, e.g. define HOT Tasking Manager project ID.

  • Download data using DuckDB. This time we also download HOT project data.

  • Filter OSM contributions using changeset attributes.

  • Display both datasets on a map.

Getting started#

Set connection params.

from dotenv import load_dotenv
load_dotenv()

True

import os

s3_user = os.environ["S3_ACCESS_KEY_ID"]  # add your user here
s3_password = os.environ["S3_SECRET_ACCESS_KEY"]  # add your password here

Configure DuckDB.

!pip install duckdb==1.0.0

import duckdb

con = duckdb.connect(
    config={
        'threads': 8,
        'max_memory': '8GB',
        # 'enable_object_cache': True
    }
)
con.install_extension("spatial")
con.load_extension("spatial")

Set the connection params to Iceberg Rest Catalog. (We need this for the OSM data.)

from pyiceberg.catalog.rest import RestCatalog

catalog = RestCatalog(
    name="default",
    **{
        "uri": "https://sotm2024.iceberg.ohsome.org",
        "s3.endpoint": "https://sotm2024.minio.heigit.org",
        "py-io-impl": "pyiceberg.io.pyarrow.PyArrowFileIO",
        "s3.access-key-id": s3_user,
        "s3.secret-access-key": s3_password,
        "s3.region": "eu-central-1"
    }
)

Set connection to MinIO object storage. (We need this for HOT Tasking Manager data)

query = f"""
DROP SECRET IF EXISTS "__default_s3";
CREATE SECRET (
      TYPE S3,
      KEY_ID '{s3_user}',
      SECRET '{s3_password}',
      REGION 'eu-central-1',
      endpoint 'sotm2024.minio.heigit.org',
      use_ssl true,
      url_style 'path'
  );
"""
con.sql(query).show()

┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘

Prepare the input parameters for your analysis#

hot_tm_parquet_data_path = "s3a://heigit-ohsome-sotm24/data/hot_tasking_manager/**"

# Set iceberg table
namespace = 'geo_sort'
tablename = 'contributions'
icebergtable = catalog.load_table((namespace, tablename))

hot_tm_project_id = 15996

Get Information about HOT Tasking Manager project#

import geopandas as gpd

query = f"""
SELECT *
FROM read_parquet('{hot_tm_parquet_data_path}') a
WHERE project_id = {hot_tm_project_id};
"""
df = con.sql(query).df()

bbox = df["bbox"].values[0]

# convert the data to geodata
gdf = gpd.GeoDataFrame(
    df,
    geometry=gpd.GeoSeries.from_wkt(df['geometry'])
).set_crs('epsg:4326')

Display where the project is located.

import lonboard

# the lonboard map definition
hot_tm_layer = lonboard.PolygonLayer.from_geopandas(
    gdf,
    get_fill_color=[0, 0, 0, 0],
    get_line_color=[255, 0, 255],
    get_line_width=10
)

view_state = {
    "longitude": df["centroid"].values[0]["x"],
    "latitude": df["centroid"].values[0]["y"],
    "zoom": 12
}

overview_map = lonboard.Map(
    basemap_style=lonboard.basemap.CartoBasemap.Positron,
    layers=[hot_tm_layer],
    view_state=view_state
)

display(overview_map)

Download data OSM Data#

import time
start_time = time.time()


icebergtable.scan(
    row_filter=(
        f"( status = 'latest' or status = 'history' )"
        f"and (xmax >= {bbox['xmin']} and xmin <= {bbox['xmax']}) "
        f"and (ymax >= {bbox['ymin']} and ymin <= {bbox['ymax']}) "
    ),
    selected_fields=(
        "user_id",
        "osm_id",
        "osm_version",
        "valid_from",
        "valid_to",
        "tags",
        "tags_before",
        "changeset",
        "geometry",
    ),
).to_duckdb('raw_osm_data',connection=con)

download_time = round(time.time() - start_time, 3)
print(f"download took {download_time} sec.")

download took 42.551 sec.

Filter data using OSM changeset information#

query = f"""
    SELECT *
    FROM raw_osm_data
    WHERE 1=1
        and list_contains(changeset.hashtags, 'hotosm-project-{hot_tm_project_id}')
    """
osm_df = con.sql(query).df()

# convert the data to geodata
osm_gdf = gpd.GeoDataFrame(
    osm_df,
    geometry=gpd.GeoSeries.from_wkt(osm_df['geometry'])
).set_crs('epsg:4326')

Display OSM data and HOT Tasking Manager project on map.

# the lonboard map definition
osm_polygons_layer = lonboard.PolygonLayer.from_geopandas(
    osm_gdf[osm_gdf.geom_type == 'Polygon'],
    get_fill_color=[0, 0, 0, 0],
    get_line_width=1
)

osm_lines_layer = lonboard.PathLayer.from_geopandas(
    osm_gdf[osm_gdf.geom_type == 'LineString'],
)


osm_points_layer = lonboard.ScatterplotLayer.from_geopandas(
    osm_gdf[osm_gdf.geom_type == 'Point'],
)



osm_map = lonboard.Map(
    basemap_style=lonboard.basemap.CartoBasemap.Positron,
    layers=[
        hot_tm_layer,
        osm_polygons_layer,
        osm_lines_layer,
        osm_points_layer,
    ],
    view_state=view_state
)

display(osm_map)

Display OSM user statistics#

Let’s analyse who mapped how much in this area.

query = f"""
    SELECT
        user_id,
        count(*) as n_edits,
        SUM(CASE
            WHEN contains(changeset.editor, 'JOSM') THEN 1
            ELSE 0
        END) as josm_edit,
        SUM(CASE
            WHEN contains(changeset.editor, 'iD') THEN 1
            ELSE 0
        END) as iD_edit
    FROM raw_osm_data
    WHERE 1=1
        and list_contains(changeset.hashtags, 'hotosm-project-{hot_tm_project_id}')
    GROUP BY user_id
    ORDER BY n_edits DESC
    """
user_stats_df = con.sql(query).df()

We are going to add OSM usernames to our dataframe.

import requests


def get_current_usernames(user_ids_list):
    api_url = "http://textual.ru/whosthat/whosthat.php"
    user_names_list = []

    for user_id in user_ids_list:
        params = {
            "action": "info",  # Use "info" action to get detailed information
            "id": user_id,
        }
        response = requests.get(api_url, params=params)
        if response.status_code == 200:
            data = response.json()
            if data and isinstance(data, list) and len(data) > 0:
                # The API returns an array of hashes with user information
                # We need to extract the user ID from the first entry
                user_info = data[0]
                user_names_list.append( user_info['names'][-1]["name"] )

    return user_names_list


user_stats_df["username"] = get_current_usernames(user_stats_df["user_id"].values)
user_stats_df["link"] = "https://www.openstreetmap.org/user/" + user_stats_df["username"]

Display a user leaderboard.

display(user_stats_df)
user_id n_edits josm_edit iD_edit username link
0 563947 898 0.0 858.0 notoncebut2x https://www.openstreetmap.org/user/notoncebut2x
1 11010237 734 734.0 0.0 Dannyoung https://www.openstreetmap.org/user/Dannyoung
2 10570996 468 468.0 0.0 Erick tamba https://www.openstreetmap.org/user/Erick tamba
3 21292317 439 0.0 439.0 abhimanyubarun https://www.openstreetmap.org/user/abhimanyubarun
4 17340053 388 388.0 0.0 Sally Mlingi https://www.openstreetmap.org/user/Sally Mlingi
5 13722972 261 186.0 75.0 Jimerveille https://www.openstreetmap.org/user/Jimerveille
6 21288883 251 0.0 251.0 BTR2 https://www.openstreetmap.org/user/BTR2
7 19063413 245 0.0 245.0 Aczel-Divin MOUNTOU https://www.openstreetmap.org/user/Aczel-Divin...
8 20450590 167 0.0 167.0 KOGNOKO ABOULATSAMBO https://www.openstreetmap.org/user/KOGNOKO ABO...
9 9449463 150 150.0 0.0 Mercy14846 https://www.openstreetmap.org/user/Mercy14846
10 14962448 117 0.0 117.0 Abdul-Nayar Rabiatu Sungumu https://www.openstreetmap.org/user/Abdul-Nayar...
11 8386618 104 0.0 104.0 Adele Birkenes https://www.openstreetmap.org/user/Adele Birkenes
12 15320583 75 0.0 75.0 Eldablessing https://www.openstreetmap.org/user/Eldablessing
13 21292063 60 0.0 60.0 GlobalTurtle https://www.openstreetmap.org/user/GlobalTurtle
14 21304240 57 0.0 57.0 sbribri https://www.openstreetmap.org/user/sbribri
15 20155561 51 13.0 38.0 BENYE MULTI MEDIA https://www.openstreetmap.org/user/BENYE MULTI...
16 18790262 50 50.0 0.0 NzahT https://www.openstreetmap.org/user/NzahT
17 20768452 44 0.0 44.0 BAZONZILA MBANI Gloire https://www.openstreetmap.org/user/BAZONZILA M...
18 20982460 40 18.0 22.0 Jaco452 https://www.openstreetmap.org/user/Jaco452
19 21256164 33 17.0 16.0 Perey Wisdom https://www.openstreetmap.org/user/Perey Wisdom
20 21186164 31 7.0 24.0 Ziggyentumi@20 https://www.openstreetmap.org/user/Ziggyentumi@20
21 21314713 29 0.0 29.0 Maryamarab https://www.openstreetmap.org/user/Maryamarab
22 17496710 17 17.0 0.0 dellington_deli https://www.openstreetmap.org/user/dellington_...
23 21230154 15 0.0 15.0 VijayB2345 https://www.openstreetmap.org/user/VijayB2345
24 21125012 15 0.0 15.0 R237497 https://www.openstreetmap.org/user/R237497
25 16690030 15 0.0 15.0 Bouyaossere https://www.openstreetmap.org/user/Bouyaossere
26 13007399 14 0.0 14.0 elodienix https://www.openstreetmap.org/user/elodienix
27 11500943 14 0.0 14.0 Mariannamukulunga https://www.openstreetmap.org/user/Mariannamuk...
28 16739098 11 0.0 11.0 TUNGBANI FRANCIS XAVIER https://www.openstreetmap.org/user/TUNGBANI FR...
29 21366183 9 0.0 9.0 George Nkpagneer Gmagnancha https://www.openstreetmap.org/user/George Nkpa...
30 19297985 9 0.0 9.0 susan27 https://www.openstreetmap.org/user/susan27
31 15320557 8 0.0 8.0 GDC_Christie https://www.openstreetmap.org/user/GDC_Christie
32 21363124 6 0.0 6.0 Ibrahim Wunpini Sulemana https://www.openstreetmap.org/user/Ibrahim Wun...
33 10394726 5 0.0 5.0 Calvin Menttor https://www.openstreetmap.org/user/Calvin Menttor
34 21363912 4 3.0 1.0 Panladawuda https://www.openstreetmap.org/user/Panladawuda
35 21207980 2 0.0 2.0 Kenaurd OKAMA https://www.openstreetmap.org/user/Kenaurd OKAMA
36 21331501 1 0.0 1.0 DEKU PETER MEDODZI https://www.openstreetmap.org/user/DEKU PETER ...

Inspect the contributions made by the mapper with most edits.

top_mapper = user_stats_df.sort_values("n_edits")["user_id"][0]

# the lonboard map definition
osm_polygons_layer = lonboard.PolygonLayer.from_geopandas(
    osm_gdf[(osm_gdf.geom_type == 'Polygon') & (osm_gdf["user_id"] == top_mapper)],
    get_fill_color=[0, 0, 0, 0],
    get_line_width=1
)

osm_lines_layer = lonboard.PathLayer.from_geopandas(
    osm_gdf[(osm_gdf.geom_type == 'LineString') & (osm_gdf["user_id"] == top_mapper)],
)


osm_points_layer = lonboard.ScatterplotLayer.from_geopandas(
    osm_gdf[(osm_gdf.geom_type == 'Point') & (osm_gdf["user_id"] == top_mapper)],
)



osm_map = lonboard.Map(
    basemap_style=lonboard.basemap.CartoBasemap.Positron,
    layers=[
        hot_tm_layer,
        osm_polygons_layer,
        osm_lines_layer,
        osm_points_layer,
    ],
    view_state=view_state
)

display(osm_map)
Contents