Skip to content

Instantly share code, notes, and snippets.

@HeikkiVesanto
HeikkiVesanto / strava_process.sql
Created September 19, 2022 23:42
Strava Activity Processing
View strava_process.sql
--Only needs to be run if it already exists.
drop table strava.strava_pts_map;
--Moves the points to start from 0,0
create table strava.strava_pts_map as
with
min_time_geom
as
(
select distinct on (aid)
View hoods processing.sql
alter table dublin_hoods.all_dub_new add column geom_2157 geometry(polygon, 2157);
update dublin_hoods.all_dub_new set geom_2157 = st_transform(geom, 2157);
create index
idx_all_dub_new_geom_2157 on
dublin_hoods.all_dub_new using gist (geom_2157);
-- Hex grid needs to be created in QGIS
@HeikkiVesanto
HeikkiVesanto / gist:a5a0e1f24d2680e9306ba465553de431
Created December 7, 2021 11:42
Download and parse to PostgreSQL Dublin Hoods
View gist:a5a0e1f24d2680e9306ba465553de431
import zipfile, os, subprocess, psycopg2, codecs, json
from urllib.request import Request, urlopen
import shutil
dir_path = os.path.dirname(os.path.realpath(__file__))
DB = "PG:dbname=postgis host=localhost port=5432 user=postgres password=postgres"
conn = psycopg2.connect(host='localhost', dbname='postgis', user='postgres', password='postgres')
print(DB)
@HeikkiVesanto
HeikkiVesanto / glob.py
Last active November 26, 2021 17:57
Spinning globe
View glob.py
# QGIS spinning globe export.
# Run https://gist.github.com/Joonalai/7b8693ef904df75cb15cb9af0e82c032 first
# Uses the GISPO globe plugin: https://github.com/GispoCoding/GlobeBuilder/
import time
from PyQt5.QtGui import *
mapCanvas = iface.mapCanvas()
vLayer = iface.activeLayer()
def spin(seconds):
@HeikkiVesanto
HeikkiVesanto / hood_hex.sql
Created November 4, 2021 09:58
Hoods to Hex SQL
View hood_hex.sql
create index idx_liberties_geom_2157 on dublin_hoods.liberties using gist (st_transform(geom, 2157));
create table dublin_hoods.liberties_extent as
select g.id, g.geom, count(nh.*) as count, ((count(nh.*)/(select count(*)::float from dublin_hoods.liberties)) * 100)::int as pct
from dublin_hoods.hex_grid g
left join dublin_hoods.liberties nh on st_intersects(g.geom, st_transform(nh.geom, 2157)) --My grid is in EPSG:2157 but the geoms are 4326 so need to reproject.
group by g.id, g.geom
having count(nh.*) > 0;
@HeikkiVesanto
HeikkiVesanto / parse_hoods.py
Last active January 15, 2022 02:36
Parse Dublin Hoods Simple
View parse_hoods.py
import os, psycopg2, json
from urllib.request import Request, urlopen
dir_path = os.path.dirname(os.path.realpath(__file__))
# Database details:
DB = "PG:dbname=postgis host=localhost port=5432 user=postgres password=postgres"
conn = psycopg2.connect(host='localhost', dbname='postgis', user='postgres', password='postgres')
@HeikkiVesanto
HeikkiVesanto / load_osm_history.py
Last active August 8, 2021 20:58
pipe osmium-tools extracts into PostgreSQL
View load_osm_history.py
import os
import psycopg2
import subprocess
def runogr2ogr(infile):
# import the data
try:
command = ["ogr2ogr", "-f", "PostgreSQL",
OGR_CONN_STRING,
@HeikkiVesanto
HeikkiVesanto / py3_osgeo.bat
Created March 9, 2018 10:26
Launch Python3 in OSGeo4W
View py3_osgeo.bat
@ECHO OFF
set OSGEO4W_ROOT=C:\OSGeo4W64
set PATH=%OSGEO4W_ROOT%\bin;%PATH%
set PATH=%PATH%;%OSGEO4W_ROOT%\apps\qgis\bin
@echo off
call "%OSGEO4W_ROOT%\bin\o4w_env.bat"
call "%OSGEO4W_ROOT%\bin\qt5_env.bat"
@HeikkiVesanto
HeikkiVesanto / walk.json
Last active August 29, 2015 14:22
Schiehallion
View walk.json
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
View world_country.topojson
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.