16.5. Lesson: Construeren van geometrie

In dit gedeelte duiken we een beetje dieper in hoe eenvoudige geometrieën worden geconstrueerd in SQL. In de realiteit zou u waarschijnlijk een GIS zoals QGIS gebruiken om complexe geometrieën te maken met behulp van hun gereedschappen voor digitaliseren; echter, begrijpen hoe zij worden geformuleerd kan handig zijn bij het schrijven van query’s en begrijpen hoe de database is samengesteld.

Het doel van deze les: Beter begrijpen hoe ruimtelijke entiteiten direct in PostgreSQL/PostGIS te maken.

16.5.1. Lijnen maken

Terug naar onze database address, laten we zorgen dat onze tabel streets overeenkomt met de andere; d.i. een beperking hebben op de geometrie, een index en een item in de tabel geometry_columns.

16.5.2. Try Yourself moderate

  • Pas de tabel streets zo aan dat die een kolom voor geometrie bevat van het type ST_LineString.

  • Vergeet niet een update uit te voeren om de geometrie kolom te laten maken.

  • Voeg een constraint toe om te voorkomen dat andere geometrie dan lijnen of lege geometrie kan worden ingevoerd.

  • Maak een ruimtelijke index op de nieuwe geometrie kolom

Antwoord

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

Laten we nu een lijn in onze tabel streets maken. In dit geval zullen we een bestaand record voor een straat bijwerken:

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

Bekijk de resultaten in QGIS. (U moet misschien met rechts klikken op de laag streets in het paneel ‘Lagen’ en kiezen voor ‘Op kaartlaag inzoomen’.)

Maak nu nog enkele items voor streets - enkele in QGIS en enkele vanaf de opdrachtregel.

16.5.3. Polygonen maken

Het maken van polygonen is net zo eenvoudig. Eén ding om te onthouden is dat, bij definitie, polygonen tenminste vier hoekenpunten hebben, waarvan de eerste en laatste op één lijn liggen:

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

Notitie

Een polygoon vereist dat er dubbele haken rondom de lijst met coördinaten staat; dat is om het voor u mogelijk te maken complex polygonen met meerdere niet verbonden gebieden toe te voegen. Bijvoorbeeld

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

Indien u deze stap volgde kunt u controleren wat die deed door de gegevensset cities te laden in QGIS, de attributentabel ervan te openen en het nieuwe item te selecteren. merk op hoe de twee nieuwe polygonen zich gedragen als één polygoon.

16.5.4. Oefening: Steden aan mensen koppelen

Voor deze oefening zou u het volgende moeten doen:

  • Alle gegevens uit uw tabel people verwijderen.

  • Een kolom voor een vreemde sleutel, die verwijst naar de primaire sleutel van de tabel cities, aan people toevoegen.

  • QGIS gebruiken om enkele steden vast te leggen.

  • SQL gebruiken om enkele nieuwe records voor people in te voeren, er zorg voor dragend dat elk daarvan een geassocieerde straat en stad heeft.

Uw bijgewerkte schema voor people zou er ongeveer zo uit moeten zien:

\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     |
  the_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(the_geom) =
                       'ST_Point'::text OR the_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)

Antwoord

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

(steden vastleggen in QGIS)

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

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

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

Als u het volgende foutbericht ontvangt:

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".

dan betekent dat, dat tijdens het experimenteren met het maken van polygonen voor de tabel cities, u enkele ervan moet hebben verwijderd en opnieuw moet beginnen. Controleer eenvoudigweg de items in uw tabel cities en gebruik een id die bestaat.

16.5.5. Ons schema bekijken

Ons schema zou er nu ongeveer zo uit moeten zien:

../../../_images/final_schema.png

16.5.6. Try Yourself hard

Maak stadsgrenzen door de minimum convex hull van alle adressen voor die stad te berekenen en een buffer rondom dat gebied te berekenen.

16.5.7. Toegang tot sub-objecten

Met de functies voor het SFS-model heeft u een breed scala aan opties om toegang te verkrijgen tot sub-objecten van geometrieën van SFS. Wanneer u het eerste vertexpunt van elke geometrie polygoon in de tabel myPolygonTable wilt selecteren, moet u dat op de volgende manier doen:

  • Transformeer de randen van de polygoon naar een lijn:

    select st_boundary(geometry) from myPolygonTable;

  • Selecteer het eerste vertexpunt van de resulterende lijn:

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

16.5.8. Gegevens verwerken

PostGIS ondersteunt alle standaard conforme functies van OGC SFS/MM. Al deze functies beginnen met ST_.

16.5.9. Clippen

U kunt de functie ST_INTERSECT() gebruiken om een sub-gedeelte van uw gegevens te clippen. Vermijd lege geometrieën door te gebruiken:

where not st_isempty(st_intersection(a.the_geom, b.the_geom))
../../../_images/qgis_001.png 
select st_intersection(a.the_geom, b.the_geom), b.*
from clip as a, road_lines as b
where not st_isempty(st_intersection(st_setsrid(a.the_geom,32734),
  b.the_geom));
../../../_images/qgis_002.png

16.5.10. Geometrieën uit andere geometrieën bouwen

vanuit een opgegeven punttabel wilt u een lijn genereren. De volgorde van de punten wordt gedefinieerd door hun id. Een andere methode van sorteren zou een tijdstempel kunnen zijn, zoals die welke u krijgt wanneer u waypoints vastlegt met een GPS-ontvanger.

../../../_images/qgis_006.png

U kunt de volgende opdracht gebruiken om een lijn uit een nieuwe puntlaag, genaamd ‘points’, te maken:

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

U zou deze opdracht ook uit kunnen voeren op de laag ‘people’, hoewel dat natuurlijk weinig nut binnen de echte wereld zou hebben om dit te doen, om te zien hoe het werkt zonder een nieuwe laag te maken.

../../../_images/qgis_007.png

16.5.11. Geometrie opruimen

U kunt meer informatie over dit onderwerp krijgen in dit blog-item.

16.5.12. Verschillen tussen tabellen

U kunt het sleutelwoord voor PostgreSQL EXCEPT gebruiken om het verschil tussen twee tabellen met dezelfde structuur te detecteren:

select * from table_a
except
select * from table_b;

Als resultaat zult u alle records uit table_a verkrijgen die niet zijn opgeslagen in table_b.

16.5.13. Tabelruimten

U kunt definiëren waar Postgres zijn gegevens op schijf op zou moeten slaan dor tabelruimten te maken:

CREATE TABLESPACE homespace LOCATION '/home/pg';

U kunt u dan, wanneer u een database maakt, specificeren welke tabelruimte gebruikt moet worden bijv.:

createdb --tablespace=homespace t4a

16.5.14. In Conclusion

U heeft geleerd hoe u meer complexe geometrieën maakt met behulp van argumenten van PostGIS. Onthoud dat dit meer is om uw achtergrondkennis te verbeteren wanneer u werkt met voor geo ingeschakelde databases via een GIS als startpunt. Gewoonlijk hoeft u deze argumenten niet handmatig in te voeren, maar als u een algemeen idee heeft van hun structuur zal dat u helpen wanneer u een GIS gebruikt, in het bijzonder als u fouten tegenkomt die anders nogal cryptisch zouden kunnen lijken.