Usos de suelo (sql)

querys.sql

--Tenemos dos tablas, una con los datos para las colonias (colonias_final) y otra con los nombres de las colonias (colonias_names). Lo que quiero hacer es pegarle los nombres de las colonias a los datos
--Para hacer el spatial join voy a usar un punto al interior de las colonias porque las geomeetrías no coinciden perfectamente (no quiero usar st_centroid porque las geometrías de las colonias son caprichosas y el centroide puede caer fuera de la colonia). Para poder usar índices, voy a crear una tabla intermedia con los centroides de la capa con nombres

--Creamos la tabla de centroides
create table centro_col_nombres as select gid, nombre, st_PointOnSurface(geom) as geom
from colonias_names;

--Le ponemos índice
create index sidx_centro_col_nombres_geom on centro_col_names USING GIST (geom);

--Agregamos la columna para el nombre en la tabla de destino
alter table colonias_final add column nombre text;

--Le damos valor al nombre
update colonias_final c set nombre = foo.nombre
from
(select d.id_colonia, n.nombre 
from colonias_final d
join centro_col_nombres n
on st_intersects(d.geom , n.geom)) as foo
where foo.id_colonia = c.id_colonia

--Ahora vamos a pegarle a cada manzana (tabla manzanas_zmvm) el id de la colonia a la que pertenece, otra vez vamos a usar st_Point_On_Surface para evitar problemas con las geometrías:

--Creamos la tabla con los centroides de las manzanas y le creamos un índice espacial
create table centro_manzanas as select gid, st_PointOnSurface(geom) as geom
from manzanas_zmvm;
create index sidx_centro_manzanas_geom on centro_manzanas USING GIST (geom);

--Agregamos una columna para el id de la colonia a la tabla original de manzanas:
alter table manzanas_zmvm add column id_colonia integer;

--Populamos la columna con los ids de las colonias:
update manzanas_zmvm mz set id_colonia = foo.id_colonia
from
(select m.gid, c.id_colonia from 
centro_manzanas m
join colonias_final c
on st_intersects(m.geom,c.geom)) as foo
where mz.gid = foo.gid

--Ahora vamos a crear una tabla de colonias con la geometría de las manzanas, aquí hay un par de trucos: ST_Multi es mucho más rápido que ST_Union pero si se usa sobre geometrías multi regresa colecciones, por eso usamos el st_dump:
SELECT  f.id_colonia, ST_Multi(ST_Collect(f.geom)) as geom 
FROM
(SELECT  gid, id_colonia, (ST_Dump(geom)).geom As geom
FROM manzanas_zmvm) as f
GROUP BY f.id_colonia 

--Ya sólo falta pegarle a esta nueva geometría de colonias los datos de la tabla colonias_final:
create table colonias_multi as 
select d.id_colonia, d. poblacion, d.vivienda, d.servicios, d.comercio, d.ocio, d.entropia_c as entropia, d.nombre, m.geom
from 
(SELECT  f.id_colonia, ST_Multi(ST_Collect(f.geom)) as geom 
FROM
(SELECT  gid, id_colonia, (ST_Dump(geom)).geom As geom
FROM manzanas_zmvm) as f
GROUP BY f.id_colonia) as m
join colonias_final d
on d.id_colonia = m.id_colonia