Show data points based on bounding box

[rudibroekhuizen]

I want to show markers on a map, but only the datapoints that exist in the selected area. The dataset is stored in Clickhouse and consists of 10 million records. I only want the query to return records based on the bouding box of the map. The following code works, I can see that the data is read from the database.

import folium
import streamlit as st
import pandas as pd
import clickhouse_connect
from streamlit_folium import folium_static
from streamlit_folium import st_folium
import os

# ClickHouse config
clickhouse_host = os.getenv('CLICKHOUSE_HOST')
clickhouse_user = os.getenv('CLICKHOUSE_USER')
clickhouse_pass = os.getenv('CLICKHOUSE_PASSWORD')
client = clickhouse_connect.get_client(host=clickhouse_host, port=8123, user=clickhouse_user, password=clickhouse_pass, database='db1')

def get_data_in_bbox(bbox):
    parameters = (bbox[0], bbox[2], bbox[1], bbox[3])
    result = client.query('SELECT decimallatitude, decimallongitude FROM gbif_enriched_mv WHERE decimallatitude BETWEEN %s AND %s AND decimallongitude BETWEEN %s AND %s LIMIT 100', parameters=parameters)
    return result.result_rows

m = folium.Map()

map_a = st_folium(m,
   width=800,
   height=400
   )

# Get bounding box of selected area
bounds = map_a["bounds"]
south = bounds["_southWest"]["lat"]
west = bounds["_southWest"]["lng"]
north = bounds["_northEast"]["lat"]
east = bounds["_northEast"]["lng"]
bbox = [south, west, north, east]

# Retrieve datapoints in bouding box using function get_data_in_bbox
datapoints = get_data_in_bbox(bbox)
print(datapoints)

# Add to existing map, this part doesn't work
for data_point in datapoints:
    folium.Marker(
        location=data_point
    ).add_to(m)

I can pass the data points to a second map using a featuregroup, that works:

I want to be able to zoom in and out and see the corresponding data points in that area, using 1 map.

Versions:
Python 3.10.12
folium 0.15.1
streamlit-folium 0.18.0

[rudibroekhuizen]

This can be achieved using st.session_state. After initiating the map, the bounds can be read from st.session_state, like this:

import folium
import streamlit as st
import pandas as pd
import clickhouse_connect
from streamlit_folium import st_folium
import os

# ClickHouse configurations
clickhouse_host = os.getenv('CLICKHOUSE_HOST')
clickhouse_user = os.getenv('CLICKHOUSE_USER')
clickhouse_pass = os.getenv('CLICKHOUSE_PASSWORD')
client = clickhouse_connect.get_client(host=clickhouse_host, port=8123, user=clickhouse_user, password=clickhouse_pass, database='ndor')


def get_data_in_bbox(bbox):
    parameters = (bbox[0], bbox[2], bbox[1], bbox[3], bbox[0], bbox[2], bbox[1], bbox[3])
    with open('query.sql', 'r') as file:
        sql_query = file.read()
    result = client.query(sql_query, parameters=parameters)
    return result.result_rows

st.set_page_config(
    page_title="streamlit-folium",
    page_icon=":world_map:~O",
    layout="wide"
)

m_a = folium.Map()

START_BOUNDS = '''
{'_southWest': {'lat': -90, 'lng': -255}, '_northEast': {'lat': 90, 'lng': 255}}
'''

session_keys = list(st.session_state.keys())

try:
    first_bounds = st.session_state[session_keys[0]]['bounds']
except Exception as e:
    first_bounds = START_BOUNDS

try:
    south = first_bounds['_southWest']['lat']
except Exception as e:
    south = -80
try:
    west = first_bounds['_southWest']['lng']
except Exception as e:
    west = -254
try:
    north = first_bounds['_northEast']['lat']
except Exception as e:
    north = 254
try:
    east = first_bounds['_northEast']['lng']
except Exception as e:
    east = 80

bbox = [south, west, north, east]
print(bbox)

datapoints = get_data_in_bbox(bbox)
print(datapoints)
    
fg = folium.FeatureGroup(name="Datapoints")

for data_point in datapoints: 
    folium.CircleMarker( 
        location=data_point,  
        radius=4,  
        color='cornflowerblue', 
        fill=True, 
        fill_opacity=1, 
        opacity=1, 
    ).add_to(fg) 

map_a = st_folium(m_a,
    feature_group_to_add = fg,
    width=1000,
    height=500,
    key="map_a"
)