plablo09 / neigborhood_connectivity_degree.sql

Created December 6, 2019 15:59

SQL (PostGis) script to calculate a weighted (by street type) connectivity degree (also known as permeability) at the neighborhood level

	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 / natural_cities.sql

Last active August 14, 2019 19:35

Pure SQL implementation of Bin Jiang Natural Cities Algorithm

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

plablo09 / spatial_join.py

Last active March 5, 2019 16:49

	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 / contour_lines.sql

Created November 26, 2018 19:19

Contour lines from irregular points

	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 / README.md

Last active March 22, 2018 00:47 — forked from TennisVisuals/README.md

reusable updating radarChart

plablo09 / topics data frame

Created February 25, 2017 00:27

	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 / prepare_data.sql

Last active March 25, 2022 16:26

twitter_context database process

plablo09 / extract_all.py

Created June 2, 2016 18:50

Extract all shapes and import them to postgis

plablo09 / csv2pgsql.py

Last active February 23, 2016 23:05

Parse and import a csv with points to postgres/poststgis

	# -- 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 / csv2geojson.py

Last active December 11, 2015 17:33

quick csv to geojson

	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",

pablo lopez plablo09

reusable updating radarChart