Download Creación de una instancia de base de datos postGIS.

Práctica PostgreSQL + PostGIS.
Índice
Instalación ............................................................................................................................................ 3
Windows xp..................................................................................................................................... 3
Kubuntu 7.04 ................................................................................................................................... 8
Conceptos básicos ................................................................................................................................ 9
Creación de una instancia de base de datos postGIS. ..................................................................... 9
Windows XP ............................................................................................................................... 9
Kubuntu 7.04 ............................................................................................................................ 10
Crear una capa postGIS con gvSIG ............................................................................................... 11
Crear una tabla postGIS con shp2pgsql ........................................................................................ 13
Cargar una capa PostGIS en gvSIG .............................................................................................. 14
Conceptos avanzados en PostgresSQL .............................................................................................. 15
Esquemas ....................................................................................................................................... 15
Roles y privilegios ......................................................................................................................... 16
Creación de roles de grupo. ..................................................................................................... 17
Creación de roles de login ........................................................................................................ 18
Asignar privilegios en un esquema. .......................................................................................... 18
Asignar privilegios en un objeto de un esquema ...................................................................... 19
Comprobación de los privilegios con gvSIG ............................................................................ 20
Configuración de parámetros (postgresql.conf) ............................................................................ 21
Seguridad a nivel de red (pg_hba.conf) ........................................................................................ 22
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
1
Copias de seguridad ...................................................................................................................... 22
Conceptos avanzados en PostGIS ...................................................................................................... 24
Figuras
Illustration 1: Página de descarga de PostgreSQL ............................................................................... 5
Illustration 2: Programa de instalación de Postgres en Windows ........................................................ 5
Illustration 3: Configuración del servicio de PostgreSQL en windows ............................................... 7
Illustration 4: Modificar la variable de entorno PATH ........................................................................ 9
Illustration 5: Menú de Postgres en Windows ..................................................................................... 9
Illustration 6: Herramienta de administración pgAdmin III .............................................................. 10
Illustration 7: Crear una nueva base de datos con pgAdmin III ......................................................... 12
Illustration 8: Parámetros en pgAdmin III para crear una bd ........................................................... 15
Illustration 9: carga de un shapefile en gvSIG ................................................................................... 18
Illustration 10: Exportar capa con gvSIG .......................................................................................... 19
Illustration 11: Parámetros de conexión al exportar una capa en gvSIG ........................................... 20
Illustration 12: Conexión GeoBD gvSIG+ ........................................................................................ 22
Illustration 13: Creación de esquemas con pgAdminIII .................................................................... 25
Illustration 14: Creación de un grupo en postgreSQL ....................................................................... 26
Illustration 15: Rol de grupo al que pertenece un usuario ................................................................. 27
Illustration 16: Privilegios en un esquema ......................................................................................... 28
Illustration 17: Privilegios en un esquema ......................................................................................... 29
Illustration 18: Miembros de un grupo .............................................................................................. 30
Instalación
Windows xp1
Para estas prácticas emplearemos la versión 8.2.4.

1
En el caso que tuviésemos instalado el programa cygwin, tendremos que eliminar de la variable de entorno PATH la ruta a
los binarios de este programa. Para acceder a las variables de entorno, desde un explorador de archivos, pulsar el botón derecho
del ratón sobre MiPC, y en Propiedades/Opciones Avanzadas/Variables de entorno.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
2
Illustration 1: Página de descarga de PostgreSQL
En primer lugar descomprimiremos el fichero postgresql-8.2.4-1.zip que encontraremos en el
directorio del cdrom data/software/win. Este fichero puede descargarse también en
http://www.postgresql.org/ftp/binary/v8.2.4/win32/
Una vez descargado lo descomprimiremos y ejecutaremos el fichero postgresql-8.2.msi.
Primero deberemos seleccionar el idioma. Como el español no está disponible podemos seleccionar
el inglés. Le damos a Siguiente.
En la siguiente ventana seleccionamos "PostGIS Spatial Extensions" y le decimos que lo instale en
nuestro disco duro local y le damos a Siguiente. Con esta opción estamos incluyendo el módulo de
PostGIS en la instalación.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
Illustration 2: Programa de instalación de Postgres en Windows
3
En la nueva ventana señalamos “Install as a service2” para que arranque Postgres cuando arranca el
sistema. También indicamos un nombre de usuario y contraseña que añadirá al sistema si no existe
y con el que se ejecutará la base de datos. Podemos poner, por ejemplo, "postgres" como usuario, y
"postgres" como contraseña. Le damos a Siguiente.
Illustration 3: Configuración del servicio de PostgreSQL en windows
En el siguiente paso el asistente nos pide varios parámetros sobre la configuración de la base de
datos:

Inidicamos que inicialice el cluster.

Seleccionamos el puerto de escucha (5432).

Permitimos que se pueda acceder desde otras máquinas a la base de datos.

Seleccionamos el lenguaje (Español).

Seleccionamos la codificación por defecto de las instancias de bases de datos, por ejemplo,
latin9.

Finalmente indicamos el nombre y contraseña que tendrá el superusuario de la base de
2 Para ver los servicios instalados en windows xp ir a Panel de control -> Rendimiento y mantenimiento>Herramientas Administrativas -> Servicios. Aunque la instalación crea el servicio automáticamente para que se
inicie al arrancar la máquina, en el caso que falle, habrá que comprobar que el servicio está en estado “iniciado” y
que se arranca “automáticamente”.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
4
datos, por ejemplo, “postgres” y contraseña “postgres”. No hay que confundir este usuario
(el que emplearemos para conectarnos a la base de datos) con el usuario que utiliza el
sistema operativo para ejecutar la base de datos (el que comentábamos anteriormente).
El resto de la instalación se completa pulsando a "Siguiente”.
Una vez finalizada la instalación, si queremos disponer de las herramientas de de PostgreSQL y
PostGIS que se ejecutan en un intérprete de comandos (pg_dump,shp2pgsql...), ponemos el
directorio bin de la instalación de Postgres (C:\Archivos de programa\PostgreSQL\8.2\bin) en la
varialbe de entorno PATH. Lo añadiremos desde un explorador de archivos, dándole al botón
derecho del ratón sobre MiPC, y en Propiedades/Opciones Avanzadas/Variables de entorno.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
5
Para comprobar la correctaIllustration
instalación
ejecutar
la herramienta
4: Modificar
la variable
de entorno de administración pgAdmin3 (Inicio
PATH
-> Programas -> PostgresSQL8.2
-> pgAdmin III).
Illustration 5: Menú de Postgres en Windows
Nos conectamos al servidor localhost haciendo doble click en el icono, nos solicitará la contraseña
del usuario postgres (usuario de la base de datos), al validarnos veremos una serie de objetos que se
han creado en la instalación , los más importantes son:

Bases de datos: son las instancias de bases de datos creadas, por defecto se crea la base de
datos “postgres” y la base de datos con soporte geoespacial “template_postgis”. Esta
instancia de base de datos contiene las funciones, procedimientos, tipos de datos y más
objetos necesarios para manejar información vectorial georreferenciada.

Esquemas: son los contenedores de información de una instancia de base de datos. Una base
de datos puede tener varios esquemas. Entre bases de datos no se puede ver la información
pero sí entre esquemas dentro de la misma bd. Por defecto se crea un esquema public.

Tablas: son las unidades contenedoras de información dentro de un esquema. Por defecto, se
habrán creado dos tablas (tal como se especifica en la norma SFA:
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
6

geometry_columns: tabla con información sobre las tablas que contienen alguna
columna con geometrías georeferenciadas.

spatial_ref_sys: tabla con información sobre los sistemas de referencia empleados en la
georreferenciación de las geometrías.
Kubuntu 7.04
Illustration 6: Herramienta de administración pgAdmin III
La versión de postgres para linux que hemos empleado en la práctica es 8.1.
En primer lugar instalamos la versión de PostgreSQL que incluye la extensión PostGIS, para ello,
en una shell ejecutar el siguiente comando3:
sudo aptitude install postgresql-8.1-postgis
El segundo paso es instalar la herramienta de administración pgAdmin3
sudo aptitude install pgadmin3
De la misma forma que comentábamos antes, podemos comprobar la correcta instalación abriendo
el programa pgAdmin3.
Hay que destacar que en la instalación en kubuntu
no se crea por defecto una instancia de base de
3 Hay que tener activados los repositorios universe en el fichero /etc/apt/sources.list
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
7
datos con la plantilla de postGIS, por tanto, sólo
encontraremos las plantillas del sistema
template0 y template1.Conceptos básicos
Una vez instalada la base de datos y comprobado su correcto funcionamiento, abordaremos los
conceptos más básicos como puede ser la creación de una instancia de base de datos postGIS, la
carga de datos vectoriales en un tabla y su posterior eliminación.
Creación de una instancia de base de datos postGIS.
Tras instalar el software, crearemos una instancia de base de datos que utilizaremos en el resto de la
práctica.
Windows XP
Crearemos una nueva base de datos empleando la plantilla que viene por defecto, template_postgis.
El uso de esta plantilla nos garantiza que la instancia de base de datos creada tendrá todos los
objetos de postGIS (las tablas geometry_columns y spatial_ref_sys, funciones, tipos de datos, casts,
...)
El primer paso es ejecutar la herramienta pgAdmin3 conectándonos con el usuario “postgres”.
Sobre el icono “Bases de datos” , botón derecho del ratón Nueva Base de Datos ...
Illustration 7: Crear una nueva base de datos con pgAdmin III
Aparecerá un cuadro
de diálogo en el que indicaremos los siguiente parámetros:

Nombre: nombre de la instancia de base de datos, en nuestro caso le llamaremos “postgis”.

Codificado: codificación de los caracteres de la base de datos, en nuestro caso “latin9”
puesto que las fuentes de datos que emplearemos están en esta codificación. Es
recomendable emplear la codificación “utf-8” como comentaremos más adelante. De
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
8
momento, y para simplificar, emplearemos esta “obsoleta” codificación.

Plantilla: emplearemos la plantilla que viene con la instalación para la generación de bases
de datos postgis, “template_postgis”.
De momento, nos centraremos en éstos parámetros, más adelante veremos el resto.
Illustration 8: Parámetros en pgAdmin III para crear una bd
Kubuntu 7.04
Puesto que en la instalación por defecto no hay una plantilla para crear instancias de bases de datos
postGIS, crearemos una de nombre “template_postgis” para ello ejecutararemos en una shell los
comandos siguientes4:
Con este primer comando creamos una nueva instancia de base de datos de nombre
“template_postgis” con el usuario postgres5.
createdb -U postgres template_postgis
Damos soporte para el lenguaje procedural pl/pgsql en la base de datos.
4 pgAdmin3 puede realizar las mismas acciones que estos comandos.
5 Típicamente se llama a este usuario con privilegios totales sobre todas las instancias de base de datos, DBA (Data
Base Administrator).
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
9
createlang -U postgres plpgsql template_postgis
Ejecutamos el script SQL que carga los objetos que convierten una base de datos normal en un base
de datos postGIS:
psql -U postgres -d template_postgis -f /usr/share/postgresql-8.1-postgis/lwpostgis.sql
Finalmente cargamos la tabla con los sistemas de referencia:
psql -U postgres -d template_postgis -f /usr/share/postgresql-8.1-postgis/spatial_ref_sys.sql
En este punto ya disponemos de la plantilla para crear bases de datos postGIS. Para crear una base
de datos de nombre “postgis” bastaría con ejecutar:
createdb -U postgres -T template_postgis postgis
Crear una capa postGIS con gvSIG
Tras la creación de una instancia de base de datos postGIS, emplearemos gvSIG para crear unas
tablas con información vectorial. Recordemos los parámetros de conexión:

Usuario: postgres

Password: postgres

Host(máquina): localhost

Puerto: 5432

Base de datos: postgis

Esquema: public
Empezaremos esta parte de la práctica creando una nueva vista en gvSIG. Tras ello cargamos el
shapefile carreteras.shp que encontraremos en el directorio “Cartografia/postGIS” del cdrom.
(Vista -> Añadir Capa -> Archivo)
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
10
Illustration 9: carga de un shapefile en gvSIG
Una vez cargada la capa, la exportaremos a postGIS con su utilidad de exportación de capas 6 (Capa
-> Exportar a ... -> postGIS)
6 Para poderse exportar una capa debe estar seleccionada.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
11
Indicamos el nombre de la tabla que contendrá la información geográfica y alfanumérica del
shapefile, le podemos llamar carreteras, por ejemplo.
Tras este paso, aparecerá
diálogo
donde especificaremos los parámetros de conexión a
Illustrationun
10:cuadro
Exportarde
capa
con gvSIG
la base de datos que comentábamos al principio de este capítulo.
Illustration 11: Parámetros de conexión al exportar una
Tras cargar los datos en postGIS,
el programa nos preguntará si deseamos cargar la capa recién
capa en gvSIG
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
12
creada en la vista. Responderemos afirmativamente a esta cuestión con la finalidad de ver la tabla
recién creada.
Práctica Libre: probar a cargar distintos tipos de ficheros vectoriales como DWGs, DXFs, DGNs,
etc ...
Crear una tabla postGIS con shp2pgsql
PostGIS tiene su propio cargador para shapefiles, se trata del comando shp2pgsql. Para probar su
funcionamiento ejecutaremos en un intérprete de comandos y nos situaremos en el directorio que
contiene la cartografía para las prácticas de postGIS (data/Cartografia/postgis), aquí ejecutamos 7:
shp2pgsql -I -s 23030 limites.shp limites | psql -U postgres -h localhost -d postgis
Práctica libre: ver las distintas opciones que tiene el comando shp2pgsql.
Práctica libre: redireccionar la salida del comando shp2pgsql a un fichero sql para ver su
contenido.
Cargar una capa PostGIS en gvSIG
Para comprobar que todo el proceso ha sido correcto, cargaremos la tabla recién cargada en gvSIG
(Vista -> Añadir Capa -> GeoBD). Por ello no insertaremos la capa en la vista tras su carga. De
nuevo, ponemos los parámetros de conexión.
7 Si no exite el comando es porque no está definida correctamente la variable de entorno PATH.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
13
Illustration 12: Conexión GeoBD gvSIG+
Finalmente seleccionamos la tabla que acabamos de crear:
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
14
Conceptos avanzados en PostgresSQL
Aunque realmente no se tratan de conceptos “muy avanzados”, queremos dividir esta parte en la
que se profundiza sobre la base de datos PostgresSQL.
La mayoría de los ejercicios los haremos con la herramienta de administración pgAdmin III.
Esquemas
Hasta ahora hemos visto el concepto de instancia de base de datos y plantilla, además de éstos,
veremos que una base de datos puede tener varios esquemas que son los que realmente contienen
las tablas, procedimientos, etc ... Por defecto, cuando creas una base de datos se crea el esquema
“public”.
Algunas de las características de los esquemas son los siguientes:

Objetos de diferentes esquemas puede “verse” siempre que estén en la misma instancia de
base de datos. Objetos de distintas bases de datos no tienen acceso.

Para acceder a un objeto de un esquema, en SQL se especifica como
nombre_esquema.nombre_objeto. Si no especificas el esquema en una consulta, postgreSQL
utilizará cualquiera de los esquemas especificados en la variable de usuario search_path.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
15
Para crear un esquema con pgAdminIII basta con pulsar el botón derecho sobre los esquema y
seleccionar “Nuevo Esquema”.
Práctica libre: modificar la variable “search_path” del usuario postgres de forma que acceda por
defecto a un esquema distinto al “public”. Comprobar que al cargar un shapefile con el comando
“shp2pgsql” la tabla se guarda en el nuevo esquema. Volver a ejecutar el comando indicando en
esta ocasión la ruta completa a la tabla con la sintaxis “esquema.tabla”, guardar la tabla en el
Illustration 13: Creación de esquemas con pgAdminIII
esquema “public”.
Roles y privilegios
Hasta el momento hemos empleado el DBA (postgres) como usuario para realizar todas las
operaciones en la bases de datos. Sin embargo, esto no es recomendable ya que este usuario tiene
privilegios totales sobre cualquier objeto de la base de datos. En una organización, lo más habitual
es que exista un conjunto de usuarios que tiene ciertos privilegios sobre ciertos objetos de la base de
datos. En esta parte de la práctica veremos cómo añadir roles (usuarios o grupos de usuarios) y
otorgar privilegios a dichos roles.
En postgreSQL encontraremos dos tipos de roles, el rol de login y el rol de grupo. El primero
identifica a un usuario y el segundo a un grupo. La única diferencia significativa entre ambos es que
el primero puede conectarse a la base de datos mientras que el segundo no puede. El segundo tipo
de rol, el de grupo, lo emplearemos para agrupar los roles de login que comparten los mismos
privilegios.
En cuanto a los privilegios, se pueden diferenciar entre los privilegios en el esquema y los
privilegios en los objetos del esquema (tablas, funciones, ...). Los privilegios en un esquema son de
uso (puedo acceder al esquema) y creación (puedo crear objetos en el esquema), mientras que los
privilegios en los objetos son los típicos SELECT, UPDATE, INSERT, etc.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
16
Para familiarizarnos con estos conceptos emplearemos la base de datos creada anteriormente,
“postgis”, donde crearemos dos roles de grupo, el primero llamado “editores” que tendrá privilegios
para crear y modificar objetos en el esquema “public” y el segundo, llamado “consultores” que sólo
tendrá permisos de consulta. Una vez creado estos grupos, añadiremos un usuario “consultorX” al
grupo “consultores” y un usuario (rol de login) llamado “editorX” al grupo de “editores”. La idea es
que los usuarios de un grupo puedan leer la cartografía y los usuarios de otro grupo puedan
modificarla.
Creación de roles de grupo.
En primer lugar nos conectamos a la base de datos del servidor localhost con el DBA que es el
único rol que existe por ahora. Tras conectarnos, pulsaremos con el botón derecho de nuestro ratón
en “Roles de grupo” y seleccionamos “Nuevo Rol de Grupo”. Aparecerá un diálogo como el de la
figura en el que pondremos los datos de los nuevos roles de grupo8.
Creación de roles de login
Illustration 14: Creación de un grupo en
Una vez creados los grupos, crearemos
postgreSQLlos roles de login “editorX” y “consultorX” que
pertenecerán al grupo “editores” y consultores respectivamente. Para crear los roles de login, pulsar
con el botón derecho en “Roles de Login -> Crear Nuevo Rol de Login”. Aparecerá un cuadro de
diálogo igual al que utilizábamos para crear los roles de grupo, aquí es importante destacar que se
debe marcar la casilla Hereda permisos de los roles padre, con ésto todos los roles que pertenezcan
a este tendrán los mismos privilegios. Tras indicar el nombre del rol nos situaremos en la pestaña
“Membresía del Role” e indicaremos que el usuario es miembro del grupo correspondiente.
8 Los checkbox que aparecen son privilegios especiales del rol que no son heredados por los miembros de ese rol.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
17
Illustration 15: Rol de grupo al que pertenece un usuario
Asignar privilegios en un esquema.
Una vez creados todos los roles, el siguiente paso será asignar a los roles de grupo los privilegios en
el esquema y en los objetos del esquema. Para dar permisos en el esquema, pulsaremos con el botón
derecho en el esquema y seleccionamos “Propiedades -> Privilegios”, allí añadimos el grupo
correspondiente e indicamos el tipo de privilegios. En nuestro ejemplo añadiremos el grupo
“editores” con todos los privilegios y el grupo “consultores” con privilegios de uso.
Illustration 16: Privilegios en un esquema
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
18
Asignar privilegios en un objeto de un esquema
Tras asignar los privilegios en el esquema, debemos hacer los mismo para cada objeto concreto del
esquema, para ello seleccionamos con el botón derecho del ratón el “Asistente para permisos” en el
esquema. En él seleccionamos todos los elementos que nos interesan, en nuestro caso la tabla
carreteras y la tabla geometry_columns9, después pasamos a la pestaña de privilegios donde
elegimos todos los privilegios “All” para el grupo “editores” y “Select” para el grupo “consultores”.
Además, para el grupo de editores deberemos dar todos los permisos en la secuencia de la tabla
(carreteras) ya que para crear nuevos elementos en la tabla se necesita el uso de dicha secuencia.
Illustration 17: Privilegios en un
esquema
9 Es importante dar permisos en esta tabla ya que las aplicaciones cliente, como gvSIG, consultan esta tabla para
averiguar datos referentes a las tablas que contienen cartografía.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
19
Comprobación de los privilegios con gvSIG
18: Miembros de
un grupo
Primero comprobaremos como Illustration
el rol “consultorX”
sólo
es capaz de visualizar una tabla (privilegio
SELECT), para ello cargaremos la tabla “carreteras” empleando una conexión con el usuario
“consultorX”. Deberá de visualizarse sin problemas. Tras ello seleccionaremos la capa y la
pondremos en edición para añadir una carretera, gvSIG indicará un error que confirma nuestras
sospechas, “consultorX” no tiene privilegios para modificar el objeto “carreteras”.
La segunda comprobación será poner en edición la capa “carreteras” para añadir un elemento pero
en este caso con el usuario “editorX”. No deberá dar problemas al terminar la edición.
Copias de seguridad
Configuración de parámetros (postgresql.conf)
En este fichero podemos modificar los parámetros generales de configuración de la instalación de
postgreSQL como el puerto de escucha, la opción de autovacuum, etc ...
Para modificarlo podemos utilizar pgAdmin3, “Archivo -> Abrir postgresql.conf10”.
10 En linux este fichero está en el direcotorio /etc/postgres y tiene permisos para el usuario y grupo “postgres”. Para
modificarlo desde pgAdminIII deberás tener permisos de escritura.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
20
Illustration 19: Fichero postgresql.conf
Práctica libre: probar a modificar algún parámetro. Por ejemplo, se puede cambiar el puerto al
6432 y probar a conectarnos con gvSIG.
Seguridad a nivel de red (pg_hba.conf)
PostgreSQL dispone de un mecanismo para restringir el acceso por la red (a través del puerto 5432).
Para modificar dicho acceso tenemos el fichero pg_hba.conf que podemos manipular con
pgAdminIII, “Abrir -> Fichero pg_hba.conf”.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
21
Illustration 20: Fichero pg_hba.conf
Copias de seguridad
Aunque existen varios métodos para hacer copias de seguridad (física, lógica, física online, ...), en
este punto nos centraremos en las copias de seguridad lógicas de una instancia de base de datos con
pgAdminIII.
Para realizar la copia de seguridad nos situamos con el ratón encima de la base de datos, en nuestro
ejemplo “postgis”, y con el botón derecho seleccionamos “Resguardo”. Nos aparecerá un cuadro de
diálogo dónde seleccionaremos el fichero en el que se volcará los datos y otros parámetros.
Si todo el proceso ha sido correcto nos aparecerá un mensaje indicando algo como: “El proceso
retornó el código de salida 0”
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
22
Illustration 21: Copias de seguridad lógicas con pgAdminIII
En el segundo paso simularemos una caída de la base de datos y su posterior recuperación con la
copia de seguridad. Para ello, eliminaremos la base de datos postGIS, sobre la base de datos
pulsaremos el botón derecho del ratón “Borrar/Eliminar”. Una vez borrada la base de datos la
volveremos a crear con el mismo nombre, sobre ella pulsaremos el botón derecho del ratón y
elegiremos la opción “Restaurar”.
Illustration 22: Restaurar una base de datos con
pgAdminIII
Podemos comprobar el resultado volviendo a
cargar la tabla “carreteras” con gvSIG.Conceptos
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
23
avanzados en PostGIS
Una vez profundizado en los aspectos más básicos de postgreSQL nos centraremos en el módulo
postGIS.
Funciones openGIS
PostGIS implementa la especificación SFA que define un esquema SQL para el acceso a
información georreferenciada en una base de datos relacional. En esta especificación se definen una
serie de operaciones básicas sobre geometrías, cómo deben almacenarse en la base de datos, etc ...
Veamos mediante una serie de ejercicios algunas de estas funciones y las posibilidades del lenguaje
de acceso a bases de datos SQL11.
Emplearemos la base de datos “postgis” que hemos estado utilizando durante toda la práctica y el
programa para ejecutar sentencias SQL de pgadmin. Para poder ejecutar una sentencia SQL
seleccionamos con el ratón la instancia de base de datos deseada y elegimos en el menú
“Herramientas -> Herramientas para Consulta”
Illustration 23: Herramienta para consultas SQL
11 En los ejercicios emplearemos funciones estádar del lenguaje SQL, propias del estándar openGIS y funciones
específicas de PostGIS.
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
24
Ejercicios
1. ¿Cuántas carreteras hay?
select count(*) from carreteras
2. ¿Cuales son los distintos tipos de carreteras?
select distinct(cod_ent) from carreteras
3. ¿Qué tipo de geometrías representa las carreteras?
select distinct(geometryType(the_geom)) from carreteras
4. Ver en formato WKT la carretera con identificador único 10.
select asText(the_geom) from carreteras where gid=10
5. Ver en formato WKB la carretera con identificador único 10.
select asBinary(the_geom) from carreteras where gid=10
6. Obtener los identificadores únicos de las carreteras que están dentro del rectángulo formado
por las coordenadas minX=450000, minY=4050000, maxX=500000, maxY=4100000
select gid FROM carreteras WHERE
the_geom && setSRID('BOX3D(450000 4050000, 500000 4100000)'::box3d, 23030)
7. Añadir una nueva carretera que cruza en línea recta desde el punto 450000, 4050000 al
500000, 4100000 y es del tipo 'V10'
insert into carreteras(the_geom,cod_ent) values
(GeomFromText('MULTILINESTRING((450000 4050000, 500000 4100000))','23030'),'v10')
8. Obtener el centro de la carretera recién creada.
select astext(centroid(the_geom)) from carreteras where cod_ent='v10'
Añadir un sistema de coordenadas
SIAPAD: Sistema de Información Andino para la Prevención y Atención de Desastres
25