17.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.
17.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.
17.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
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.
17.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.
17.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)
17.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.
17.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;
17.5.8. Data Processing¶
PostGIS supporta tutte le funzioni conformi agli standard OGC SFS/MM. Tutte queste funzioni iniziano ST_.
17.5.9. Clipping¶
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));
17.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.
17.5.11. Pulire la geometria¶
You can get more information for this topic in this blog entry.
17.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.
17.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
17.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.