Base de datos Countries (PostGIS): tablas espaciales y ejercicios de SQL

Countries es una base PostGIS para aprender SQL espacial: países y capitales del mundo, además de capas de Nueva York con manzanas censales, barrios, calles y estaciones de metro. En SQLtest.online puedes consultarla directamente en el navegador: resolver ejercicios con corrección automática y ejecutar tus propias consultas en el playground, sin instalar nada.

• 7 tablas espaciales
• 246 países
• 491 estaciones de metro
• 11 ejercicios de SQL

Qué es Countries

PostGIS es la extensión de PostgreSQL que añade tipos geométricos y cientos de funciones espaciales: distancias, áreas, intersecciones, transformaciones de coordenadas. Esta base permite probarlas con datos conocidos.

Las tablas de Nueva York proceden del conocido taller «Introduction to PostGIS», y las tablas del mundo contienen las fronteras de los países y las capitales. Juntas cubren puntos, líneas y polígonos en dos sistemas de coordenadas.

Qué contiene la base

Las tablas se agrupan en dos bloques.

Lo principal: las tablas del mundo guardan grados y las de Nueva York, metros. En las capas de Nueva York las distancias y áreas salen directamente en metros; en las tablas del mundo, convierte primero a geography o transforma la geometría.

Cuántos datos hay en las tablas:

Estructura de las tablas

Haz clic en una tabla para ver sus columnas, una fila de ejemplo y sus claves.

Consultas de ejemplo

Estas consultas muestran cómo se relacionan los datos. Copia cualquiera y ejecútala en el playground.

Una capital dentro de su país: coordenadas del punto con ST_X / ST_Y y comprobación espacial con ST_Contains.

SELECT c.name AS capital, co.name AS country,
       round(ST_Y(c.location)::numeric, 2) AS lat,
       round(ST_X(c.location)::numeric, 2) AS lon,
       ST_Contains(co.border, c.location) AS inside_border
FROM capitals c
JOIN countries co ON co.id = c.country_id
ORDER BY c.name
LIMIT 3;

Estaciones de metro y su SRID: las capas de Nueva York usan la proyección 26918.

SELECT s.name AS station, s.borough, s.routes, ST_SRID(s.geom) AS srid
FROM nyc_subway_stations s
ORDER BY s.gid
LIMIT 3;

Ejercicios de SQL por tema

Esta base tiene 11 ejercicios de PostGIS: distancias, áreas, longitudes, conversiones a texto y JSON y uniones espaciales. Las soluciones se comprueban automáticamente en un PostgreSQL real con PostGIS. El número de la derecha es la cantidad de ejercicios del tema; los puntos de color muestran el rango de dificultad.

• Datos geoespaciales (PostGIS) 11

Por dónde empezar

Los primeros ejercicios de la base Countries:

1. Extraer Geometría como Texto
2. Extraer Geometría como JSON
3. Distancia entre ciudades
4. Área del País
5. Estaciones de Subte de Manhattan
6. Área del Barrio
7. Área del Barrio
8. Promedio de Área por Barrio
9. Longitud de las Calles de Nueva York
10. Estaciones de Little Italy

Todos los ejercicios de Countries →