Introducción a las Bases de Datos

Espaciales

POSTGRESQL + POSGIS

Ejercicio y Guía HowTo de PostgreSQL / PostGIS

ING. ALDO MARCELO ALGORRY

Ejercicio:

Crear una base de datos (BD), llamarla GEODB<tres últimos nros dni>

Instalar la Extension PostGIS en la BD

Crear una secuencia llamada public.minas_gid_seq que comience en 1 con incremento

de 1.

Crear un dominio personalizado de estado de las minas: “En

produccion”,”Abandonada”,”Detectada”.

Crear una tabla espacial llamada Minas con geometría tipo POINT y srid: 3857. Con

atributos: id de tipo autogenerado y clave primaria, Estado del tipo creado en el punto

anterior, Nombre varchar(255).

Importar los Shapefiles pedanias y departamentos, usando QGis, verificar que tengan

srid 3857

Crear un trigger de validación que verifique que el punto esta dentro de una pedanía.

En QGis armar un proyecto que levante las capas departamentos, pedanias y minas.

Habilitar la edición de la capa minas y probar de incorporar nuevas minas en la

provincia, completar los datos y verificar que se grabe correctamente.

Probar grabar una mina fuera de la provincia o en la Laguna Mar Chiquita. Comprobar

que la validación funciona.

Crear una vista espacial de los puntos de la mina, agregando el nombre de la pedanía y

del departamento al que pertenecen. Llamarla minas_vw.

Guía HowTo de PostgreSQL / PostGIS

Crear una Base de Datos

Abrir QUERY TOOL

Instalar la extensión POSTGIS a nivel de la Base de Datos

Usar el comando SQL:

CREATE EXTENSION POSTGIS

Crear sequencias

CREATE SEQUENCE public.departamentos_gid_seq

INCREMENT 1

START 26

MINVALUE 1

MAXVALUE 2147483647

CACHE 1;

Crear dominios y tipos de datos personalizados:

Un listado de valores admitidos, dos formas:

a)

CREATE DOMAIN color VARCHAR(10)

CHECK (VALUE IN ('red', 'green', 'blue'));

b)

CREATE TYPE color2 AS ENUM ('red', 'green', 'blue');

Controlando alguna restricción:

Expresión regular:

CREATE DOMAIN us_postal_code AS TEXT

CHECK(

VALUE ~ '^\d{5}$'

OR VALUE ~ '^\d{5}-\d{4}$'

);

Crear una Tabla Espacial

Para los campos NO espaciales se puede usar la GUI de pgAdmin 4

Usando DDL SQL:

CREATE TABLE public.departamentos

(

gid integer NOT NULL DEFAULT nextval('departamentos_gid_seq'::regclass),

name character varying(254),

origen character varying(254),

url character varying(254) ,

geom geometry(MultiPolygon,3857),

CONSTRAINT departamentos_pkey PRIMARY KEY (gid)

)

Para agregar un campo de geometría usar DDL SQL:

ALTER TABLE public.departamentos

ADD COLUMN geom geometry(MultiPolygon);

Usando QGis:

Importar un Shapefile

Vía comandos (Linux):

shp2pgsql -s 4326 -I C:\ruta\ocean.shp public.ocean > ocean.sql

psql -d basededatos -h localhost -U postgres -p54321 -f ocean.sql

Usando shp2pgsql-gui.exe

Usando QGis

Crear trigger

CREATE FUNCTION public.checkparaje() RETURNS trigger AS $checkparaje$

DECLARE

cantped int;

BEGIN

SELECT count(*) INTO STRICT cantped

FROM public.pedanias WHERE st_contains(public.pedanias.geom,NEW.geom);

IF cantped =0 THEN

RAISE EXCEPTION 'El paraje no se encuentra en una pedania';

END IF;

RETURN NEW;

END;

$checkparaje$ LANGUAGE plpgsql;

CREATE TRIGGER checkparaje BEFORE INSERT OR UPDATE ON public.parajes

FOR EACH ROW EXECUTE PROCEDURE checkparaje();

Crear vista con junta especial

CREATE OR REPLACE VIEW public.centrosurbanosvw AS

SELECT centrosurbanos.id,

centrosurbanos.nombre,

centrosurbanos.geom,

pedanias.nombre AS nombre_pedania

FROM centrosurbanos,

pedanias

WHERE st_within(centrosurbanos.geom, pedanias.geom);