<jupyter_start><jupyter_text>QueryThis query gets all geometries within x meters of certain long, lat of a certain type<jupyter_code>q = """
SELECT
    id,
    ST_Distance(ST_Transform(ST_SetSrid(ST_Point({0}, {1}), 4326), 3857), geometry) * cosd({1}) dist
    {5}
FROM {2}
WHERE
    ST_DWithin(ST_Transform(ST_SetSrid(ST_Point({0}, {1}), 4326), 3857), geometry, {3} / cosd({1}))
ORDER BY dist
LIMIT {4}
"""<jupyter_output><empty_output><jupyter_text>Config* tables: the table names containing the geometries. Mapping to the noise produced by each geometry* limits: How many rows to retreive from each table for measuring noise<jupyter_code>tables = {
    "osm_light_railway": 70,
    "osm_secondary_road": 85,
    "osm_trunk": 95,
    "osm_motorway": 100,
    "osm_tertiary_road": 88,
    "osm_primary_road": 90,
    "osm_railway": 90,
    "osm_retail_zone": 80,
    "osm_sport": 70,
    "osm_sport_point": 70,
    "osm_leisure": 60,
    "osm_party": 80,
    "osm_shop": 60,
    "osm_industrial_zone": 80,
    "osm_food": 65,
    "osm_school": 70,
    "osm_school_point": 70
}
limits = {
    "osm_light_railway": 1,
    "osm_secondary_road": 2,
    "osm_trunk": 2,
    "osm_motorway": 2,
    "osm_tertiary_road": 2,
    "osm_primary_road": 2,
    "osm_railway": 1,
    "osm_retail_zone": 2,
    "osm_sport": 2,
    "osm_sport_point": 2,
    "osm_leisure": 2,
    "osm_party": 2,
    "osm_shop": 2,
    "osm_industrial_zone": 2,
    "osm_food": 2,
    "osm_school": 2,
    "osm_school_point": 2,
}
speed_limits = {
    "osm_trunk": [100, 80],
    "osm_motorway": [100, 80],
    "osm_secondary_road": [50 ,30],
    "osm_tertiary_road": [50, 30],
    "osm_primary_road": [50, 30],
}<jupyter_output><empty_output><jupyter_text>EndpointA function that calculates noise given long, lat<jupyter_code>def noise(long, lat, tables, max_dist=1200):
    """Noise report for certain long, lat
    
    Args:
        long: Longitude
        lat: Latitude
        max_dist: Maximum distance to look for geometries for (in meters)
        
    Returns:
        dict containing the report of the structure (noise in dB and distances in meters)
        {'osm_secondary_road': 166,
         'osm_tertiary_road': 355,
         'osm_primary_road': 234,
         'osm_railway': 184,
         'osm_retail_zone': 272,
         'osm_party': 234,
         'osm_shop': 163,
         'osm_industrial_zone': 272,
         'osm_food': 271,
         'noise': 49.9986629248497}
    """
    dist  = [] # Distances
    power = [] # Noise Power
    info  = {} # Nearest of each type
    
    # get nearest x geometries of each type
    for table in tables:
        extention = ""
        if table in speed_limits:
            extention = ", maxspeed"
        cursor.execute(q.format(long, lat, table, max_dist, limits[table], extention))
        record = cursor.fetchall()
        for r in record:
            dist.append(round(r[1]))
            power.append(tables[table])
            if table in speed_limits and r[2] and r[2] <= speed_limits[table][0]:
                power[-1] -= 5
            if table in speed_limits and r[2] and r[2] <= speed_limits[table][1]:
                power[-1] -= 5
            info[table] = info.get(table, round(r[1]))

    # Noise calculation formula (simple noise propagations)
    dist = np.array(dist).clip(5)
    power = np.array(power)
    info["noise"] = np.log10((10**(power / 10 - 2 * abs(np.log10(dist)) - dist / 3000)).sum()) * 10
    return info
%%time
noise(8.451002, 50.092927, tables)<jupyter_output>CPU times: user 2.16 ms, sys: 2.78 ms, total: 4.94 ms
Wall time: 48.7 ms
{'fraction_non_alphanumeric': 0.1362116991643454, 'fraction_numerical': 0.05598885793871866, 'mean_word_length': 2.928884026258206, 'pattern_counts': {'":': 39, '<': 14, '<?xml version=': 0, '>': 12, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '537357', 'license': 'MIT', 'n_tokens_mistral': 1493, 'n_tokens_neox': 1308, 'n_words': 327, 'path': 'notebooks/api_demo.ipynb', 'repo_name': 'nawarhalabi/osm-noise-pollution', 'size': '3590'}