TUTORIAL POSTGRE
Contenido
1. INSTALACIÓN POSTGRESQL __________________________________________________________ 1
2. INSTALACIÓN QGIS __________________________________________________________________ 8
3. USO DE POSTGRESQL ______________________________________________________________ 10
3.1 Para crear una BD: __________________________________________________________ 11
3.2 Para crear tablas con campos _______________________________________________ 12
3.3 Para comenzar a llenar una tabla _____________________________________________ 16
3.4 Para ejecutar consultas: _____________________________________________________ 19
Códigos para desplegar tabla y consultar campo específico: ________________________ 19
Ejemplos de códigos para consultas _______________________________________________ 21
Para consultas más complejas: ___________________________________________________ 22
Para hacer algunas validaciones: __________________________________________________ 24
Modificar la tabla agregando campos de validación _________________________________ 26
Para importar archivos Excel en formato csv (delimitado por coma) __________________ 27
Importar CSV directamente y de manera simplificada _______________________________ 29
4. USO DE QGIS CON POSTGRE ________________________________________________________ 29
4.1 Crear nuevo Proyecto ____________________________________________________ 29
4.2 Crear conexión a PostgeSQL ______________________________________________ 30
4.3 Agregar datos a QGis _____________________________________________________ 34
4.4 Cargar objetos espacialmente como puntos _______________________________ 35
4.5 Para guardar archivos ____________________________________________________ 37
4.6 Para desplegar los nombres de los collares: ________________________________ 38
4.7 Para agregar otros achivos vectoriales como kmz o shape: __________________ 40
1. INSTALACIÓN POSTGRESQL
Acceder a:
[Link]
Click en:
Selecciona sistema operativo:
Seleccionar descargar instalador en el texto:
Seleccionar última versión:
Abrir y ejecutar:
Seguir las instrucciones de instalación:
Instalar todo:
Definir puerto por defecto:
Configuración por defecto:
Listo para instalar:
Finalizar y permitir que Stack Builder descargue herramientas adicionales:
Seleccionar versión de PostgreSQL instalada:
Instalar todos los drivers y el servidor Database Server de la versión instalada:
Continuar la instalación:
Instalar cada driver, se abre una ventana como la siguiente en cada caso:
Continuar hasta instalar todos los seleccionados previamente y finalizar:
Una vez instalado, ir a las aplicaciones y abrir PgAdmin4:
Hacer click en Servers y te pedirá la contraseña creada antes:
2. INSTALACIÓN QGIS
[Link]
Descargar:
Saltar esto:
Descargar versión estable (LTR) o seleccionar Otras Plataformas para otro sistema
operativo:
Ejecutar:
Instalar:
Next y aceptar las condiciones:
Seleccionar carpeta de destino, next e instalar:
Una vez instalada Finalizar.
Para abrir, ir a las aplicaciones y seleccionar QGis:
3. USO DE POSTGRESQL
Una vez conectado, crear un nuevo servidor, haciendo click derecho:
Sobre el servidor nuevo, crear nueva BD si queremos agregar campo por
campo y datos:
Recomiendo saltar ese paso e iniciar la herramienta de consulta que permite
automatizar el proceso:
Comenzar a escribir código
3.1 Para crear una BD:
** Cualquier texto que comience con -- permitirá escribir información sin que
afecte en la ejecución del código**
** El comando CREATE DATABASE permitirá generar una base de datos, a
continuación, se escribe el nombre que va a tener esta BD:
CREATE DATABASE GeoBasDat;
**Si seleccionas el código y apretas Execute Script, se creará la nueva BD**
**Se puede consultar en la raíz al actualizar sobre el servidor o sobre
Databases**
3.2 Para crear tablas con campos
**Abrir una nueva herramienta de consulta desde la BD creada**
**En la nueva ventana de consulta, ejecutar el siguiente código**
CREATE TABLE Collar (
id_Collar SERIAL PRIMARY KEY,
CoordX DECIMAL(10, 2) NOT NULL,
CoordY DECIMAL(10, 2) NOT NULL,
CoordZ DECIMAL(10, 2) NOT NULL,
Prof DECIMAL(10, 2) NOT NULL
);
**El comando CREATE TABLE, permitirá crear una nueva tabla seguida de su
nombre y en paréntesis separados por coma irá cada campo (campo1,
campo2) terminando el código con punto y coma.**
** cada campo tiene el nombre seguido del tipo de dato, ejemplo: DECIMAL
indica número decimal seguido de (10,2) que significa longitud 10 y 2
decimales, SERIAL indica número de serie, VARCHAR (50) indica cadena de
texto de longitud 50, INT indica número entero.**
**A continuación le sigue PRIMARY KEY si es clave, o NOT NULL si es no
anulable o NULL si es anulable.**
**Al seleccionar y ejecutar el código se crea la tabla.**
Revisar en la BD en Schemas/public/Tables:
**En la misma ventana, ejecutar el siguiente código, a continuación del otro**
CREATE TABLE Survey (
id_Collar INT REFERENCES Collar(id_Collar),
id_Zona SERIAL PRIMARY KEY,
Desde DECIMAL(10, 2) NOT NULL,
Hasta DECIMAL(10, 2) NOT NULL,
Acimut INT NOT NULL,
Dip INT NOT NULL
);
** A diferencia del comando anterior, después de definir id_Collar (que aparece
en la primera tabla) se indica INT (debido a que es un número de serie) y REFERENCES
(para indicar que hace referencia a la primera tabla)**
** El primer id es una clave foránea, porque ya existe en la primera tabla y se
pide la referencia**
.**Si no aparece en la raíz, puede apretar Refresh**
**La clave foránea se puede chequear en las propiedades de la tabla survey
(click derecho sobre ella)**
**Finalmente para crear tabla para los datos geotécnicos**
CREATE TABLE GeoData (
id_Geo SERIAL PRIMARY KEY,
id_Collar INT REFERENCES Collar(id_Collar),
Desde DECIMAL(10,2) NOT NULL,
Hasta DECIMAL(10,2) NOT NULL,
Litologia VARCHAR(50) NOT NULL,
RQD DECIMAL(5,2),
Fracturas INT
);
3.3 Para comenzar a llenar una tabla
Para llenar las tablas antes creadas, podemos ingresar el siguiente código:
INSERT INTO Collar (id_Collar, CoordX, CoordY, CoordZ, Prof) VALUES
(1, 665321, 5963254.23, 1685, 230),
(2, 664523, 5963302, 1587, 330),
(3, 664896, 5963365.3, 1658, 240),
(4, 665236, 5965625.53, 1375, 295),
(5, 664523, 596324.73, 1467, 330),
(6, 664598, 5963272.3, 1478, 230),
(7, 664632, 5963241.2, 1445, 267),
(8, 664789, 5963251.3, 1665, 294),
(9, 665236, 5963391, 1678, 313),
(10, 665256, 5963891, 1467, 300);
**Seleccionar y ejecutar**
INSERT INTO Survey (id_Collar, id_Zona, Desde, Hasta, Acimut, Dip) VALUES
(1, 2, 0, 230, 285, 70),
(2, 3, 0, 330, 187, 80),
(3, 5, 0, 240, 78, 70),
(4, 7, 0, 295, 90, 75),
(5, 6, 0, 330, 120, 50),
(6, 1, 0, 230, 189, 60),
(7, 4, 0, 267, 144, 67),
(8,9, 0, 294, 166, 84),
(9, 10, 0, 313, 178, 73),
(10, 8, 0, 300, 257, 70);
**Seleccionar y ejecutar también para el contenido de la tabla survey**
INSERT INTO GeoData (id_Collar, Desde, Hasta, Litologia, RQD, Fracturas) VALUES
-- Sondaje 1
(1, 0, 100, 'Granodiorita', 75.5, 12),
(1, 100, 230, 'Diorita', 62.0, 18),
-- Sondaje 2
(2, 0, 150, 'Andesita', 80.3, 10),
(2, 150, 330, 'Basalto', 68.5, 16),
-- Sondaje 3
(3, 0, 120, 'Granodiorita', 71.2, 14),
(3, 120, 240, 'Toba', 55.0, 22),
-- Sondaje 4
(4, 0, 160, 'Toba', 60.0, 25),
(4, 160, 295, 'Diorita', 72.8, 13),
-- Sondaje 5
(5, 0, 180, 'Granodiorita', 69.3, 15),
(5, 180, 330, 'Andesita', 74.1, 12),
-- Sondaje 6
(6, 0, 100, 'Basalto', 61.0, 20),
(6, 100, 230, 'Toba', 59.4, 23),
-- Sondaje 7
(7, 0, 140, 'Diorita', 77.0, 11),
(7, 140, 267, 'Toba', 66.5, 17),
-- Sondaje 8
(8, 0, 170, 'Andesita', 64.2, 19),
(8, 170, 294, 'Granodiorita', 79.0, 9),
-- Sondaje 9
(9, 0, 180, 'Granodiorita', 82.0, 8),
(9, 180, 313, 'Toba', 70.7, 14),
-- Sondaje 10
(10, 0, 200, 'Basalto', 60.5, 21),
(10, 200, 300, 'Andesita', 73.4, 12);
**Seleccionar y ejecutar también para el contenido de la tabla GeoData**
3.4 Para ejecutar consultas:
Abrir nueva consulta en el Query Tool
Códigos para desplegar tabla y consultar campo específico:
SELECT * FROM Collar
**Aquí ya podemos revisar que la table tiene el contenido que agregamos**
**La siguiente consulta sirve para revisar solo la columna id_Collar**
SELECT id_Collar FROM Collar
**La siguiente consulta sirve para revisar las columnas CoordX y CoordY **
SELECT CoordX, CoordY FROM Collar
Ejemplos de códigos para consultas
**Para preguntar el mínimo de una columna (Puede ser máximo MAX, suma SUM,
pomedio AVG)**
**Para consultas con condiciones del promedio de datos menores a 250**
** Se puede usar >, <, =,<=,>=, pero para diferente se usa =! o <>**
** Se puede cambiar el nombre del campo**
**Otras condiciones son: IN, NOT IN, IS NULL, IS NOT NULL, LIKE, IS NOT LIKE (en
todos, para cadenas de texto se utiliza entre ‘’ , para numéricos entre () y separado por
comas cuando es más de un valor), BETWEEN (no necesita paréntesis para números)
y algunos agregados: AND, OR, NOT (esta última se usa solamente después de
AND)**
Para consultas más complejas:
**promedio RQD por lito**
**Ordenar de mayor a menor los sondajes en cantidad de fracturas**
**Se usa GROUP BY para agrupar datos por un campo en específico y ORDER BY para
ordenarlas de acuerdo a un criterio, en este caso DESC es descendente y ASC
ascendente**
**Contar por cantidad de intervalos de cada litología en la totalidad de sondajes**
** Se usa COUNT para contar registros por un criterio, en este caso es agrupando por
litología y ordenando de mayor a menor. AS solo se utiliza para darle nombre al
campo que está entregando el dato**
Para hacer algunas validaciones:
**Rangos fuera de norma para RQD**
**Datos de N° de fracturas vacío o sin dato**
SELECT * FROM GeoData
WHERE Fracturas IS NULL OR Fracturas < 0;
**Detectar solapamiento de intervalos en cada sondaje**
**En este caso es necesario definir un JOIN que va a permitir ver dos filas distintas al
mismo tiempo para comparar sus valores**
**Para poder usar JOIN, es necesario definir alias, como a, b, c**
SELECT a.id_Collar, [Link] AS desde_a, [Link] AS hasta_a,
[Link] AS desde_b, [Link] AS hasta_b
FROM GeoData a
JOIN GeoData b ON a.id_Collar = b.id_Collar AND a.id_Geo < b.id_Geo
WHERE [Link] > [Link] AND [Link] < [Link];
**La explicación:
FROM GeoData a JOIN GeoData b ON:
Aquí la tabla GeoData aparece dos veces, con alias a y b.
Esto es un auto-join: una unión de la tabla consigo misma.
El propósito es poder comparar filas de GeoData con otras filas de la misma
tabla.
ON a.id_Collar = b.id_Collar AND a.id_Geo < b.id_Geo
La condición a.id_Collar = b.id_Collar indica que solo vamos a comparar
tramos que pertenecen al mismo sondaje.
La condición a.id_Geo < b.id_Geo evita que se compare la misma fila consigo
misma y además evita duplicados simétricos. Es decir:
o Si comparas el registro 5 con el 7, no necesitas comparar también el 7
con el 5.
Esto hace que cada pareja de filas se compare solo una vez, de forma
ordenada por el identificador id_Geo.
WHERE [Link] > [Link] AND [Link] < [Link]
Esta es la condición clave para detectar solapamientos entre intervalos.
Los intervalos que representamos son:
o Para a: desde [Link] hasta [Link].
o Para b: desde [Link] hasta [Link].
La condición significa que los intervalos se superponen si:
o El final de a es mayor que el inicio de b (es decir, [Link] > [Link])
o Y el inicio de a es menor que el final de b ([Link] < [Link])
SELECT a.id_Collar, [Link] AS desde_a, [Link] AS hasta_a, [Link] AS
desde_b, [Link] AS hasta_b
Se seleccionan los valores para mostrar claramente:
o El sondaje (id_Collar) donde ocurre el solapamiento.
o Los intervalos de ambos registros (a y b) para que puedas visualizar el
traslape.
No hay datos con traslape
** Ejemplo para validar profundidad**
SELECT g.*
FROM GeoData g
JOIN Collar c ON g.id_Collar = c.id_Collar
WHERE [Link] > [Link];
**Ejemplo para tipografía**
SELECT DISTINCT Litologia
FROM GeoData
WHERE Litologia NOT IN ('Granodiorita', 'Diorita', 'Andesita', 'Toba', 'Basalto');
Modificar la tabla agregando campos de validación
**Ejemplo para agregar un campo de validación booleana (True,False) para la
litología**
** Para adicionar la columna**
ALTER TABLE GeoData
ADD COLUMN lito_valida BOOLEAN;
**Para agregar los datos**
**CASE se usa para un caso en que se puedan dar dos condiciones, WHEN define la
condición, THEN es el resultado, ELSE el resultado para el caso contrario, e usa END
para finalizar**
UPDATE GeoData
SET lito_valida = CASE
WHEN Litologia IN ('Granodiorita', 'Diorita', 'Andesita', 'Toba', 'Basalto') THEN TRUE
ELSE FALSE
END;
** Al hacer la consulta arroja la columna adicional**
Para importar archivos Excel en formato csv (delimitado por coma)
**Crear las tablas **
-- Tabla Collar
CREATE TABLE Collar (
id_Collar SERIAL PRIMARY KEY,
CoordX DECIMAL(10, 2) NOT NULL,
CoordY DECIMAL(10, 2) NOT NULL,
CoordZ DECIMAL(10, 2) NOT NULL,
Prof DECIMAL(10, 2) NOT NULL
);
-- Tabla Survey
CREATE TABLE Survey (
id_Collar INT REFERENCES Collar(id_Collar),
id_Zona SERIAL PRIMARY KEY,
Desde DECIMAL(10, 2) NOT NULL,
Hasta DECIMAL(10, 2) NOT NULL,
Acimut INT NOT NULL,
Dip INT NOT NULL
);
-- Tabla GeoData
CREATE TABLE GeoData (
id_Geo SERIAL PRIMARY KEY,
id_Collar INT REFERENCES Collar(id_Collar),
Desde DECIMAL(10, 2) NOT NULL,
Hasta DECIMAL(10, 2) NOT NULL,
Litologia VARCHAR(50) NOT NULL,
RQD DECIMAL(5, 2),
Fracturas INT
);
** Comandos para importar CSV**
**Suposiciones:Los archivos CSV se llaman: [Link], [Link], [Link] y
están en la carpeta: /home/usuario/datos/, los CSV tienen encabezado (nombres de
columnas en primera fila), los campos están separados por coma**
**Importar tabla Collar**
COPY Collar (id_Collar, CoordX, CoordY, CoordZ, Prof)
FROM '/home/usuario/datos/[Link]'
DELIMITER ','
CSV HEADER;
**Importar tabla Survey**
COPY Survey (id_Collar, id_Zona, Desde, Hasta, Acimut, Dip)
FROM '/home/usuario/datos/[Link]'
DELIMITER ','
CSV HEADER;
**Importar tabla GeoData**
COPY GeoData (id_Geo, id_Collar, Desde, Hasta, Litologia, RQD, Fracturas)
FROM '/home/usuario/datos/[Link]'
DELIMITER ','
CSV HEADER;
Importar CSV directamente y de manera simplificada
**Nota importante: Se puede usar COPY directamente sin necesidad de los pasos
anteriores, pero PostgreSQL necesita permiso para leer archivos en el servidor.**
\copy Collar (id_Collar, CoordX, CoordY, CoordZ, Prof) FROM
'/home/usuario/datos/[Link]' DELIMITER ',' CSV HEADER;
4. USO DE QGIS CON POSTGRE
4.1 Crear nuevo Proyecto
Para crear un nuevo Proyecto, hacer click en hoja blanca o proyecto nuevo vacío
En el navegador, desplegar XYZ Tiles y doble click sobre uno de los archivos para
cargar el mapa base:
Ojo que yo tengo varios más
4.2 Crear conexión a PostgeSQL
Click en el ícono de postgre para crear una nueva conexión, si no aparece click en
administrador de fuentes de datos:
Seleccionar nueva conexión:
Luego introducen sus datos, nombre del servidor (vale), base de datos (geobasdat),
puerto y anfitrión, luego prueban conexión:
Les pide su autentificación: posgres (datos aparecen en las propiedades del servidor
en postgreSQL, excepto la contraseña) y contraseña creada en instalación:
Ojo que anoté mal el nombre en el cajoncito.
Debe aparecerles:
En el navegador aparece:
4.3 Agregar datos a QGis
En la ventana anterior hacen click en elementos sin geometría (no existen líneas ni
vectores aún), despliegan public y seleccionan collar, click en añadir:
Ahora les aparece en las capas, hacen click derecho sobre la capa collar y abren la
tabla de atributos:
Se les despliega la tabla con datos:
Click en el botón para que aparezca como tabla:
4.4 Cargar objetos espacialmente como puntos
Click en la caja de herramienta de procesos o click derecho sobre
barra de herramientas:
Se abre:
En buscar apretar, Crear capa de puntos a partir de tabla y doble click sobre ella.
Ingresar parámetros de entrada de la tabla, collar, CoordX, CoordY y si quieren
CoordZ:
Ejecutan. Ojo con el SRC, debería ser WGS84 Zona UTM 19S (32719). Mis
coordenadas fueron inventadas:
Se crea la capa temporal:
4.5 Para guardar archivos
Click derecho sobre capa creada y click en hacer permanente
Pueden guardar como KML o como archivo Shape de ESRI, deben agregar el nombre:
Les aparece la notificación
Guarden también el proyecto:
4.6 Para desplegar los nombres de los collares:
Click derecho sobre capa creada y luego click en propiedades
Click sobre etiquetas y en lista seleccionar etiquetas sencillas:
En valor seleccionar id_collar y pueden cambiar color, tamaño, aceptan:
Ahora se ven los puntos con sus etiquetas:
4.7 Para agregar otros achivos vectoriales como kmz o shape:
Seleccionan el administrador de fuentes de datos, luego seleccionan vectorial, click
en los tres puntos y buscan el archivo, en este caso kmz, abrir, luego añadir.
Se despliega un recuadro con añadir capas lo seleccionan y luego
cierran la siguiente ventana.
Deberían aparecer en las capas:
