Skip to content

Instantly share code, notes, and snippets.

View kpennell's full-sized avatar
😋

Kyle kpennell

😋
21 followers · 3 following
View GitHub Profile
@kpennell
kpennell / bigquerybuildings.sql
Created February 3, 2022 19:10
-- Define bounding area of London
WITH
bounding_area AS (
SELECT
geometry
FROM
`bigquery-public-data.geo_openstreetmap.planet_features`
WHERE
('name',
'Los Angeles County') IN (
@kpennell
kpennell / blackrock2021camps.geojson
Created August 30, 2021 19:24
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@kpennell
kpennell / blackrock2021camps.csv
Created August 30, 2021 19:23
We can make this file beautiful and searchable if this error is corrected: It looks like row 7 should actually have 10 columns, instead of 2. in line 6.
name,what3words,description,events,website,arrival,departure,gps,latitude,longitude
(Dusty Decibles),processors.support.undiplomatic,"Sound camp: little bit of oontz oontz, little bit of sound meditation",see L.O.S.T. Guide,,9/1/2021,9/6/2021,"40.806043, -119.178792",40.806043,-119.178792
@nyc499,exists.tanned.cable,A bit from the center,,website,8/30/2021,9/6/2021,"40.83757, -119.175052",40.83757,-119.175052
@REFUGEE UNICORNS@,beware.stud.scatters,We are refugee unicorns ... If you are near us stop by ... And also add us on Instagram @refugeeunicorns,Free unicorn every sunset,website,8/29/2021,9/6/2021,"40.8094227, -119.1884007",40.8094227,-119.1884007
*GeOde Out*,snowy.unleash.upcoming,"Come chill with us under the Glowing Geo Dome & get GeOde Out, Good vibes only ",All Day every day,,8/31/2021,9/7/2021,"40.8091075, -119.1683197",40.8091075,-119.1683197
*Rainbow Kitty DGAF Booty Camp*,stands.requirement.exonerate,Kitties DGAF.,,,9/1/2021,9/6/2021,"40.808841, -119.1827509",40.808841,-119.1827509
&camp,follow
@kpennell
kpennell / indexscoretrees.sql
Created July 12, 2021 18:16
WITH
all_and_stscaler AS (
SELECT
*,
ML.STANDARD_SCALER(trees_per_1000squaremeters) OVER() AS trees_per_1000squaremeters_scale,
ML.STANDARD_SCALER(avg_tree_diameter) OVER() AS avg_tree_diameter_scale
FROM
`cartodb-gcp-solutions-eng-team.kyle_data.nyc_blockgroups_withtreedatacalc` ),
sum_to_create_score AS (
SELECT
@kpennell
kpennell / nyc_blockgroups_withtreedatacalc.sql
Created July 12, 2021 17:34
CREATE TABLE
`cartodb-gcp-solutions-eng-team.kyle_data.nyc_blockgroups_withtreedatacalc` AS
WITH
nycbgs AS (
SELECT
a.blockgroup_geom AS the_geom,
a.geo_id,
b.total_pop,
b.median_income,
b.income_per_capita,
@kpennell
kpennell / target_stores_enriched_with_geosocial_safegraph_2.csv
Created October 28, 2020 15:38
We can't make this file beautiful and searchable because it's too large.
the_geom,cases_per_cap,area_deprivation_index_percent,cartodb_id,unnamed_0,city,contact,country,county,district,formatted,_group,id,last_remodel,latitude,longitude,opened,region,state,store_features,store_name,stores_open,street,subtype,timings,type,zipcode,median_non_home_dwell_time,positive_sentiment_posts,negative_sentiment_posts
0101000020E61000001E6FF25B74B355C05804FF5BC99C4040,2.91771789397949,5,1,0,Alabaster,(205) 564-2608,United States,Shelby,340,"250 S Colonial Dr, Alabaster, AL 35007-4657",394,2276,NaN,33.224895000000004,-86.803977,2007-07-25T12:00:00,300,AL,"['CVS pharmacy', 'Cafe-Pizza', 'Drive Up', 'Starbucks', 'Wine & Beer Available']",Alabaster,"['Monday-Friday', 'Saturday', 'Sunday']",250 S Colonial Dr,NaN,"[{'Week Day': 'Monday-Friday', 'Open Hours': '8:00 a.m.-10:00 p.m.'}, {'Week Day': 'Saturday', 'Open Hours': '8:00 a.m.-10:00 p.m.'}, {'Week Day': 'Sunday', 'Open Hours': '8:00 a.m.-9:00 p.m.'}]",Store,35007-4657,127.45517163505,20.3442457478019,15.9417324310794
@kpennell
kpennell / targetstores_revenueandemployees.csv
Created October 28, 2020 15:31
We can't make this file beautiful and searchable because it's too large.
the_geom,cartodb_id,field_1,city,contact,country,county,district,formatted,_group,id,last_remodel,latitude,longitude,opened,region,state,store_features,store_name,stores_open,street,subtype,timings,type,zipcode,revenue,employee_count
0101000020E6100000F71F990E9D0E57C052D2C3D0EA644140,138,137,North Little Rock,(501) 771-4220,United States,Pulaski,167,"4000 McCain Blvd, North Little Rock, AR 72116-8039",194,162,2007-03-23T12:00:00,34.788416,-92.228336,1982-07-20 12:00:00+00,100,AR,"['Café', 'Drive Up']",Little Rock North,"['Monday-Friday', 'Saturday', 'Sunday']",4000 McCain Blvd,normal,"[{'Week Day': 'Monday-Friday', 'Open Hours': '8:00 a.m.-10:00 p.m.'}, {'Week Day': 'Saturday', 'Open Hours': '8:00 a.m.-11:00 p.m.'}, {'Week Day': 'Sunday', 'Open Hours': '8:00 a.m.-10:00 p.m.'}]",Store,72116-8039,662129,99
@kpennell
kpennell / targetstores_revenueandemployees.csv
Last active October 28, 2020 14:31
We can't make this file beautiful and searchable because it's too large.
the_geom,cartodb_id,field_1,city,contact,country,county,district,formatted,_group,id,last_remodel,latitude,longitude,opened,region,state,store_features,store_name,stores_open,street,subtype,timings,type,zipcode,revenue,employee_count
0101000020E6100000F71F990E9D0E57C052D2C3D0EA644140,138,137,North Little Rock,(501) 771-4220,United States,Pulaski,167,"4000 McCain Blvd, North Little Rock, AR 72116-8039",194,162,2007-03-23T12:00:00,34.788416,-92.228336,1982-07-20 12:00:00+00,100,AR,"['Café', 'Drive Up']",Little Rock North,"['Monday-Friday', 'Saturday', 'Sunday']",4000 McCain Blvd,normal,"[{'Week Day': 'Monday-Friday', 'Open Hours': '8:00 a.m.-10:00 p.m.'}, {'Week Day': 'Saturday', 'Open Hours': '8:00 a.m.-11:00 p.m.'}, {'Week Day': 'Sunday', 'Open Hours': '8:00 a.m.-10:00 p.m.'}]",Store,72116-8039,662129,99
@kpennell
kpennell / targetswithnolatlng.csv
Created October 28, 2020 03:29
We can't make this file beautiful and searchable because it's too large.
City,Contact,Country,County,District,Formatted,Group,ID,Last Remodel,Opened,Region,State,Store Features,Store_Name,Stores_Open,Street,SubType,Timings,Type,Zipcode
Alabaster,(205) 564-2608,United States,Shelby,340,"250 S Colonial Dr, Alabaster, AL 35007-4657",394,2276,null,2007-07-25T12:00:00,300,AL,"['CVS pharmacy', 'Cafe-Pizza', 'Drive Up', 'Starbucks', 'Wine & Beer Available']",Alabaster,"['Monday-Friday', 'Saturday', 'Sunday']",250 S Colonial Dr,,"[{'Week Day': 'Monday-Friday', 'Open Hours': '8:00 a.m.-10:00 p.m.'}, {'Week Day': 'Saturday', 'Open Hours': '8:00 a.m.-10:00 p.m.'}, {'Week Day': 'Sunday', 'Open Hours': '8:00 a.m.-9:00 p.m.'}]",Store,35007-4657
@kpennell
kpennell / examplegooglesheetsresult.js
Created August 2, 2019 10:24
{
"spreadsheetId": "1Qyn6530gveP7wnLHswBH0a_6ndTbWE65hvMWY17313Y",
"valueRanges": [
{
"range": "export!A1:W998",
"majorDimension": "ROWS",
"values": [
[
"id",
"move",