13. Joins Espaciales

Las uniones espaciales son el pan de cada día de las bases de datos espaciales. Permiten combinar información de diferentes tablas usando relaciones espaciales como la clave de unión. Gran parte de lo que consideramos «análisis GIS estándar» puede expresarse como uniones espaciales.

En la sección anterior, exploramos relaciones espaciales usando un proceso de dos pasos: primero extrajimos un punto de estación de metro para “Broad St”; luego, usamos ese punto para hacer más preguntas como «¿en qué vecindario está la estación “Broad St”?»

Usando una unión espacial, podemos responder la pregunta en un solo paso, recuperando información sobre la estación de metro y el vecindario que la contiene:

SELECT
  subways.name AS subway_name,
  neighborhoods.name AS neighborhood_name,
  neighborhoods.boroname AS borough
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_subway_stations AS subways
ON ST_Contains(neighborhoods.geom, subways.geom)
WHERE subways.name = 'Broad St';

 subway_name | neighborhood_name  |  borough
-------------+--------------------+-----------
 Broad St    | Financial District | Manhattan

Podríamos haber unido cada estación de metro con su vecindario contenedor, pero en este caso queríamos información solo de una. Cualquier función que proporcione una relación verdadero/falso entre dos tablas puede usarse para realizar una unión espacial, pero las más comúnmente usadas son: ST_Intersects, ST_Contains, y ST_DWithin.

13.1. Unir y resumir

La combinación de un JOIN con un GROUP BY proporciona el tipo de análisis que usualmente se hace en un SIG.

Por ejemplo: «¿Cuál es la población y la composición racial de los vecindarios de Manhattan?» Aquí tenemos una pregunta que combina información de la población del censo con los límites de los vecindarios, con una restricción a solo un distrito de Manhattan.

SELECT
  neighborhoods.name AS neighborhood_name,
  Sum(census.popn_total) AS population,
  100.0 * Sum(census.popn_white) / Sum(census.popn_total) AS white_pct,
  100.0 * Sum(census.popn_black) / Sum(census.popn_total) AS black_pct
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_census_blocks AS census
ON ST_Intersects(neighborhoods.geom, census.geom)
WHERE neighborhoods.boroname = 'Manhattan'
GROUP BY neighborhoods.name
ORDER BY white_pct DESC;

  neighborhood_name  | population | white_pct | black_pct
---------------------+------------+-----------+-----------
 Carnegie Hill       |      18763 |      90.1 |       1.4
 North Sutton Area   |      22460 |      87.6 |       1.6
 West Village        |      26718 |      87.6 |       2.2
 Upper East Side     |     203741 |      85.0 |       2.7
 Soho                |      15436 |      84.6 |       2.2
 Greenwich Village   |      57224 |      82.0 |       2.4
 Central Park        |      46600 |      79.5 |       8.0
 Tribeca             |      20908 |      79.1 |       3.5
 Gramercy            |     104876 |      75.5 |       4.7
 Murray Hill         |      29655 |      75.0 |       2.5
 Chelsea             |      61340 |      74.8 |       6.4
 Upper West Side     |     214761 |      74.6 |       9.2
 Midtown             |      76840 |      72.6 |       5.2
 Battery Park        |      17153 |      71.8 |       3.4
 Financial District  |      34807 |      69.9 |       3.8
 Clinton             |      32201 |      65.3 |       7.9
 East Village        |      82266 |      63.3 |       8.8
 Garment District    |      10539 |      55.2 |       7.1
 Morningside Heights |      42844 |      52.7 |      19.4
 Little Italy        |      12568 |      49.0 |       1.8
 Yorkville           |      58450 |      35.6 |      29.7
 Inwood              |      50047 |      35.2 |      16.8
 Washington Heights  |     169013 |      34.9 |      16.8
 Lower East Side     |      96156 |      33.5 |       9.1
 East Harlem         |      60576 |      26.4 |      40.4
 Hamilton Heights    |      67432 |      23.9 |      35.8
 Chinatown           |      16209 |      15.2 |       3.8
 Harlem              |     134955 |      15.1 |      67.1

¿Qué está pasando aquí? En teoría (el orden de evaluación real es optimizado internamente por la base de datos) esto es lo que ocurre:

1. La cláusula JOIN crea una tabla virtual que incluye columnas tanto de la tabla de vecindarios como de la tabla del censo.

2. La cláusula WHERE filtra nuestra tabla virtual a solo filas en Manhattan.

3. Las filas restantes se agrupan por el nombre del vecindario y se procesan a través de la función de agregación Sum() de los valores de población.

4. Después de un poco de aritmética y formato (por ejemplo, GROUP BY, ORDER BY) sobre los números finales, nuestra consulta arroja los porcentajes.

Nota

La cláusula JOIN combina dos elementos de FROM. Por defecto, estamos usando un INNER JOIN, pero hay otros cuatro tipos de joins. Para más información ver la definición de join_type en la documentación de PostgreSQL.

También podemos usar pruebas de distancia como clave de unión, para crear consultas resumidas de «todos los elementos dentro de un radio». Exploremos la geografía racial de Nueva York usando consultas de distancia.

Primero, obtengamos la composición racial de referencia de la ciudad.

SELECT
  100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
  100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
  Sum(popn_total) AS popn_total
FROM nyc_census_blocks;

    white_pct     |    black_pct     | popn_total
------------------+------------------+------------
 44.0039500762811 | 25.5465789002416 |    8175032

Así, de los 8 millones de personas en Nueva York, aproximadamente 44% están registradas como «blancas» y 26% están registradas como «negras».

Duke Ellington una vez cantó «You / must take the A-train / To / go to Sugar Hill way up in Harlem.» Como vimos antes, Harlem tiene por mucho la mayor población afroamericana en Manhattan (80.5%). ¿Es lo mismo cierto para el tren A de Duke?

Primero, nota que el contenido del campo routes de la tabla nyc_subway_stations es lo que nos interesa para encontrar el tren A. Los valores allí son un poco complejos.

SELECT DISTINCT routes FROM nyc_subway_stations;

A,C,G
4,5
D,F,N,Q
5
E,F
E,J,Z
R,W

Nota

La palabra clave DISTINCT elimina filas duplicadas del resultado. Sin la palabra clave DISTINCT, la consulta anterior identifica 491 resultados en lugar de 73.

Así que para encontrar el tren A, queremos cualquier fila en routes que tenga una “A”. Podemos hacerlo de varias formas, pero hoy usaremos el hecho de que strpos(routes,'A') devolverá un número distinto de cero solo si “A” está en el campo routes.

SELECT DISTINCT routes
FROM nyc_subway_stations AS subways
WHERE strpos(subways.routes,'A') > 0;

A,B,C
A,C
A
A,C,G
A,C,E,L
A,S
A,C,F
A,B,C,D
A,C,E

Resumamos la composición racial dentro de 200 metros de la línea del tren A.

SELECT
  100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
  100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
  Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
WHERE strpos(subways.routes,'A') > 0;

    white_pct     |    black_pct     | popn_total
------------------+------------------+------------
 45.5901255900202 | 22.0936235670937 |     189824

Así que la composición racial a lo largo del tren A no es radicalmente diferente de la composición de la ciudad de Nueva York en su conjunto.

13.2. Joins avanzados

En la última sección vimos que el tren A no servía a una población que difiriera mucho de la composición racial del resto de la ciudad. ¿Hay algunos trenes que tengan una composición racial no promedio?

Para responder a esa pregunta, añadiremos otra unión a nuestra consulta, de modo que podamos calcular simultáneamente la composición de muchas líneas de metro a la vez. Para hacer eso, necesitaremos crear una nueva tabla que enumere todas las líneas que queremos resumir.

CREATE TABLE subway_lines ( route char(1) );
INSERT INTO subway_lines (route) VALUES
  ('A'),('B'),('C'),('D'),('E'),('F'),('G'),
  ('J'),('L'),('M'),('N'),('Q'),('R'),('S'),
  ('Z'),('1'),('2'),('3'),('4'),('5'),('6'),
  ('7');

Ahora podemos unir la tabla de líneas de metro a nuestra consulta original.

SELECT
  lines.route,
  100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
  100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
  Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
JOIN subway_lines AS lines
ON strpos(subways.routes, lines.route) > 0
GROUP BY lines.route
ORDER BY black_pct DESC;

 route | white_pct | black_pct | popn_total
-------+-----------+-----------+------------
 S     |      39.8 |      46.5 |      33301
 3     |      42.7 |      42.1 |     223047
 5     |      33.8 |      41.4 |     218919
 2     |      39.3 |      38.4 |     291661
 C     |      46.9 |      30.6 |     224411
 4     |      37.6 |      27.4 |     174998
 B     |      40.0 |      26.9 |     256583
 A     |      45.6 |      22.1 |     189824
 J     |      37.6 |      21.6 |     132861
 Q     |      56.9 |      20.6 |     127112
 Z     |      38.4 |      20.2 |      87131
 D     |      39.5 |      19.4 |     234931
 L     |      57.6 |      16.8 |     110118
 G     |      49.6 |      16.1 |     135012
 6     |      52.3 |      15.7 |     260240
 1     |      59.1 |      11.3 |     327742
 F     |      60.9 |       7.5 |     229439
 M     |      56.5 |       6.4 |     174196
 E     |      66.8 |       4.7 |      90958
 R     |      58.5 |       4.0 |     196999
 N     |      59.7 |       3.5 |     147792
 7     |      35.7 |       3.5 |     102401

Como antes, las uniones crean una tabla virtual de todas las combinaciones posibles disponibles dentro de las restricciones de JOIN ON, y esas filas luego se pasan a un resumen con GROUP. La magia espacial está en la función ST_DWithin, que asegura que solo los bloques censales cercanos a las estaciones de metro apropiadas se incluyan en el cálculo.

13.3. Lista de funciones

ST_Contains(geometry A, geometry B): Devuelve true si y solo si ningún punto de B se encuentra en el exterior de A, y al menos un punto del interior de B se encuentra en el interior de A.

ST_DWithin(geometry A, geometry B, radius): Devuelve true si las geometrías están dentro de la distancia especificada entre sí.

ST_Intersects(geometry A, geometry B): Devuelve TRUE si las geometrías/geografías «intersectan espacialmente» (comparten alguna porción del espacio) y FALSE si no lo hacen (son disjuntas).

round(v numeric, s integer): Función matemática de PostgreSQL que redondea a s decimales

strpos(string, substring): Función de cadena de PostgreSQL que devuelve una ubicación entera de una subcadena especificada.

sum(expresión): Función de PostgreSQL que devuelve la suma de los valores de un conjunto de registros.