HeikkiVesanto

--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)

HeikkiVesanto

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

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

# 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

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

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

import os
import psycopg2
import subprocess


def runogr2ogr(infile):
    # import the data
    try:
        command = ["ogr2ogr", "-f", "PostgreSQL",
                   OGR_CONN_STRING,

HeikkiVesanto

@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