16.5. Costruzione delle Geometrie

In questa sezione approfondirai come puoi costruire semplici geometrie con SQL. In realtà userai un GIS come QGIS per creare geometrie complesse con gli strumenti di digitalizzazione; tuttavia, capire come li puoi formulre può essere utile per scrivere interrogazioni e capire come è costruito il database.

L’obiettivo di questa lezione: Capire meglio come creare entità spaziali direttamente in PostgreSQL/PostGIS.

16.5.1. Crea linee

Torna al database address, fai in modo che la tabella delle strade corrisponda alle altre; cioè, avendo un vincolo sulla geometria, un indice e una voce nella tabella geometry_columns.

16.5.2. Try Yourself moderate

  • Modificare la tabella streets in modo che abbia una colonna geometry di tipo ST_LineString.

  • Don’t forget to do the accompanying update to the geometry columns table!

  • Also add a constraint to prevent any geometries being added that are not LINESTRINGS or null.

  • Create a spatial index on the new geometry column

Verifica i risultati

Inserisci una linea nella tabella streets. In questo caso aggiornerai un record di strada esistente:

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

Dai un’occhiata ai risultati in QGIS. (Potresti aver bisogno di fare clic-destro del mouse sul vettore streets nel pannello “Layers” e scegliere “Zoom sul layer”.)

Ora crea altre voci per le strade: alcune in QGIS e altre dalla riga di comando.

16.5.3. Crea poligoni

Creare poligoni è altrettanto facile. Una cosa da ricordare è che, per definizione, i poligoni hanno almeno quattro vertici, con l’ultimo e il primo essere coincidenti:

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

Nota

Un poligono richiede doppie parentesi attorno alla sua lista di coordinate; questo per consentire di aggiungere poligoni complessi con più aree non connesse. Per esempio

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

Se hai seguito questo passaggio, puoi verificare cosa ha fatto caricando l’insieme di dati della città in QGIS, aprendo la sua tabella degli attributi e selezionando la nuova voce. Nota come i due nuovi poligoni si comportano come un poligono.

16.5.4. Esercizio: Collega delle città alle persone

Per questo esercizio dovresti fare quanto segue:

  • Cancella tutti i dati dalla tabella people.

  • Add a foreign key column to people that references the primary key of the cities table.

  • Usa QGIS per inserire qualche città.

  • Usa SQL per inserire qualche nuovo record in people, assicurandoti che ognuno abbia una via e una città associata

Il tuo schema aggiornato dovrebbe assomigliare a qualcosa come questo:

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

Verifica i risultati

16.5.5. Guarda lo schema

Adesso lo schema dovrebbe assomigliare a questo:

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

16.5.6. Try Yourself hard

Crea limiti di città calcolando il minimopoligono convesso di tutti gli indirizzi per quella città e calcolando un buffer attorno a quell’area.

16.5.7. Accedi agli oggetti

Con le funzioni del modello SFS, hai una vasta gamma di opzioni per accedere agli oggetti di Geometrie SFS. Se vuoi selezionare il primo vertice di ogni poligono nella tabella myPolygonTable, devi fare in questo modo:

  • Trasforma il policono di confine in una linea:

    select st_boundary(geometry) from myPolygonTable;

  • Seleziona il primo vertice della linea risultante:

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

16.5.8. Elaborazione

PostGIS supporta tutte le funzioni conformi agli standard OGC SFS/MM. Tutte queste funzioni iniziano ST_.

16.5.9. Ritaglio

Per ritagliare una parte dei ati puoi usare la funzione ST_INTERSECT(). Per evitare geometrie vuote usa:

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. Costruisci geometrie da altre geometrie

Da una determinata tabella di punti, vuoi generare una linea. L’ordine dei punti è definito dal loro id. Un altro metodo per ordinare potrebbe essere una tabella oraria, come quello che si ottiene quando si acquisiscono punti con un ricevitore GPS.

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

Per creare una linea da un vettore punti chiamato “points”, puoi eseguire il seguente comando:

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

Per vedere come funziona senza creare un nuovo livello, è possibile eseguire questo comando anche sul livello “people”, anche se ovviamente sarebbe poco consueto.

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

16.5.11. Pulire la geometria

Puoi ottenere maggiori informazioni su questo argomento in this blog entry.

16.5.12. Differenze tra tabelle

Per rilevare la differenza tra due tabelle con la stessa struttura, è possibile utilizzare la parola PostgreSQL EXCEPT:

select * from table_a
except
select * from table_b;

Come risultato, otterrai tutti i record di table_a che non sono memorizzati in table_b.

16.5.13. Archiviazione delle tabelle

Puoi definire dove postgres deve memorizzare i propri dati su disco creando tablespaces:

CREATE TABLESPACE homespace LOCATION '/home/pg';

Se crei un database puoi allora specificare quale spazio di archiviazione usare ad esempio:

createdb --tablespace=homespace t4a

16.5.14. In Conclusion

Hai imparato come creare geometrie più complesse usando le istruzioni PostGIS. Tieni presente che questo è per migliorare la tua conoscenza quando lavori con database spaziali tramite un GIS. Di solito non è necessario inserire queste affermazioni manualmente, ma avere un’idea generale della loro struttura ti aiuterà quando utilizzi un GIS, specialmente se incontri errori che altrimenti sembrerebbero incomprensibili.