pull_data.py

import geopandas as gpd
import os 
from ssr_tools.gen_utilities.gen_utilities import * 
import requests
import pandas as pd
import pyproj
import plotly.express as px
import nbformat
import plotly.io as pio
import numpy as np
from shapely.geometry import Polygon
from string import punctuation

pd.set_option('display.max_columns', None)
pd.set_option('display.width', 500)
pd.set_option('display.max_rows', 150)

ssrc_pal = {
    'ssrc_blue': '#002060', 
    'ssrc_liblue': '#AED5E7',
    'ssrc_richblue': '#1F285C',
    'ssrc_coolblue': '#3099BF',
    'ssrc_gold':'#E6B222',
    'ssrc_magenta': "#B626B5",
    'ssrc_grey': '#495057'
}


# this works
#pull dispatch calls manually between a time range

verbose = True
url = 'https://data.seattle.gov/resource/kzjm-xkqj.json'
# url = 'https://services.arcgis.com/ZOyb2t4B0UYuYNYH/arcgis/rest/services/SDOT_Collisions_Persons/FeatureServer/0/query?outFields=*&where=1%3D1&f=geojson'
limit = 1000
offset = 0
all_data = []

# Define year range
start_year = 2010
end_year = 2025

# Construct date strings in ISO 8601 format
start_date = f"{start_year}-01-01T00:00:00.000"
end_date = f"{end_year}-12-31T23:59:59.999"

while True:
# while offset <= 500000:
    params = {
        '$limit': limit,
        '$offset': offset,
        # '$where': f"datetime between '{start_date}' and '{end_date}' and type == 'Fire in Building'"
        '$where': f"datetime between '{start_date}' and '{end_date}'"

    }
    response = requests.get(url, params=params)
    data_chunk = response.json()

    if not data_chunk: 
        break
    all_data.extend(data_chunk)
    offset += limit
    if verbose: 
        print(f"Downloaded {offset} rows")

df = pd.json_normalize(all_data)
print(f"Final row count: {len(df)}")

df['collision_time_dt'] = pd.to_datetime(df.datetime)
df['hour'] = df['collision_time_dt'].dt.hour
df['minute'] = df['collision_time_dt'].dt.minute
df['year'] = df['collision_time_dt'].dt.year
df['month'] = df['collision_time_dt'].dt.month
df['week'] = df['collision_time_dt'].dt.isocalendar().week

find_frequency(df['type'])

def get_season(month):
  x = month%12 // 3 + 1
  if x == 1:
    return "Winter"
  if x == 2:
    return "Spring"
  if x == 3:
    return "Summer"
  if x == 4:
    return "Autumn"
  else: 
    return 'idk'
 
df['season'] = df.apply(lambda x: get_season(x['month']),axis=1)

find_frequency(df['season'])

df.to_csv('api_calls.csv')

# df = pd.read_csv('api_calls.csv')

# xxx = find_frequency(df.groupby(['year', 'month'])['type'], return_str=False).reset_index()
# xxx['x_label'] = xxx.year.astype(str) + '-' + xxx.month.astype(str) 
# xxx['n'] = xxx['n'].astype(int)

# fig = px.line(    
#     xxx,
#     title='calls',
#     x='x_label', 
#     y='n',
#     # labels={'year': 'Average Yield Rate', 'n': 'Average Yiled Rate'},
#     width=2000,
#     height=500,
#     template='plotly_white',
#     line_shape='spline'
#     # text='label',
# )

# fig.update_traces(
#     marker_color=ssrc_pal.get('ssrc_blue'),
#     marker_line_width=3.5,
#     opacity=0.9,
# ) 

# fig.update_layout(
#    xaxis = dict(
#       tickmode = 'linear',
#       tick0 = 0.5,
#       dtick = 0.75
#    )
# )
# fig.update_yaxes(range = [0,xxx.query['%_tot'].max()+5])
# fig.show()

# df.info(verbose=True)

gdf = gpd.GeoDataFrame(
    df, 
    crs="EPSG:4326", 
    geometry=gpd.points_from_xy(df['longitude'], df['latitude']))\
        .to_crs("EPSG:2926")

gdf.info(verbose=True)

gdf.drop(columns=['week']).to_file('api_dispatch.gpkg', layer='api_dispatch', driver="GPKG")

# read files
# hex_gdf = gpd.read_file('/Users/balmdale/code/B01_sfd/api_dispatch.gpkg', layername='hex_with_buffer_counts')
# hex_gdf = hex_gdf.to_crs("EPSG:2926")

city = gpd.read_file('/Users/balmdale/Downloads/Seattle_Area_Polygon_-8421515193026472219/Seattle_Area_Poly.shp').to_crs("EPSG:2926")
village = gpd.read_file('/Users/balmdale/Downloads/Urban_Centers_Villages_and_Manufacturing_Industrial_Centers/Urban_Centers_Villages_and_Manufacturing_Industrial_Centers.shp').to_crs("EPSG:2926")
retired = gpd.read_file('/Users/balmdale/Downloads/Long_Term_Care_Residential_Care_view_-8610466403980595132/Long_Term_Care_Residential_Care.shp').to_crs("EPSG:2926")

def hexagon(x_center, y_center, r):
    """Create a flat-topped hexagon with edge length r."""
    angles = np.linspace(0, 2*np.pi, 7)  # 0 to 360 degrees
    coords = [(x_center + r * np.cos(a), y_center + r * np.sin(a)) for a in angles]
    return Polygon(coords)

def hex_grid_from_gdf(gdf, edge_length, crs_out=None, mode="intersect"):
    """
    Generate a flat-topped hexagon grid covering the bounding box of a GeoDataFrame.
    
    Parameters
    ----------
    gdf: GeoDataFrame 
        Input geometries
    edge_length: float
        Length of each hexagon edge (same units as CRS)
    crs_out : str or int, optional
        Projected CRS to use (recommended, e.g., EPSG:26910 for Seattle)
    mode: {"full", "clip", "intersect"}, default "clip"
        - "full": return all hexes covering bounding box
        - "clip": return only the clipped parts inside geometry
        - "intersect": return full hexes that intersect geometry
    """
    if crs_out:
        gdf = gdf.to_crs(crs_out)

    xmin, ymin, xmax, ymax = gdf.total_bounds

    dx = 3/2 * edge_length
    dy = np.sqrt(3) * edge_length

    ncols = int((xmax - xmin) / dx) + 2
    nrows = int((ymax - ymin) / dy) + 2

    hexes = []
    for col in range(ncols):
        for row in range(nrows):
            x = xmin + col * dx
            y = ymin + row * dy
            if col % 2 == 1:
                y += dy / 2
            hexes.append(hexagon(x, y, edge_length))

    hex_gdf = gpd.GeoDataFrame(geometry=hexes, crs=gdf.crs)

    if mode == "clip":
        hex_gdf = gpd.overlay(hex_gdf, gdf, how="intersection")
    elif mode == "intersect":
        mask = hex_gdf.intersects(gdf.unary_union)
        hex_gdf = hex_gdf.loc[mask].copy()
    elif mode == "full":
        pass  
    else:
        raise ValueError("mode must be one of {'full', 'clip', 'intersect'}")

    return hex_gdf

# add location booleans to jex
hex_size = 500
hex_gdf = hex_grid_from_gdf(city, hex_size, mode='intersect')

village_dt = village.query("UVDA == 'Center_City'")
village_dt_expanded = village.query("UVDA in ['Center_City', 'First_Hill_Capitol_Hill']")

hex_gdf["in_downtown"] = hex_gdf.intersects(village_dt.unary_union)
hex_gdf["in_downtown_epanded"] = hex_gdf.intersects(village_dt_expanded.unary_union)
hex_gdf["in_village"] = hex_gdf.intersects(village.unary_union)

hex_gdf["retired"] = hex_gdf.intersects(retired.unary_union)

hex_gdf.to_file('api_dispatch.gpkg',layer=f'hex_{hex_size}', driver="GPKG")



def count_calls_in_hex(hex_gdf, calls_gdf, buffer=None, call_type_col=None):
    """
    Count EMS calls per hexagon.

    Parameters
    ----------
    hex_gdf: GeoDataFrame
        Hexagon grid polygons (from hex_grid_from_gdf).
    calls_gdf: GeoDataFrame
        Point dataset of EMS calls.
    buffer: float, optional
        Buffer distance in same units as CRS. If provided, counts calls
        within the buffered area of each call.
    call_type_col: str, optional
        Column in calls_gdf to group counts by call type. If None, total count only.

    Returns
    -------
    GeoDataFrame with counts joined to hex_gdf.
    """
    if hex_gdf.crs != calls_gdf.crs:
        calls_gdf = calls_gdf.to_crs(hex_gdf.crs)

    if buffer:
        calls_gdf = calls_gdf.copy()
        calls_gdf["geometry"] = calls_gdf.buffer(buffer)

    joined = gpd.sjoin(calls_gdf, hex_gdf, how="inner", predicate="intersects")

    if call_type_col:
        counts = (
            joined.groupby(["index_right", call_type_col])
            .size()
            .reset_index(name="count")
        )
        counts_wide = counts.pivot(
            index="index_right", columns=call_type_col, values="count"
        ).fillna(0)
        counts_wide["total_calls"] = counts_wide.sum(axis=1)
        hex_gdf = hex_gdf.join(counts_wide, how="left")
    else:
        counts = joined.groupby("index_right").size().rename("total_calls")
        hex_gdf = hex_gdf.join(counts, how="left")

    hex_gdf = hex_gdf.fillna(0)

    return hex_gdf

def clean_cols(df, clean_index=True, other_chars_dict=None): 
    df = df.rename(columns = lambda x: str(x).lower().strip())
    replace_dict={i:'_' for i in list(punctuation)+[' ', '\n']}
    if other_chars_dict is not None:
        replace_dict = merged_dict(replace_dict, other_chars_dict)
    for k, v in  replace_dict.items():
        df = df.rename(columns = lambda x: str(x).replace(k, v))
    df = df.rename(columns = lambda x: '_'+str(x) if str(x)[0].isdigit() else str(x))
    print("Column rename succesful")
    return df
    
# Count all calls in each hex
hex_with_counts = count_calls_in_hex(hex_gdf, gdf)
hex_with_counts.to_file('api_dispatch.gpkg',layer='hex_with_counts', driver="GPKG")

# Count calls within 100m of each call point
hex_with_buffer_counts = count_calls_in_hex(hex_gdf, gdf, buffer=100)
hex_with_buffer_counts.to_file('api_dispatch.gpkg',layer='hex_with_buffer_counts', driver="GPKG")

# Count by call type (column "call_type")
# hex_with_type_counts = count_calls_in_hex(hex_gdf, gdf, call_type_col="type")
# hex_with_type_counts = clean_cols(hex_with_type_counts)

find_frequency(gdf['type'])
gdf.shape
gdf.head(5)
hex_with_buffer_counts_aid = count_calls_in_hex(hex_gdf, gdf.query("type in ['Aid Response', 'Medic Response']"), buffer=100)
hex_with_buffer_counts_aid.to_file('api_dispatch.gpkg',layer='hex_with_buffer_counts_aid', driver="GPKG")

	






































#---------------------------------
# this works 
# pulls 1,000 records between date range
# then waits to pull in new data
#---------------------------------
import time
import requests
import pandas as pd
import sqlite3

# --- CONFIG ---
interval = 60  # in seconds
verbose = True
db_file = "sfd_api_calls_2025_0902.db"
table_name = "api_dispatch"

url = 'https://data.seattle.gov/resource/kzjm-xkqj.json'
limit = 1000

# Date range
start_year = 2025
end_year = 2025
start_date = f"{start_year}-01-01T00:00:00.000"
end_date = f"{end_year}-12-31T23:59:59.999"

# --- SETUP DATABASE ---
conn = sqlite3.connect(db_file)
cursor = conn.cursor()

# Create table if not exists
cursor.execute(f'''
    CREATE TABLE IF NOT EXISTS {table_name} (
        incident_number TEXT PRIMARY KEY,
        datetime TEXT,
        type TEXT,
        address TEXT,
        incident_location TEXT,
        longitude REAL,
        latitude REAL
    )
''')
conn.commit()

def store_new_calls(df):
    for _, row in df.iterrows():
        try:
            cursor.execute('''
                INSERT INTO dispatch (incident_number, datetime, level, units, location, type)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', (
                row['Incident #'],
                row['Date/Time'],
                row['Level'],
                row['Units'],
                row['Location'],
                row['Type']
            ))
            print(f"Added incident {row['Incident #']}")
        except sqlite3.IntegrityError:
            # Skip if incident already exists
            pass
    conn.commit()

# --- FUNCTION TO GET NEW DATA ---
def fetch_data():
    all_data = []
    offset = 0

    while True:
        params = {
            '$limit': limit,
            '$offset': offset,
            '$where': f"datetime between '{start_date}' and '{end_date}'"
        }
        response = requests.get(url, params=params)
        chunk = response.json()

        if not chunk:
            break

        all_data.extend(chunk)
        offset += limit
        if verbose:
            print(f"Fetched {offset} records")

    return pd.json_normalize(all_data)

# --- FUNCTION TO STORE DATA ---
def store_new_records(df):
    new_count = 0
    for _, row in df.iterrows():
        try:
            cursor.execute(f'''
                INSERT INTO {table_name} (incident_number, datetime, type, address, incident_location, longitude, latitude)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (
                row.get('incident_number'),
                row.get('datetime'),
                row.get('type'),
                row.get('address'),
                row.get('incident_location.human_address'),
                row.get('incident_location.longitude'),
                row.get('incident_location.latitude'),
            ))
            new_count += 1
        except sqlite3.IntegrityError:
            # Already exists
            continue
    conn.commit()
    print(f"Inserted {new_count} new records.")

# --- MAIN LOOP ---
while True:
    print("\n Checking for new incidents...")
    try:
        df = fetch_data()
        if not df.empty:
            store_new_records(df)
        else:
            print("No new data.")
    except Exception as e:
        print("Error:", e)

    print(f"Sleeping {interval} seconds...")
    time.sleep(interval)



# UNITS
#---------------------------------
# this works 
# pull unit data from realtime fire
# but only he 396 displayed on the webpage 
#---------------------------------

from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from bs4 import BeautifulSoup
import pandas as pd
import time

# Setup headless Chrome
chrome_options = Options()
chrome_options.add_argument("--headless")
chrome_options.add_argument("--disable-gpu")
chrome_options.add_argument("--no-sandbox")

# Launch driver
driver = webdriver.Chrome(options=chrome_options)

# URL of real-time 911 dispatch feed
url = 'https://web.seattle.gov/sfd/realtime911/getRecsForDatePub.asp?action=Today&incDate=&rad1=des'
driver.get(url)

# Wait for JavaScript to render
time.sleep(15)  # Give time for data to load

# Get page content
html = driver.page_source
driver.quit()

# Parse HTML using BeautifulSoup
soup = BeautifulSoup(html, 'html.parser')
table = soup.find('table')

# Sanity check
if table is None:
    raise Exception("Could not find the dispatch table. Try increasing the wait time.")

# Get headers
headers = ['Date/Time', 'Incident #', 'Level', 'Units', 'Location' , 'Type']
# Extract rows
data = []
for row in table.find_all('tr')[1:]:  # skip header
    cells = [td.get_text(strip=True) for td in row.find_all('td')]
    if len(cells) == len(headers):
        data.append(cells)

# Convert to DataFrame
df_units = pd.DataFrame(data, columns=headers)


# df_merge = df.merge(df_units, how='inner', left_on='incident_number', right_on='Incident #')





#---------------------------------
# this works
# doanloads and appends data to a sqllite3 db
#---------------------------------

import time
import sqlite3
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from bs4 import BeautifulSoup
import pandas as pd

# Set your polling interval (in seconds)
interval = 20  # run every 60 seconds

# Set up SQLite database connection
conn = sqlite3.connect('sfd_911_calls.db')
cursor = conn.cursor()

# Create table if it doesn't exist
cursor.execute('''
    CREATE TABLE IF NOT EXISTS dispatch (
        incident_number TEXT PRIMARY KEY,
        datetime TEXT,
        level TEXT,
        units TEXT,
        location TEXT,
        type TEXT
    )
''')
conn.commit()

cursor.execute("SELECT * FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())

# Function to scrape and return a DataFrame
def scrape_dispatch_table():
    chrome_options = Options()
    chrome_options.add_argument("--headless")
    chrome_options.add_argument("--disable-gpu")
    chrome_options.add_argument("--no-sandbox")

    driver = webdriver.Chrome(options=chrome_options)
    url = 'https://web.seattle.gov/sfd/realtime911/getRecsForDatePub.asp?action=Today&incDate=&rad1=des'
    driver.get(url)
    time.sleep(15)
    html = driver.page_source
    driver.quit()

    soup = BeautifulSoup(html, 'html.parser')
    table = soup.find('table')
    if table is None:
        raise Exception("Dispatch table not found.")

    headers = ['Date/Time', 'Incident #', 'Level', 'Units', 'Location' , 'Type']
    data = []
    for row in table.find_all('tr')[1:]:
        cells = [td.get_text(strip=True) for td in row.find_all('td')]
        if len(cells) == len(headers):
            data.append(cells)

    df = pd.DataFrame(data, columns=headers)
    return df

# Function to insert new records into the database
def store_new_calls(df):
    for _, row in df.iterrows():
        try:
            cursor.execute('''
                INSERT INTO dispatch (incident_number, datetime, level, units, location, type)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', (
                row['Incident #'],
                row['Date/Time'],
                row['Level'],
                row['Units'],
                row['Location'],
                row['Type']
            ))
            print(f"Added incident {row['Incident #']}")
        except sqlite3.IntegrityError:
            # Skip if incident already exists
            pass
    conn.commit()

# Main loop
while True:
    print("Checking for new dispatch calls...")
    try:
        df_new = scrape_dispatch_table()
        store_new_calls(df_new)
    except Exception as e:
        print(f"Error: {e}")
    time.sleep(interval)

# conn = sqlite3.connect('sfd_911_calls.db')

# # Read the table into a DataFrame
# df = pd.read_sql_query("SELECT * FROM dispatch", conn)

# # Export to CSV
# df.to_csv('sfd_911_calls_export.csv', index=False)