Instantly share code, notes, and snippets.

@plablo09 /querys.sql
Last active Sep 30, 2015

Embed
What would you like to do?
Usos de suelo (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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment