Consultas SQL en bases de datos PostGres + PostGis en Debian

En unos posts pasados (1, 2) consideramos la instalación de PostGres + PostGis en Debian 8, la creación de una base de datos con componente espacial, la inclusión de un shapefile a la base PostGis y la conexión y visualización de los rasgos espaciales de ésta a través de QGIS. En este nuevo post trataremos de las consultas a la base de datos PostGis que se ha obtenido a partir del ya conocido shapefile world_borders.

Después de haber creado la base de datos world_borders, en consola, nos hacemos superusuario y conectamos como un usuario particular (en mi caso zeito) a ésta. El proceso se resume a continuación:

#su - zeito
psqpl world_borders
Contraseña: 
psql (9.4.1)
conexión SSL (protocolo: TLSv1.2, cifrado: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compresión: desactivado)
Digite «help» para obtener ayuda.

world_borders=#

Con el comando \d listamos todas las tablas que están adscritas a la base de datos donde la penúltima corresponde a world_borders.

                Listado de relaciones
 Esquema |        Nombre         |   Tipo    | Dueño 
---------+-----------------------+-----------+-------
 public  | geography_columns     | vista     | zeito
 public  | geometry_columns      | vista     | zeito
 public  | raster_columns        | vista     | zeito
 public  | raster_overviews      | vista     | zeito
 public  | spatial_ref_sys       | tabla     | zeito
 public  | world_borders         | tabla     | zeito
 public  | world_borders_gid_seq | secuencia | zeito
(7 filas)

world_borders=# 

Con la siguiente serie de comandos consultamos el nombre de los campos que contiene la tabla atributiva (si lo desea, puede hacer lo mismo con pgadmin3 en modo gráfico o de la manera más fácil con \d world_borders):

world_borders=# SELECT COLUMN_NAME
world_borders-# FROM INFORMATION_SCHEMA.COLUMNS
world_borders-# WHERE TABLE_NAME='world_borders';
 column_name 
-------------
 gid
 cat
 fips_cntry
 cntry_name
 area
 pop_cntry
 geom
(7 filas)

world_borders=#

Ahora realizamos nuestra primera consulta por cntry_name que corresponde al nombre de países. La lista finaliza con el caracter dos puntos (:). Podemos avanzar en ella con las teclas TAB o las teclas del cursor. Para salir de ese modo pulsamos q. Observe que la lista no esta ordenada y tiene registros repetidos.

SELECT cntry_name FROM world_borders;

             cntry_name             
------------------------------------
 Aruba
 Antigua and Barbuda
 Antigua and Barbuda
 Algeria
 Azerbaijan
 Albania
 Azerbaijan
 Azerbaijan
 Armenia
 Azerbaijan
 Armenia
 Angola
 Angola
 Angola
 American Samoa
 American Samoa
 American Samoa
 American Samoa
 American Samoa
 Argentina
 Argentina
:

Para eliminar los registros repetidos usamos el predicado DISTINCT.

SELECT DISTINCT cntry_name FROM world_borders;

             cntry_name             
------------------------------------
 Costa Rica
 Cambodia
 Eritrea
 Paracel Islands
 Turkey
 Tanzania, United Republic of
 Chad
 Cyprus
 Cayman Islands
 Kiribati
 Spratly Islands
 Slovenia
 Western Samoa
 Vietnam
 Jamaica
 Kuwait
 Antigua and Barbuda
 Cameroon
 St. Lucia
 Cape Verde
 Saudi Arabia
:

A continuación seleccionamos dos campos (cntry_name y area) y con la clausula ORDER BY ordenamos la lista por nombre de país (cntry_name):

world_borders=# SELECT DISTINCT cntry_name, area FROM world_borders
world_borders-# ORDER BY cntry_name;

             cntry_name             |   area   
------------------------------------+----------
 Afghanistan                        |   647500
 Albania                            |    28748
 Algeria                            |  2381740
 American Samoa                     |      199
 Andorra                            |      468
 Angola                             |  1246700
 Anguilla                           |      102
 Antarctica                         |        1
 Antigua and Barbuda                |      443
 Argentina                          |  2766890
 Armenia                            |    29800
 Aruba                              |      193
 Australia                          |  7686850
 Austria                            |    83858
 Azerbaijan                         |    86600
 Bahamas, The                       |    13940
 Bahrain                            |      665
 Baker Island                       |        1
 Bangladesh                         |   144000
 Barbados                           |      431
 Belgium                            |    30510
:

Dentro de la base de datos world_borders creamos una nueva TABLA, con la orden INTO, llamada paises; que incluye sólo dos campos (cntry_name, area) y está ordenada por cntry_name. Observe que se seleccionaron 251 registros. Con \d paises se observa cuales son las columnas y el tipo de variable que alberga la tabla paises. Si se conecta a world_borders con QGIS podrá acceder a ella marcando la opción de cargar tablas que no contienen componente espacial.

world_borders=# SELECT DISTINCT cntry_name, area INTO paises FROM world_borders ORDER BY cntry_name;
SELECT 251
world_borders=# \d paises
               Tabla «public.paises»
  Columna   |         Tipo          | Modificadores 
------------+-----------------------+---------------
 cntry_name | character varying(80) | 
 area       | double precision      | 

Utilizando nuestra primera función de agregación, COUNT, para determinar cuantos registros tenemos en la base de datos world_borders. Escogemos el campo cuyo nombre es más fácil de recordar: area.

world_borders=# SELECT count(area) FROM world_borders;
 count 
-------
  3784
(1 fila)

world_borders=#

Ahora vamos a utilizar para nuestras búsquedas la tabla países; que sólo contiene 251 registros y ya están ordenados en orden alfabético. Comenzaremos con utilizar la combinación de la clausula WHERE y la función de agregación MAX para encontrar el país con el área máxima. La sintaxis es la siguiente:

world_borders=# SELECT cntry_name,area FROM paises WHERE area = (SELECT max(area) FROM paises);
 cntry_name |   area   
------------+----------
 Russia     | 17075200
(1 fila)

Ahora los países con el área mínima:

world_borders=# SELECT cntry_name,area FROM paises WHERE area = (SELECT min(area) FROM paises);
             cntry_name             | area 
------------------------------------+------
 Antarctica                         |    1
 Baker Island                       |    1
 Bouvet Island                      |    1
 British Indian Ocean Territory     |    1
 French Southern & Antarctic Lands  |    1
 Gaza Strip                         |    1
 Glorioso Islands                   |    1
 Heard Island & McDonald Islands    |    1
 Howland Island                     |    1
 Jan Mayen                          |    1
 Jarvis Island                      |    1
 Johnston Atoll                     |    1
 Juan De Nova Island                |    1
 Midway Islands                     |    1
 Paracel Islands                    |    1
 South Georgia and the South Sandwi |    1
 Spratly Islands                    |    1
 St. Helena                         |    1
 St. Kitts and Nevis                |    1
 St. Lucia                          |    1
 St. Pierre and Miquelon            |    1
 St. Vincent and the Grenadines     |    1
 Virgin Islands                     |    1
 Wake Island                        |    1
 West Bank                          |    1
(25 filas)

(END)

Para finalizar, dos formas de calcular el promedio del área y la desviación estandar:

world_borders=# SELECT SUM(area)/COUNT(area) AS promedio, AVG(area), STDDEV(area) FROM paises;
     promedio     |       avg        |      stddev      
------------------+------------------+------------------
 541549.852589641 | 541549.852589641 | 1710885.52961272
(1 fila)
Esta entrada fue publicada en Postgres+postgis, SIG. Guarda el enlace permanente.

3 respuestas a Consultas SQL en bases de datos PostGres + PostGis en Debian

  1. Para ver las columnas y tipo de una tabla también se puede usar el comando:

    \d+ [nombre de tabla]
    
  2. Pingback: Consultas SQL en bases de datos PostGres + PostGis mediante lenguaje R | El Blog de José Guerrero

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