Cómo hacer un join no espacial en una base PostGis

En el post anterior contemplamos la transformación del sistema de referencia espacial de la columna de geometría de una base PostGis que había sido conformada con la incorporación de un shapefile (Geometría: MULTILINESTRING) de la red vial de los Estados Unidos. Si se explora la tabla de atributos, se podrá observar que el campo ‘state’ contiene sólo los códigos de 2 letras que corresponden a los estados y no el nombre en extenso del mismo. Por tanto, para lograr una salida más legible en las consultas que incluyan este campo es necesario hacer un join no espacial dentro de la base PostGIS.

Antes de producir la base de datos sin componente espacial vamos a realizar una consulta para saber cuantos códigos de estado tenemos dentro de la base PostGis. Para ello, utilizamos en la consola de PostgreSQL la siguiente serie de comandos:

SELECT DISTINCT state 
    FROM roads 
ORDER BY state;

 state 
-------
 AK
 AL
 AR
 AZ
 CA
 CO
 CT
 .
 .
 .
 VT
 WA
 WI
 WV
 WY
(53 filas)

donde se visualiza que existen 53 códigos de nombre. Con esta información, creé la tabla ‘usa_states’, con las variables ‘state_code’ y ‘state_name’, y copié en su interior los valores correspondientes a partir de un archivo CSV (usa_states.csv); tal como se expresa a continuación:

CREATE TABLE usa_states(state_code char(4) PRIMARY KEY, state_name varchar(100));
COPY usa_states (state_code, state_name)
FROM '/home/zeito/Desktop/usa_states.csv' WITH CSV HEADER;

A continuación se corrobora, que la tabla ha sido configurada como se esperaba.

SELECT state_code, state_name 
    FROM usa_states 
    ORDER BY state_name;

 state_code |      state_name       
------------+-----------------------
 AL         |  Alabama
 AK         |  Alaska
 AZ         |  Arizona
 AR         |  Arkansas
 CA         |  California
 CO         |  Colorado
 CT         |  Connecticut
 DE         |  Delaware
 DC         |  District of Columbia
 .
 .
 .
 UT         |  Utah
 VT         |  Vermont
 VA         |  Virginia
 VI         |  Virgin Island
 WA         |  Washington
 WV         |  West Virginia
 WI         |  Wiscosin
 WY         |  Wyoming
(53 filas)

Antes de hacer el join en la base PostGIS, voy a producir la salida “normal” en la que quiero averiguar cuantas vías del tipo “Cruce de Ferry” (“Ferry_Crossing”) existen por estado. La serie de comandos es la siguiente; conjuntamente con su respuesta:

SELECT DISTINCT state,
	COUNT(feature) AS Ferry_Crossing
FROM roads 
WHERE feature LIKE 'Ferry Crossing%'
GROUP BY state
ORDER BY Ferry_Crossing DESC;

 state | ferry_crossing 
-------+----------------
 AK    |             51
 WA    |             15
 MI    |             10
 MA    |              8
 NY    |              8
 CT    |              6
 NC    |              3
 RI    |              3
 VT    |              3
 CA    |              1
 DE    |              1
 IL    |              1
 NJ    |              1
 TX    |              1
 WI    |              1
(15 filas)

Para que en una nueva consulta aparezcan ahora los nombres de los estados el join de la base de datos con componente espacial ‘roads’, con la base sin componente espacial ‘usa_states’, puede hacerse de la manera siguiente:

SELECT DISTINCT us.state_name,
	COUNT(r.feature) AS Ferry_Crossing
FROM roads AS r 
	INNER JOIN usa_states AS us
	ON r.state = us.state_code
WHERE r.feature LIKE 'Ferry Crossing%'
GROUP BY us.state_name
ORDER BY Ferry_Crossing DESC;

   state_name    | ferry_crossing 
-----------------+----------------
  Alaska         |             51
  Washington     |             15
  Michigan       |             10
  Massachusetts  |              8
  New York       |              8
  Connecticut    |              6
  North Carolina |              3
  Rhode Island   |              3
  Vermont        |              3
  California     |              1
  Delaware       |              1
  Illinois       |              1
  New Jersey     |              1
  Texas          |              1
  Wiscosin       |              1
(15 filas)

La salida anterior permite ahora observar claramente que Alaska, Washington y Michigan encabezan la lista con 51, 15 y 10 vías acuáticas de este tipo.

Esta entrada fue publicada en Postgres+postgis, SIG. Guarda el enlace permanente.

Responder

Por favor, inicia sesión con uno de estos métodos para publicar tu comentario:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s