Skip to content

Instantly share code, notes, and snippets.

View janbenetka's full-sized avatar

Jan Benetka janbenetka

4 followers · 4 following
  • Unacast
  • Pilsen, Czech Republic
View GitHub Profile
@janbenetka
janbenetka / resolution_per
Created September 27, 2021 07:33
[Coordinates Resolution per decimal points / lat lon] #geo
decimal
places degrees distance
------- ------- --------
0 1 111 km
1 0.1 11.1 km
2 0.01 1.11 km
3 0.001 111 m
4 0.0001 11.1 m
5 0.00001 1.11 m
6 0.000001 11.1 cm
@janbenetka
janbenetka / site_selection_cbgs.sql
Created September 16, 2021 13:28
[Site Selection - CBGs] #mango #bigquery
unique_venues = sites_with_foot_traffic.drop_duplicates(subset=['name'])
# initialize map
map = folium.Map(location = [30.3006387,-97.7587983],
zoom_start = 11,
tiles='Stamen Toner')
# add pins of candidate locations
for _, r in unique_venues.iterrows():
geo_j = gpd.GeoSeries(r['geo']).to_json()
@janbenetka
janbenetka / style_df.py
Created August 20, 2021 05:35
[Style Pandas Dataframe]
# examples: https://pandas.pydata.org/pandas-docs/stable/user_guide/style.html
SHOW_N = 15
(
pd.concat([km_vs_census_county.head(SHOW_N), km_vs_census_county.tail(SHOW_N)])
.style.bar(subset=['diff_pct'], align='zero', color=['#d65f5f', '#5fba7d'])
.bar(subset=['pop'], align='left', color=['lightblue'])
)
@janbenetka
janbenetka / latex_in_code.md
Created July 29, 2021 02:37
[Latex in Coda]

=LaTeX::Math("t_0")

@janbenetka
janbenetka / ST_GeogFromText.sql
Created July 14, 2021 09:55
[Polygon from text in BQ] #bigquery #geo
SELECT
"Rajkot City" as name,
ST_GeogFromText('Polygon((22.34784492579322 70.7388523794632, 22.355323924868447 70.8053762206165, 22.326248911520796 70.8656791529891, 22.31437981716447 70.85284874184599, 22.24910789523421 70.94889071900057, 22.228288250932245 70.94465075865024, 22.215764615136106 70.92166299169969, 22.229806743896408 70.87265587874343, 22.25464706420604 70.8512638822403, 22.255187022256013 70.83998446735355, 22.24942736237542 70.8347337052511, 22.25158726259193 70.8281216344554, 22.230706832103227 70.82170403633019, 22.22530621441818 70.80828542206835, 22.208743023287838 70.81042462144343, 22.19307820698311 70.80342360530682, 22.193618402156407 70.79681153451114, 22.230886849109833 70.7915607724087, 22.237187298322823 70.77114113787808, 22.234487140323587 70.75791699628672, 22.23592723106375 70.73380238514952, 22.246367446344223 70.7022978125348, 22.262026310267927 70.69393548770496, 22.277143551277188 70.69938072247787, 22.26490591066832 70.71416064543293, 22.269405167673817 70.716883
@janbenetka
janbenetka / display_iframe_in_jupyter.py
Created July 6, 2021 07:48
[iFrame in Jupyter] #iframe #display
from IPython.display import IFrame
from IPython.core.display import display
display(IFrame('https://www.capolicylab.org/calexodus-are-people-leaving-california/', '100%', '400px'))
@janbenetka
janbenetka / polar_plot.py
Created June 22, 2021 09:08
[Polar/Radar plot in plotly] #plotly
import plotly.graph_objects as go
categories = ['accommodation','financial','tourism',
'shopping', 'food&drinks']
fig = go.Figure()
fig.add_trace(go.Scatterpolar(
r=[1, 5, 2, 2, 3],
theta=categories,
@janbenetka
janbenetka / bq_geojson_load.sql
Created June 15, 2021 06:35
[GeoJson load into BQ] #bigquery #json #geo
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
--json_extension=GEOJSON \
--project_id=uc-project-id\
--autodetect \
dataset.new_table \
Arkansas.geojson
@janbenetka
janbenetka / bq_date_loop.sql
Last active November 20, 2022 12:46
[BQ Loop over days] Date loop for backfills #bigquery #sql #date #loop
DECLARE i INT64 DEFAULT 0;
DECLARE DATES ARRAY<DATE>;
DECLARE event_date DATE;
SET DATES = GENERATE_DATE_ARRAY(DATE(2019,1,1), DATE(2019, 12, 31), INTERVAL 1 DAY);
LOOP
SET i = i + 1;
IF i > ARRAY_LENGTH(DATES) THEN
@janbenetka
janbenetka / h3_in_bigquery.sql
Last active October 27, 2022 10:02
[H3 hexagon functions in BigQuery] #h3 #hex
SELECT jslibs.h3.ST_GEOGPOINTFROMH3("u2ce02j")
SELECT jslibs.h3.ST_H3_BOUNDARY(jslibs.h3.ST_H3(ST_GEOGPOINT(13.377534960188237, 49.747300576525554), 11))
SELECT jslibs.h3.h3GetResolution("u2ce02j");
# lat/lon to hexagon
SELECT jslibs.h3.ST_H3(ST_GEOGPOINT(statistics.coordinate.longitude, statistics.coordinate.latitude), 10) AS dwell_hex_id,
WITH polygon AS (