Skip to content

Instantly share code, notes, and snippets.

Avatar
:shipit:
Focusing

pablo lopez plablo09

:shipit:
Focusing
View GitHub Profile
@plablo09
plablo09 / neigborhood_connectivity_degree.sql
Created Dec 6, 2019
SQL (PostGis) script to calculate a weighted (by street type) connectivity degree (also known as permeability) at the neighborhood level
View neigborhood_connectivity_degree.sql
with gp as(
select dim_colonia.*, deg.count as degree
from
(select h.colonia_cve, count(h.name)
from
(
select gu.colonia_cve, name
from
(
select c.colonia_cve, rp.gid, rp.the_geom, rp.name
@plablo09
plablo09 / natural_cities.sql
Last active Aug 14, 2019
Pure SQL implementation of Bin Jiang Natural Cities Algorithm
View natural_cities.sql
-- create delaunay lines
select st_delaunaytriangles(st_collect(the_geom),0.0000001, 1)from planet_osm_roads_vertices_pgr
-- create delaunay and dump the resulting multilinestrings
select (st_dump(st_delaunaytriangles(st_collect(the_geom),0.0000001, 1))).path[1] as id, (st_dump(st_delaunaytriangles(st_collect(the_geom),0.0000001, 1))).geom
from planet_osm_roads_vertices_pgr
-- add edge length in meters (over the spheroid)
select (st_dump(st_delaunaytriangles(st_collect(the_geom),0.0000001, 1))).path[1] as id,
(st_dump(st_delaunaytriangles(st_collect(the_geom),0.0000001, 1))).geom,
View spatial_join.py
import pandas as pd
import geopandas as gpd
from shapely.geometry import Point
import fiona
#supongamos que tienes los tuits en un csv (las coordenadas en las columns "x" y "y"). Los lees con pandas
df = pd.read_csv('data.csv')
#conviertes las coordenadas en objetos Point
geometry = [Point(xy) for xy in zip(df.x, df.y)]
#Le pones proyección (lat, long en este caso)
@plablo09
plablo09 / contour_lines.sql
Created Nov 26, 2018
Contour lines from irregular points
View contour_lines.sql
CREATE OR REPLACE FUNCTION _get_cell_intersects(
IN vertex geometry[], -- vertices geometries
IN vv numeric[], -- vertices values
IN bu integer[], -- vertices buckets
IN breaks numeric[], -- breaks
IN i1 integer, -- first vertex index
IN i2 integer -- last vertex index
)
RETURNS geometry[] AS $$
DECLARE
@plablo09
plablo09 / README.md
Last active Mar 22, 2018 — forked from TennisVisuals/README.md
reusable updating radarChart
View README.md
View topics data frame
structure(list(Time = c("2015-12-07", "2016-05-03", "2015-12-07",
"2016-05-03", "2015-12-07", "2016-05-03", "2015-12-07", "2016-05-03",
"2015-12-07", "2016-05-03", "2015-12-07", "2016-05-03", "2015-12-07",
"2016-05-03", "2015-12-07", "2016-05-03"), Topic = c("treasonous, demsinphilly, khan, manafort, notorious, vpdebate, gloves, mcmullin",
"treasonous, demsinphilly, khan, manafort, notorious, vpdebate, gloves, mcmullin",
"clinton, debate, hillary, ryan, women, paul, tape, rally", "clinton, debate, hillary, ryan, women, paul, tape, rally",
"debates, debatenight, hillary, clinton, hillaryclinton, vote, debate, women",
"debates, debatenight, hillary, clinton, hillaryclinton, vote, debate, women",
"will, hillary, like, just, people, president, vote, said", "will, hillary, like, just, people, president, vote, said",
"critics, bills, aswafbjtet, policy, up.trump, jekyll, tmjr7gwqze, 10pm",
@plablo09
plablo09 / extract_all.py
Created Jun 2, 2016
Extract all shapes and import them to postgis
View extract_all.py
#!/usr/bin/python
# -*- coding: utf-8 -*-
import glob
import os
import zipfile
print 'hey'
w_dir = '/home/plablo/tmp_data/'
files = glob.glob(w_dir + '*.zip')
@plablo09
plablo09 / csv2pgsql.py
Last active Feb 23, 2016
Parse and import a csv with points to postgres/poststgis
View csv2pgsql.py
# -*- coding: utf-8 -*-
import psycopg2 as psy
from datetime import datetime
import time
import csv
import json
def date_formatter(date_string):
"""Returns datetime object"""
@plablo09
plablo09 / csv2geojson.py
Last active Dec 11, 2015
quick csv to geojson
View csv2geojson.py
import csv
import json
csv_data = csv.reader(open('MonthMap.csv', 'rb'), delimiter='\t')
features = []
for i, row in enumerate(csv_data):
try:
f = {
"type" : "Feature",
@plablo09
plablo09 / select-poly.sql
Created Nov 20, 2015
select inside polygon
View select-poly.sql
select 1 as id, st_geomfromtext(
'POLYGON ((-99.5408690000000007 19.9525709999999989, -99.5435870000000023 19.9554469999999995, -99.3506809999999945 20.0420480000000012, -99.3361179999999990 20.0463349999999991,
-99.1990940000000023 20.2146280000000012,
-99.1924779999999942 20.2391210000000008,
-99.1860319999999973 20.2430979999999998,
-99.1860300000000024 20.2430990000000008,
-98.9455770000000001 20.2654850000000017,
-98.9449809999999985 20.2654619999999994,
-98.9423579999999987 20.2606110000000008,
-98.8173819999999949 20.1350939999999987, -98.7599189999999965 20.1261369999999999, -98.7324749999999938 20.1274310000000014, -98.5636429999999990 20.2358669999999989, -98.4145270000000068 20.0966829999999987, -98.7129479999999973 20.0652369999999998, -98.5766100000000023 19.7826479999999982, -98.5761750000000063 19.7703230000000012, -98.5397349999999932 19.6572999999999993, -98.6620520000000027 19.3520900000000005,
You can’t perform that action at this time.