14. Ejercicios de Joins espaciales

Aquí tienes un recordatorio de algunas de las funciones que hemos visto. Pista: ¡serán útiles para los ejercicios!

• suma(expresión): función de agregación para devolver una suma de un conjunto de registros

• count(expression): devolver la cantidad de filas (registros) que retorna una consulta

• ST_Area(geometría) devuelve el área de la geometría

• ST_AsText(geometry) devuelve la representación de geometry en formato WKT

• ST_Contiene(geometría A, geometría B) devuelve verdadero si la geometría A contiene a la geometría B

• ST_Distance(geometría A, geometría B) devuelve la distancia mínima entre la geometría A y la geometría B

• ST_DWithin(geometría A, geometría B, radio) devuelve verdadero si la geometría A está a una distancia dentro del “radio” de la geometría B

• ST_GeomFromText(text) retorna un objeto geometry desde una definición WKT

• ST_Intersects(geometry A, geometry B) devuelve verdadero si la geometría A intersecta la geometría B

• ST_Length(linestring) retorna la longitud de la línea

• ST_Touches(geometry A, geometry B) devuelve verdadero si el límite de la geometría A toca la geometría B

• ST_Within(geometry A, geometry B) devuelve verdadero si la geometría A está dentro de la geometría B

También recuerde las tablas que tenemos disponibles:

• nyc_census_blocks

  • name, popn_total, boroname, geom

• nyc_streets

  • name, type, geom

• nyc_subway_stations

  • name, routes, geom

• nyc_neighborhoods

  • name, boroname, geom

14.1. Ejercicios

• ¿Qué estación de metro está en “Little Italy”? ¿En qué ruta de metro está?

  SELECT s.name, s.routes
  FROM nyc_subway_stations AS s
  JOIN nyc_neighborhoods AS n
  ON ST_Contains(n.geom, s.geom)
  WHERE n.name = 'Little Italy';
  

     name    | routes
  -----------+--------
   Spring St | 6
  

• ¿Cuáles son todos los vecindarios servidos por el tren 6? (Pista: la columna routes en la tabla nyc_subway_stations tiene valores como “B,D,6,V” y “C,6”)

  SELECT DISTINCT n.name, n.boroname
  FROM nyc_subway_stations AS s
  JOIN nyc_neighborhoods AS n
  ON ST_Contains(n.geom, s.geom)
  WHERE strpos(s.routes,'6') > 0;
  

          name        | boroname
  --------------------+-----------
   Midtown            | Manhattan
   Hunts Point        | The Bronx
   Gramercy           | Manhattan
   Little Italy       | Manhattan
   Financial District | Manhattan
   South Bronx        | The Bronx
   Yorkville          | Manhattan
   Murray Hill        | Manhattan
   Mott Haven         | The Bronx
   Upper East Side    | Manhattan
   Chinatown          | Manhattan
   East Harlem        | Manhattan
   Greenwich Village  | Manhattan
   Parkchester        | The Bronx
   Soundview          | The Bronx
  

  Nota

  Usamos la palabra clave DISTINCT para eliminar valores duplicados de nuestro conjunto de resultados cuando había más de una estación de metro en un vecindario.

• Después del 11/9, el vecindario “Battery Park” estuvo fuera de límites durante varios días. ¿Cuántas personas tuvieron que ser evacuadas?

  SELECT Sum(popn_total)
  FROM nyc_neighborhoods AS n
  JOIN nyc_census_blocks AS c
  ON ST_Intersects(n.geom, c.geom)
  WHERE n.name = 'Battery Park';
  

  17153
  

• ¿Qué vecindario tiene la mayor densidad de población (personas/km2)?

  SELECT
    n.name,
    Sum(c.popn_total) / (ST_Area(n.geom) / 1000000.0) AS popn_per_sqkm
  FROM nyc_census_blocks AS c
  JOIN nyc_neighborhoods AS n
  ON ST_Intersects(c.geom, n.geom)
  GROUP BY n.name, n.geom
  ORDER BY popn_per_sqkm DESC LIMIT 2;
  

        name       |  popn_per_sqkm
  -------------------+------------------
   North Sutton Area | 68435.13283772678
   East Village      | 50404.48341332535