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

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 
					from (select * from network.ways where tag_id in (104,108,106,101)) as rp
					right join public.dim_colonia as c
					on st_crosses (rp.the_geom, c.colonia_geom_4326)
				) as gu 
			group by name, colonia_cve
			) as h
		group by colonia_cve
		) as deg
		join dim_colonia
		on dim_colonia.colonia_cve = deg.colonia_cve
), 
gs 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 
					from (select * from network.ways where tag_id = 110) as rp
					right join public.dim_colonia as c
					on st_crosses (rp.the_geom, c.colonia_geom_4326)
				) as gu 
			group by name, colonia_cve
			) as h
		group by colonia_cve
		) as deg
		join dim_colonia
		on dim_colonia.colonia_cve = deg.colonia_cve
), 
gr 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 
					from (select * from network.ways where tag_id = 112) as rp
					right join public.dim_colonia as c
					on st_crosses (rp.the_geom, c.colonia_geom_4326)
				) as gu 
			group by name, colonia_cve
			) as h
		group by colonia_cve
		) as deg
		join dim_colonia
		on dim_colonia.colonia_cve = deg.colonia_cve
)

select gp.colonia_cve, gp.colonia_geom_4326, 5*coalesce(gp.degree,0) + 3*coalesce(gs.degree,0) + coalesce(gr.degree,0) as grado_ponderado
from gp join gs on gp.colonia_cve = gs.colonia_cve
join gr on gr.colonia_cve = gs.colonia_cve