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.

The goal of this lesson: To better understand how to create spatial entities directly in PostgreSQL.

16.5.1. Creando linestrings

Going back to our address database, let’s get our streets table matching the others; i.e., having a constraint on the geometry, an index and an entry in the geometry_columns table.

16.5.2. Ponte a prueba: ★★☆

Modify the streets table so that it has a geometry column of type 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".

then it means that while experimenting with creating polygons for the cities table, you must have deleted some of them and started over. Just check the entries in your cities table and use any id which exists.

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.

Respuesta

Añadir algunas personas en la ciudad “Tokyo Outer Wards”

INSERT INTO people (name, house_no, street_id, phone_no, city_id, geom)
   VALUES ('Bad Aboum',
           57,
           2,
           '073 712 31 21',
           2,
           'SRID=4326;POINT(22 18)');

INSERT INTO people (name, house_no, street_id, phone_no, city_id, geom)
   VALUES ('Pat Atra',
           59,
           2,
           '074 712 31 25',
           2,
           'SRID=4326;POINT(23 14)');

INSERT INTO people (name, house_no, street_id, phone_no, city_id, geom)
   VALUES ('Kat Herin',
           65,
           2,
           '074 722 31 28',
           2,
           'SRID=4326;POINT(29 18)');

Crear tabla myPolygonTable

CREATE TABLE myPolygonTable (
  id serial NOT NULL PRIMARY KEY,
  city_id int NOT NULL REFERENCES cities(id),
  geom geometry NOT NULL
);

ALTER TABLE myPolygonTable
  ADD CONSTRAINT myPolygonTable_geom_polygon_chk
  CHECK (st_geometrytype(geom) = 'ST_Polygon'::text );

Crear y cargar los cascos convexos

INSERT INTO myPolygonTable (city_id, geom)
  SELECT * FROM
  (
    SELECT
      ROW_NUMBER() over (order by city_id)::integer AS city_id,
      ST_CONVEXHULL(ST_COLLECT(geom)) AS geom
        FROM people
        GROUP BY city_id
  ) convexHulls;

16.5.7. Acceder a Sub-Objetos

With the SFS-Model functions, you have a wide variety of options to access sub-objects of SFS Geometries. When you want to select the first vertex point of every polygon geometry in the table myPolygonTable, you have to do this in this way:

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

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

select st_startpoint(myGeometry)
from (
    select st_boundary(geom) 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

From a given point table, you want to generate a linestring. The order of the points is defined by their id. Another ordering method could be a timestamp, such as the one you get when you capture waypoints with a GPS receiver.

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;

As the result, you will get all records from table_a which are not stored in 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.