This repository has been archived by the owner on Feb 9, 2023. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 4
/
sfdb.py
137 lines (111 loc) · 3.46 KB
/
sfdb.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
## base python libraries
import json
from textwrap import dedent
from time import time
## pip installed libraries
import pandas as pd
import snowflake.connector
import streamlit as st
## repo-local code
from coordinates import Coordinates
## connect to Snowflake
@st.experimental_singleton(show_spinner=False)
def sfconn(**secrets):
return snowflake.connector.connect(**secrets)
## query Snowflake based on Streamlit input widgets
def get_feature_collection(
_conn,
coordinates: Coordinates,
table: str = "POINT",
tags: list = None,
column: str = "ACCESS",
num_rows: int = 1000,
) -> dict:
x1 = coordinates.x1
y1 = coordinates.y1
x2 = coordinates.x2
y2 = coordinates.y2
linestring = f"LINESTRING({x1} {y1}, {x2} {y1}, {x2} {y2}, {x1} {y2}, {x1} {y1})"
polygon = f"st_makepolygon(to_geography('{linestring}'))"
if tags is not None:
tags = [tag.replace("'", "''") for tag in tags]
tag_string = ",".join(f"'{tag}'" for tag in tags)
# In order to store and keep properties around, manually construct json, rather than
# using st_collect
table = f"ZWITCH_DEV_WORKSPACE.TESTSCHEMA.PLANET_OSM_{table}".upper()
query = f"""
with points as (
select
NAME,
{column},
TAGS,
object_construct(
'type', 'Feature',
'geometry', ST_ASGEOJSON(WAY),
'properties',
object_construct(
'NAME', NAME,
'{column}', {column},
'TAGS', SUBSTRING(TAGS, 0, 512),
'OSM_ID', OSM_ID
)
) as geojson_obj
from {table}
where NAME is not null
and {column} is not null
and st_within(WAY, {polygon})
{f"and {column} in ({tag_string})" if tags else ""}
limit {num_rows}
)
select
object_construct('type', 'FeatureCollection', 'features', array_agg(geojson_obj)) as geojson
from points;
"""
data = pd.read_sql(query, _conn)
end = time()
st.sidebar.expander("Show generated query").code(
dedent(query).replace(" ", " "), language="sql"
)
geojson_data = json.loads(data["GEOJSON"].iloc[0])
n_rows = len(geojson_data["features"])
st.sidebar.write(
f"""
Table: `{table}`
Rows returned: {n_rows}
"""
)
return geojson_data
## Get the list of points with CAPITAL = 4 (state capitals)
@st.experimental_singleton(show_spinner=False)
def state_capitals(_conn) -> pd.DataFrame:
df = pd.read_sql(
"""
select
name,
way as location
from ZWITCH_DEV_WORKSPACE.TESTSCHEMA.PLANET_OSM_POINT
where CAPITAL = '4'
order by name
""",
_conn,
)
return df
## get all possible values for a given column chosen
## limiting to more popular ones to avoid one-off and mistake values
@st.experimental_memo(show_spinner=False)
def get_fld_values(_conn, tbl, col):
df = pd.read_sql(
f"""
select * from (
select
{col},
count(*) as inst
from ZWITCH_DEV_WORKSPACE.TESTSCHEMA.planet_osm_{tbl}
where {col} is not NULL
group by 1
order by 2 desc)
where inst >= 10
""",
_conn,
)
return df[col]