Viktigt

Översättning är en gemenskapsinsats du kan gå med i. Den här sidan är för närvarande översatt till 100.00%.

16.5. Lektion: Geometri Konstruktion

I det här avsnittet ska vi gå lite djupare in på hur enkla geometrier konstrueras i SQL. I verkligheten kommer du förmodligen att använda ett GIS som QGIS för att skapa komplexa geometrier med hjälp av deras digitaliseringsverktyg, men att förstå hur de formuleras kan vara praktiskt när du skriver frågor och förstår hur databasen är uppbyggd.

** Målet med denna lektion: ** För att bättre förstå hur man skapar spatiala enheter direkt i PostgreSQL / PostGIS.

16.5.1. Skapa linjestring

Vi går tillbaka till vår databas address och ser till att vår gatutabell stämmer överens med de andra, dvs. att den har en begränsning för geometrin, ett index och en post i tabellen geometry_columns.

16.5.2. Prova själv: ★★☆

Modifiera tabellen streets så att den har en geometrikolumn av typen ST_LineString.
Glöm inte att göra den åtföljande uppdateringen av tabellen med geometrikolumner!
Lägg också till en begränsning för att förhindra att geometrier läggs till som inte är LINESTRINGS eller null.
Skapa ett spatialt index på den nya geometrikolumnen

Svar

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);

Låt oss nu infoga en linjestring i vår gatutabell. I det här fallet kommer vi att uppdatera en befintlig gatupost:

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

Ta en titt på resultaten i QGIS (du kan behöva högerklicka på gatulagret i panelen ”Lager” och välja ”Zooma till lagrets omfattning”)

Skapa nu några fler gatuposter - några i QGIS och några från kommandoraden.

16.5.3. Skapa polygoner

Att skapa polygoner är lika enkelt. En sak att komma ihåg är att polygoner per definition har minst fyra hörnpunkter, där den sista och den första är samlokaliserade:

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

Observera

En polygon kräver dubbla parenteser runt sin koordinatlista; detta för att du ska kunna lägga till komplexa polygoner med flera områden som inte är sammankopplade. Exempelvis

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))'
        );

Om du följde det här steget kan du kontrollera vad det gjorde genom att ladda in stadsdatasetet i QGIS, öppna dess attributtabell och välja den nya posten. Notera hur de två nya polygonerna beter sig som en polygon.

16.5.4. Övning: Koppla samman städer med människor

För denna övning ska du göra följande:

Ta bort alla data från din personaltabell.
Lägg till en kolumn med främmande nyckel i people som refererar till primärnyckeln i tabellen cities.
Använd QGIS för att fånga några städer.
Använd SQL för att infoga några nya personposter och se till att varje post har en tillhörande gata och stad.

Ditt uppdaterade personschema bör se ut ungefär så här:

\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)

Svar

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

(fånga städer i 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)');

Om du får följande felmeddelande:

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

så betyder det att när du experimenterade med att skapa polygoner för stadstabellen, måste du ha tagit bort några av dem och börjat om. Kontrollera bara posterna i din stadstabell och använd alla id som finns.

16.5.5. En titt på vårt schema

Vid det här laget bör vårt schema se ut så här:

16.5.6. Prova själv: ★★★★

Skapa stadsgränser genom att beräkna det minsta konvexa skrovet av alla adresser för den staden och beräkna en buffert runt det området.

Svar

Lägg till några personer i staden ”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)');

Skapa tabellen 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 );

Skapa och ladda de konvexa skroven

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. Åtkomst till underobjekt

Med SFS-Model-funktionerna har du en mängd olika alternativ för att komma åt underobjekt till SFS-geometrier. Om du vill välja den första vertexpunkten för varje polygongeometri i tabellen myPolygonTable, måste du göra det på följande sätt:

Omvandla polygongränsen till en linjestring:

select st_boundary(geom) from myPolygonTable;

Välj den första toppunkten på den resulterande linjesträngen:

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

16.5.8. Databehandling

PostGIS stöder alla OGC SFS/MM standardkonforma funktioner. Alla dessa funktioner börjar med ST_.

16.5.9. Klippning

Om du vill klippa ut en del av dina data kan du använda funktionen ST_INTERSECT(). För att undvika tomma geometrier, använd:

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. Bygga geometrier från andra geometrier

Från en given punkttabell vill du generera en linjestring. Ordningen på punkterna definieras av deras id. En annan ordningsmetod kan vara en tidsstämpel, t.ex. den som du får när du registrerar waypoints med en GPS-mottagare.

Om du vill skapa en linjestring från ett nytt punktlager som heter ”points” kan du köra följande kommando:

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

För att se hur det fungerar utan att skapa ett nytt lager kan du också köra kommandot på lagret ”people”, även om det naturligtvis inte skulle vara särskilt meningsfullt att göra det i verkligheten.

16.5.11. Rengöring av geometri

Du kan få mer information om detta ämne i den här bloggposten.

16.5.12. Skillnader mellan tabeller

För att upptäcka skillnaden mellan två tabeller med samma struktur kan du använda PostgreSQL-nyckelordet EXCEPT:

select * from table_a
except
select * from table_b;

Resultatet blir att du får alla poster från tabell_a som inte finns lagrade i tabell_b.

16.5.13. Tablespaces

Du kan definiera var postgres ska lagra sina data på disken genom att skapa tablespaces:

CREATE TABLESPACE homespace LOCATION '/home/pg';

När du skapar en databas kan du sedan ange vilket tablespace som ska användas, t.ex:

createdb --tablespace=homespace t4a

16.5.14. Sammanfattningsvis

Du har lärt dig hur du skapar mer komplexa geometrier med hjälp av PostGIS-satser. Tänk på att detta främst är för att förbättra din tysta kunskap när du arbetar med geoaktiverade databaser via en GIS-frontend. Du behöver vanligtvis inte ange dessa satser manuellt, men att ha en allmän uppfattning om deras struktur kommer att hjälpa dig när du använder ett GIS, särskilt om du stöter på fel som annars verkar kryptiska.