Bases de Datos Espaciales · Sesión 1
Repaso: modelado de datos y SQL
Antes de guardar geometrías hay que dominar lo básico: cómo se organizan los datos en tablas, cómo se relacionan y cómo se consultan.
Hoy, en 3 horas
Plan de la sesión
| Hora | Tema | Dónde |
|---|---|---|
| 0:00 – 0:15 | El curso y el caso de estudio | Estas slides |
| 0:15 – 0:45 | Tablas, tipos de dato, llaves, NULL | Slides + pestaña Conceptos |
| 0:45 – 1:15 | Modelo entidad-relación y normalización | Slides + pestaña Modelo |
| 1:15 – 1:30 | Descanso | |
| 1:30 – 2:15 | SQL guiado | Consola SQL del HTML |
| 2:15 – 2:50 | Ejercicios con verificación | Pestaña Ejercicios |
| 2:50 – 3:00 | Autoevaluación y cierre | Pestaña Autoevaluación |
Bases de Datos Espaciales · UMNG · Sesión 1
El curso
Ocho sesiones, de las tablas al análisis espacial
Sesión 1
Repaso de modelado y SQL
Sesión 2
Arquitectura de una aplicación SIG
Sesiones 3 y 4
Crear y manipular datos espaciales
Sesiones 5 y 6
Análisis espacial
Sesión 7
Relaciones espaciales
Sesión 8
Evaluación
Tres entornos, siempre lado a lado
PostgreSQL + PostGIS · Oracle Spatial · Geodatabase de ArcGIS
Bases de Datos Espaciales · UMNG · Sesión 1
El caso de estudio
San Isidro de la Sabana, un municipio inventado
El mismo municipio se usa en todas las sesiones, en los HTML y en los scripts.
- 6 barrios, con estrato y población
- 8 colegios y 3 centros de salud
- 5 vías, un río y una quebrada
- 3 parques y 2 zonas de riesgo
Hoy se trabaja solo con sus datos descriptivos. Desde la sesión 3 cada objeto tendrá además su forma y su ubicación.
Bases de Datos Espaciales · UMNG · Sesión 1
Parte 1
Cómo se organizan los datos
Tablas, tipos de dato, llaves y el modelo que las conecta.
Conceptos
Base de datos y sistema gestor
Base de datos
Conjunto organizado de datos relacionados entre sí.
Toda la información del municipio, repartida en varias tablas que se conectan.
SGBD
Sistema Gestor de Bases de Datos: el programa que guarda, protege y consulta.
PostgreSQL, Oracle, SQL Server, MySQL.
Lo que da un SGBD
- Muchos usuarios a la vez
- Reglas que evitan datos malos
- Permisos por usuario
- Respaldo y recuperación
- Un lenguaje común: SQL
Bases de Datos Espaciales · UMNG · Sesión 1
Conceptos
Una tabla, por partes
Tabla colegios
| id | nombre | sector | fundado | id_barrio |
|---|---|---|---|---|
| 1 | IE Técnico Central | oficial | 1962 | 1 |
| 2 | Colegio Santa Lucía | oficial | 1978 | 4 |
| 9 | Colegio Nuevo Horizonte | privado | 2024 | NULL |
Fila (registro)
Un colegio concreto con todos sus datos. La fila resaltada es Santa Lucía.
Columna (atributo)
Una característica con un tipo fijo: nombre es texto, fundado es entero.
Tabla (entidad)
Un tipo de cosa del mundo: colegios, barrios, estudiantes.
Bases de Datos Espaciales · UMNG · Sesión 1
Conceptos
Cada columna tiene un tipo de dato
| Tipo | Para qué | PostgreSQL | Oracle |
|---|---|---|---|
| Entero | Conteos, códigos, años | integer, bigint | NUMBER(10) |
| Decimal | Áreas, dinero, medidas | numeric(10,2) | NUMBER(10,2) |
| Texto | Nombres, descripciones | varchar(60), text | VARCHAR2(60) |
| Fecha | Fechas y horas | date, timestamp | DATE, TIMESTAMP |
| Lógico | Sí o no | boolean | NUMBER(1) o CHAR(1) |
| Geometría | Forma y ubicación | geometry (PostGIS) | SDO_GEOMETRY |
La última fila es la razón de este curso: una base de datos espacial es una base normal con un tipo de dato más.
Bases de Datos Espaciales · UMNG · Sesión 1
Conceptos
Llave primaria y llave foránea
barrios
| id (PK) | nombre |
|---|---|
| 1 | Centro |
| 4 | Santa Lucía |
| 5 | Los Pinos |
colegios
| id (PK) | nombre | id_barrio (FK) |
|---|---|---|
| 1 | IE Técnico Central | 1 |
| 2 | Colegio Santa Lucía | 4 |
| 3 | Gimnasio Los Pinos | 5 |
Llave primaria (PK)
Identifica cada fila. No se repite y no puede estar vacía.
Llave foránea (FK)
Apunta a la PK de otra tabla. La base no deja poner un barrio que no existe.
Bases de Datos Espaciales · UMNG · Sesión 1
Conceptos
NULL no es cero ni texto vacío
NULL significa ausencia de valor: no se sabe, o no aplica.
El Colegio Nuevo Horizonte todavía no tiene barrio asignado: su id_barrio es NULL.
Cualquier comparación con NULL da "desconocido", nunca verdadero. Por eso se pregunta con IS NULL.
Correcto
SELECT nombre FROM colegios
WHERE id_barrio IS NULL;
No devuelve nada
SELECT nombre FROM colegios
WHERE id_barrio = NULL;
Bases de Datos Espaciales · UMNG · Sesión 1
Modelo entidad-relación
El modelo del municipio
BARRIOS
id PK
nombre
estrato
poblacion
area_ha
COLEGIOS
id PK
nombre
sector
fundado
id_barrio FK
DOCENTES
id PK
nombre
area
anios_experiencia
id_colegio FK
ESTUDIANTES
id PK
nombre, apellido
grado
id_colegio FK
id_barrio_residencia FK
1
N
está en
1
N
trabaja en
1 · estudia en · N
1
N
vive en
Cada relación 1 a N se guarda como una llave foránea en el lado N.
Bases de Datos Espaciales · UMNG · Sesión 1
Modelo entidad-relación
¿Uno o muchos? Las tres cardinalidades
1 : 1
Un conductor maneja una sola ruta, y cada ruta tiene un solo conductor.
FK en cualquiera de las dos tablas, marcada como única.
1 : N
Un barrio tiene muchos colegios; un colegio está en un barrio.
FK en el lado N: colegios.id_barrio. Es la más común.
N : M
Una ruta pasa por muchos paraderos; un paradero está en muchas rutas.
Tabla intermedia: rutas_paraderos(id_ruta, id_paradero).
Bases de Datos Espaciales · UMNG · Sesión 1
Modelo entidad-relación
Del enunciado al modelo, en cuatro pasos
1
Sustantivos
se vuelven entidades: colegio, barrio, docente, estudiante.
2
Características
se vuelven atributos: nombre, sector, año de fundación.
3
Verbos
se vuelven relaciones: está en, trabaja en. ¿Uno o muchos?
4
Tablas
Entidad = tabla con PK. 1:N = FK. N:M = tabla intermedia.
"Cada colegio está en un barrio y tiene docentes y estudiantes; cada estudiante vive en un barrio."
Bases de Datos Espaciales · UMNG · Sesión 1
Normalización
Cada dato se guarda una sola vez
Antes: una planilla
| colegio | barrio | estr. | docente |
|---|---|---|---|
| IE Técnico Central | Centro | 3 | Martha Rojas |
| IE Técnico Central | Centro | 3 | Jorge Peña |
| Jardín Rayitos | Centro | 3 | Diana Vargas |
| Jardín Rayitos | Centro | 3 | Liliana Herrera |
El estrato de Centro aparece cuatro veces. Si cambia, hay que corregir cuatro filas.
Después: tres tablas
barrios(id, nombre, estrato)
colegios(id, nombre, id_barrio)
docentes(id, nombre, id_colegio)
El estrato está en una sola celda. La planilla completa se recupera con un JOIN.
Bases de Datos Espaciales · UMNG · Sesión 1
A practicar · 10 minutos
Abran repaso-bases-de-datos.html
- Pestaña Conceptos: toquen cada parte de la tabla colegios.
- Pestaña Modelo entidad-relación: toquen cada relación y lean el JOIN.
- Pestaña Normalización: comparen las dos vistas.
Parte 2
SQL: hablarle a la base de datos
Consultar, filtrar, agrupar, unir tablas y modificar datos.
SQL
La anatomía de una consulta
SELECT nombre, poblacion
FROM barrios
WHERE estrato <= 3
ORDER BY poblacion DESC
LIMIT 3;
SELECT qué columnas mostrar
FROM de qué tabla
WHERE qué filas dejar
ORDER BY en qué orden
LIMIT cuántas filas
Resultado
Santa Lucía · 6900
La Estación · 5100
Centro · 4200
Bases de Datos Espaciales · UMNG · Sesión 1
SQL
Filtrar filas con WHERE
| Operador | Ejemplo | Se lee |
|---|---|---|
| = <> | sector = 'oficial' | igual, distinto |
| < > <= >= | fundado < 2000 | menor, mayor |
| AND OR NOT | estrato <= 3 AND poblacion > 4000 | combinar condiciones |
| IN | estrato IN (2, 3) | está en la lista |
| BETWEEN | grado BETWEEN 9 AND 11 | en el rango, incluidos |
| LIKE | apellido LIKE '%ez' | termina en "ez" |
| IS NULL | id_barrio IS NULL | no tiene valor |
El texto va entre comillas simples. En LIKE, el signo % reemplaza cualquier cantidad de letras.
Bases de Datos Espaciales · UMNG · Sesión 1
SQL
Resumir con agregados y GROUP BY
SELECT sector, COUNT(*) AS total
FROM colegios
GROUP BY sector;
| sector | total |
|---|---|
| oficial | 5 |
| privado | 4 |
Agregados
COUNT SUM AVG MIN MAX
WHERE o HAVING
WHERE filtra filas antes de agrupar. HAVING filtra grupos después: HAVING COUNT(*) >= 2.
Bases de Datos Espaciales · UMNG · Sesión 1
SQL
Unir tablas: JOIN y LEFT JOIN
SELECT c.nombre, b.nombre
FROM colegios c
JOIN barrios b
ON b.id = c.id_barrio;
8 filas. Solo los colegios que tienen pareja. Nuevo Horizonte desaparece.
SELECT c.nombre, b.nombre
FROM colegios c
LEFT JOIN barrios b
ON b.id = c.id_barrio;
9 filas. Conserva todos los colegios; el que no tiene barrio sale con NULL.
Las letras c y b son alias: nombres cortos para no repetir el nombre de la tabla. La condición del ON dice qué columna conecta con cuál.
Bases de Datos Espaciales · UMNG · Sesión 1
SQL
Crear, insertar, actualizar y borrar
CREATE TABLE
CREATE TABLE rutas (
id INTEGER PRIMARY KEY,
nombre VARCHAR(40) NOT NULL,
id_colegio INTEGER REFERENCES colegios(id));
INSERT
INSERT INTO barrios
(id, nombre, estrato, poblacion, area_ha)
VALUES (7, 'Villa del Sol', 3, 1500, 35);
UPDATE
UPDATE colegios SET id_barrio = 7
WHERE nombre = 'Colegio Nuevo Horizonte';
DELETE
DELETE FROM docentes
WHERE id_colegio IS NULL;
Un UPDATE o un DELETE sin WHERE afecta a todas las filas de la tabla.
Bases de Datos Espaciales · UMNG · Sesión 1
Ejercicios · pestaña Ejercicios del HTML
Escriban la consulta y pulsen Verificar
1. Barrios y estrato
2. Barrios populares más poblados
3. Colegios privados antiguos
4. Estudiantes por grado
5. Experiencia por área
6. Cada colegio con su barrio
7. Docentes sin colegio
8. Estudiantes por colegio, con vacíos
9. Colegios con muchos estudiantes
10. Estudiantes que viajan a otro barrio
11. Barrios más densos que el promedio
12. Modificar datos
En grupos
Tres ejercicios de modelado: rutas escolares, normalizar una planilla y por qué usar llaves foráneas.
Para llevarse
Tres ideas de hoy
Tablas y llaves
Cada tabla tiene su PK; las relaciones se guardan con FK.
Un dato, un lugar
Normalizar evita contradicciones; el JOIN vuelve a juntar.
SQL declarativo
SELECT, WHERE, GROUP BY y JOIN sirven igual para datos espaciales.
Próxima sesión: la arquitectura de una aplicación SIG y el lugar que ocupa la base de datos espacial.
Bases de Datos Espaciales · UMNG · Sesión 1
Flechas del teclado o deslizar el dedo para avanzar.