16.5. Lesson: 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 
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
Soluzione
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);
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)
Soluzione
delete from people;
alter table people add column city_id int not null references cities(id);
(acquisizione città 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)');
Se stai riscontrando il seguente messaggio di errore:
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".
significa che mentre si sperimenta la creazione di poligoni per la tabella delle città, devi averne cancellati alcuni e ricominciare da capo. Controlla le voci della tabella delle città e utilizza tutti gli id che sono esistenti.
16.5.5. Guarda lo schema
Adesso lo schema dovrebbe assomigliare a questo:
16.5.6. Try Yourself 
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))
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));
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.
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.
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.