I hereby claim:
- I am chribsen on github.
- I am chribsen (https://keybase.io/chribsen) on keybase.
- I have a public key ASCZGGs7p7bZ6gaBqglhmda10FiIbkQ_VtcM42jr6CCRpgo
To claim this, I am signing this object:
I hereby claim:
To claim this, I am signing this object:
I hereby claim:
To claim this, I am signing this object:
CREATE OR REPLACE FUNCTION getIndices(_table_name varchar) | |
RETURNS TABLE(table_name varchar, index_name varchar, column_name varchar) AS $$ | |
BEGIN | |
RETURN QUERY | |
select | |
t.relname::varchar as table_name, | |
i.relname::varchar as index_name, | |
a.attname::varchar as column_name | |
from | |
pg_class t, |
<?xml version="1.0" encoding="UTF-8"?> | |
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom"> | |
<Document> | |
<name>Area Calculator</name> | |
<description>2814731.04 m² | 2.81 km² | 695.54 acres | 281.47 hectares | 30297512.88 feet² |1.09 square miles | 0.82 square nautical miles</description> | |
<gx:balloonVisibility>1</gx:balloonVisibility> | |
<StyleMap id="msn_ylw-pushpin"> | |
<Pair> | |
<key>normal</key> | |
<styleUrl>#sn_ylw-pushpin</styleUrl> |
select ST_MAKELINE(ARRAY[St_Setsrid(st_centroid(dc1.geom),4326), st_setsrid(st_centroid(dc2.geom),4326)]), dc1.name_2 as source,dc2.name_2 as target, (sum(dd.is_friend_ratio)/(dc1.rf_population+dc2.rf_population)::float) as weight, count(*) as cou | |
into presentation_city_connections | |
from derived_dyads dd | |
inner join user_residence ur1 on dd.user_a=ur1.user_id | |
inner join dk_cities dc1 on dc1.gid=ur1.city_id | |
inner join user_residence ur2 on dd.user_b=ur2.user_id | |
inner join dk_cities dc2 on dc2.gid=ur2.city_id | |
where dc1.name_2!=dc2.name_2 | |
group by dc1.name_2,dc2.name_2,dc1.rf_population, dc2.rf_population,dc1.geom, dc2.geom; |
import psycopg2 | |
import requests | |
import time | |
conn_dtu = psycopg2.connect("<connection_string>") | |
cur_dtu = conn_dtu.cursor() | |
cur_dtu.execute("select artist_name from data_lineup where spotify_id is null") | |
search_url = 'https://api.spotify.com/v1/search' |
-- The size Roskilde Festival in Square Kilometers | |
-- The points used are taken from the max points received in the dataset from IBM / CBS dataset. | |
SELECT (st_distance_sphere(st_makepoint(12.106277, 55.627567), st_makepoint(12.058901,55.627567) ) /1000) * | |
(st_distance_sphere(st_makepoint(12.058901,55.627567), st_makepoint(12.058901, 55.609975) )/1000) | |
update derived_friend_features set same_genre_score=FALSE; | |
update derived_friend_features AS fr set same_genre_score = TRUE | |
where (select itunes_genre FROM tmp_user_genre WHERE user_id = fr.user_a limit 1) = (select itunes_genre FROM tmp_user_genre WHERE user_id = fr.user_b limit 1) |
-- Creates the feature same camp score by looking in the table tmp_user_camp, that consists of the most frequent visited camp site. | |
update derived_friend_features set same_camp_score=FALSE; | |
update derived_friend_features AS fr set same_camp_score = TRUE | |
where (select title FROM tmp_user_camp WHERE user_id = fr.user_a limit 1) = (select title FROM tmp_user_camp WHERE user_id = fr.user_b limit 1) | |
; |
import psycopg2 | |
from scipy.spatial.distance import jaccard | |
conn_dtu = psycopg2.connect(<connstring>) | |
cur_dtu = conn_dtu.cursor() | |
# Retrieve places visited for each user in the user pair and aggregate them into an array. | |
cur_dtu.execute(""" | |
select count(*) FROM (select user_a, ( SELECT array_agg(place_id) FROM derived_places_visited WHERE user_id=dff.user_a) as places_a, | |
user_b, ( SELECT array_agg(place_id) FROM derived_places_visited WHERE user_id=dff.user_b) as places_b from derived_friend_features as dff |