Importante

La traducción es un esfuerzo comunitario puede unirse. Esta página está actualmente traducida en |progreso de traducción|.

16.5. Lección: Construcción de geometría

En esta sección vamos a profundizar un poco más en cómo se construyen geometrías simples en SQL. En realidad, probablemente usará un SIG como QGIS para crear geometrías complejas usando sus herramientas de digitalización; sin embargo, comprender cómo se formulan puede ser útil para redactar consultas y comprender cómo se ensambla la base de datos.

** El objetivo de esta lección: ** Comprender mejor cómo crear entidades espaciales directamente en PostgreSQL / PostGIS.

16.5.1. Creando linestrings

Volviendo a nuestra base de datos address, hagamos que nuestra tabla de calles coincida con las demás; es decir, tener una restricción en la geometría, un índice y una entrada en la tabla geometry_columns.

16.5.2. Ponte a prueba: ★★☆

Modifica la tabla streets para que tenga una columna de geometría de tipo ST_LineString.
¡No olvide realizar la actualización adjunta a la tabla de columnas de geometría!
También agregue una restricción para evitar que se agreguen geometrías que no sean LINESTRINGS o nulas.
Cree un índice espacial en la nueva columna de geometría

Respuesta

alter table streets add column geom geometry;
alter table streets add constraint streets_geom_point_chk check
     (st_geometrytype(geom) = 'ST_LineString'::text OR geom IS NULL);
insert into geometry_columns values ('','public','streets','geom',2,4326,
     'LINESTRING');
create index streets_geo_idx
  on streets
  using gist
  (geom);

Ahora insertemos una cadena de líneas en nuestra tabla de calles. En este caso, actualizaremos un registro de calle existente:

update streets
set geom = 'SRID=4326;LINESTRING(20 -33, 21 -34, 24 -33)'
where streets.id=2;

Eche un vistazo a los resultados en QGIS. (Es posible que deba hacer clic con el botón derecho en la capa de calles en el panel “Capas” y elegir “Zoom a la extensión de la capa”).

Ahora cree más entradas de calles, algunas en QGIS y otras desde la línea de comandos.

16.5.3. Crear Polígonos

Crear polígonos es igual de fácil. Una cosa para recordar es que, por definición, los polígonos tienen al menos cuatro vértices, con el último y el primero coubicados:

insert into cities (name, geom)
values ('Tokyo', 'SRID=4326;POLYGON((10 -10, 5 -32, 30 -27, 10 -10))');

Nota

Un polígono requiere corchetes dobles alrededor de su lista de coordenadas; esto es para permitirle agregar polígonos complejos con múltiples áreas no conectadas. Por ejemplo

insert into cities (name, geom)
values ('Tokyo Outer Wards',
        'SRID=4326;POLYGON((20 10, 20 20, 35 20, 20 10),
                           (-10 -30, -5 0, -15 -15, -10 -30))'
        );

Si siguió este paso, puede verificar lo que hizo cargando el conjunto de datos de ciudades en QGIS, abriendo su tabla de atributos y seleccionando la nueva entrada. Observe cómo los dos nuevos polígonos se comportan como un solo polígono.

16.5.4. Ejercicio: Enlazando Ciudades a Gente

Para este ejercicio deberías hacer lo siguiente:

Borra todos los datos de tu tabla de gente.
Agregue una columna de clave externa a las personas que haga referencia a la clave principal de la tabla de ciudades.
Use QGIS para capturar algunas ciudades.
Usa SQL para insertar algunos nuevos registros de gente, asegurando que cada uno tenga asociadas una calle y una ciudad.

Tu esquema actualizado de gente debería parecerse algo a esto:

\d people

Table "public.people"
   Column   |         Type          |                      Modifiers
 -----------+-----------------------+--------------------------------------------
  id        | integer               | not null
            |                       | default nextval('people_id_seq'::regclass)
  name      | character varying(50) |
  house_no  | integer               | not null
  street_id | integer               | not null
  phone_no  | character varying     |
  geom      | geometry              |
  city_id   | integer               | not null
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(geom) =
                       'ST_Point'::text OR geom IS NULL)
Foreign-key constraints:
  "people_city_id_fkey" FOREIGN KEY (city_id) REFERENCES cities(id)
  "people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

Respuesta

delete from people;
alter table people add column city_id int not null references cities(id);

(capturar ciudades en QGIS)

insert into people (name,house_no, street_id, phone_no, city_id, geom)
   values ('Faulty Towers',
           34,
           3,
           '072 812 31 28',
           1,
           'SRID=4326;POINT(13 -15)');

insert into people (name,house_no, street_id, phone_no, city_id, geom)
   values ('IP Knightly',
           32,
           1,
           '071 812 31 28',
           1,
           'SRID=4326;POINT(18 -24)');

insert into people (name,house_no, street_id, phone_no, city_id, geom)
   values ('Rusty Bedsprings',
           39,
           1,
           '071 822 31 28',
           1,
           'SRID=4326;POINT(22 -25)');

Si recibe el siguiente mensaje de error:

ERROR:  insert or update on table "people" violates foreign key constraint
        "people_city_id_fkey"
DETAIL: Key (city_id)=(1) is not present in table "cities".

entonces significa que mientras experimentan con la creación de polígonos para la tabla de ciudades, debe haber eliminado algunos de ellos y comenzar de nuevo. Simplemente verifique las entradas en su tabla de ciudades y use cualquier id que exista.

16.5.5. Mirando tu esquema

Por ahora nuestro esquema debería parecerse a esto:

16.5.6. Póngase a prueba: ★★★

Cre los límites de ciudad calculando el mínimo casco convexo de todas las direcciones para esa ciudad y calculando un búfer alrededor de esa área.

16.5.7. Acceder a Sub-Objetos

Con las funciones del modelo SFS, tiene una amplia variedad de opciones para acceder a subobjetos de geometrías SFS. Cuando desee seleccionar el primer punto de vértice de cada geometría de polígono en la tabla myPolygonTable, debe hacer esto de esta manera:

Transforma el límite del polígono en una cadena de líneas:
```
select st_boundary(geometry) from myPolygonTable;
```

Seleccione el primer punto de vértice de la cadena de líneas resultante:

select st_startpoint(myGeometry)
from (
    select st_boundary(geometry) as myGeometry
    from myPolygonTable) as foo;

16.5.8. Procesamiento de datos

PostGIS admite todas las funciones de conformidad con el estándar OGC SFS / MM. Todas estas funciones comienzan con ST_.

16.5.9. Corte

Para recortar una subparte de sus datos, puede utilizar la función ST_INTERSECT(). Para evitar geometrías vacías, use:

where not st_isempty(st_intersection(a.geom, b.geom))

select st_intersection(a.geom, b.geom), b.*
from clip as a, road_lines as b
where not st_isempty(st_intersection(st_setsrid(a.geom,32734),
  b.geom));

16.5.10. Construcción de geometrías a partir de otras geometrías

A partir de una tabla de puntos determinada, desea generar una cadena de líneas. El orden de los puntos se define por su id. Otro método de pedido podría ser una marca de tiempo, como la que obtiene cuando captura puntos de referencia con un receptor GPS.

Para crear una cadena de líneas a partir de una nueva capa de puntos llamada “puntos”, puede ejecutar el siguiente comando:

select ST_LineFromMultiPoint(st_collect(geom)), 1 as id
from (
  select geom
  from points
  order by id
) as foo;

Para ver cómo funciona sin crear una nueva capa, también puede ejecutar este comando en la capa “personas”, aunque, por supuesto, tendría poco sentido en el mundo real hacer esto.

16.5.11. Limpieza de Geometría

Puede obtener más información sobre este tema en esta entrada de blog.

16.5.12. Diferencias entre tablas

Para detectar la diferencia entre dos tablas con la misma estructura, puede utilizar el keyword de PostgreSQL EXCEPT:

select * from table_a
except
select * from table_b;

Como resultado, obtendrá todos los registros de table_a que no están almacenados en table_b.

16.5.13. Espacios de Tabla

Puede definir dónde debe almacenar postgres sus datos en el disco creando espacios de tabla:

CREATE TABLESPACE homespace LOCATION '/home/pg';

Al crear una base de datos, puede especificar el tablespace que desea utilizar, por ejemplo

createdb --tablespace=homespace t4a

16.5.14. En conclusión

Ha aprendido a crear geometrías más complejas utilizando declaraciones PostGIS. Tenga en cuenta que esto es principalmente para mejorar su conocimiento tácito al trabajar con bases de datos habilitadas geográficamente a través de una interfaz GIS. Por lo general, no necesitará ingresar estas declaraciones manualmente, pero tener una idea general de su estructura lo ayudará cuando use un SIG, especialmente si encuentra errores que de otro modo parecerían crípticos.