GIS and Media fusion
"The explosive growth of the GeoWeb and geographic information has made GIS powerful media for the general public to communicate, but perhaps more importantly, GIS have also become media for constructive dialogs and interactions about social issues." - Sui & Goodchild
User Tools
geoinf14:postgis1
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
geoinf14:postgis1 [2014/12/01 15:27] oertz [Spatial Query Language :: Part1] |
geoinf14:postgis1 [2018/05/16 10:05] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ====== Spatial Query Language :: Part1 ====== | ====== Spatial Query Language :: Part1 ====== | ||
| - | Création d'une //géotable// : | + | Créer une //géotable// : |
| <code> | <code> | ||
| CREATE TABLE test (id serial PRIMARY KEY, genre text); | CREATE TABLE test (id serial PRIMARY KEY, genre text); | ||
| - | SELECT AddGeometryColumn( 'test1', 'geom', -1, 'GEOMETRY', 2 ); | + | SELECT AddGeometryColumn( 'test', 'the_geom', -1, 'GEOMETRY', 2 ); |
| </code> | </code> | ||
| - | Alimentation avec quelques géométries //en dur// : | + | Alimenter la table avec quelques géométries //en dur// et pas vraiment "spatiales" (SRS = -1 !!): |
| <code> | <code> | ||
| INSERT INTO test VALUES ( 3, 'batiment1', ST_GeometryFromText( 'POLYGON((10 10,40 20,35 8,12 4,10 10))', -1 ) ); | INSERT INTO test VALUES ( 3, 'batiment1', ST_GeometryFromText( 'POLYGON((10 10,40 20,35 8,12 4,10 10))', -1 ) ); | ||
| Line 20: | Line 20: | ||
| INSERT INTO test VALUES ( 9, 'bordureRoute2', ST_GeometryFromText( 'LINESTRING(1 92,50 92)', -1 ) ); | INSERT INTO test VALUES ( 9, 'bordureRoute2', ST_GeometryFromText( 'LINESTRING(1 92,50 92)', -1 ) ); | ||
| </code> | </code> | ||
| - | ===== Premiers pas en GQL ===== | + | ===== Premiers pas ===== |
| (-> cf. documentation [[http://postgis.refractions.net/documentation/manual-1.5/reference.html|PostGIS Reference]]) | (-> cf. documentation [[http://postgis.refractions.net/documentation/manual-1.5/reference.html|PostGIS Reference]]) | ||
| - | * **1. Quelles sont les aires des objets ?** -> ST_Area2d | + | - Quelles sont les aires des objets ? -> ST_Area2d<code>cf. 2</code> |
| - | <code>voir n°2</code> | + | - Quel sont les types géométriques des objets ? -> Geometrytype<code> |
| - | * **2. Quel sont les types géométriques des objets ?** -> Geometrytype | + | SELECT *, ST_Area(the_geom) FROM test WHERE ST_Geometrytype(the_geom) = 'ST_Polygon' |
| - | <code>SELECT id, genre, ST_Area2d(geom) FROM test WHERE ST_Geometrytype(geom) = 'ST_Polygon';</code> | + | |
| - | * **3. Qui est dans le bâtiment 2 ?** -> ST_Distance, ST_Within | + | |
| - | <code> | + | |
| - | SELECT genre AS pietons_dans_batiment_2 FROM test | + | |
| - | WHERE | + | |
| - | genre LIKE 'pieton%' | + | |
| - | AND | + | |
| - | ST_Distance((SELECT geom FROM test WHERE genre LIKE 'batiment 2'),test.geom)=0 | + | |
| </code> | </code> | ||
| - | <code> | + | - Quel est l'objet géométrique le plus proche du pieton 2 ? -> ST_Distance<code> |
| - | SELECT genre AS pietons_dans_batiment_2 FROM test | + | SELECT genre, ST_Distance(the_geom, (SELECT the_geom FROM test WHERE id = 2)) as dist FROM test |
| - | WHERE | + | WHERE id <> 2 ORDER BY dist LIMIT 1 |
| - | genre like 'pieton%' | + | |
| - | AND | + | OU |
| - | ST_WithIn(test.geom,(SELECT geom FROM test WHERE genre LIKE 'batiment 2')); | + | |
| + | SELECT t1.* FROM test t1, test t2 WHERE t2.genre LIKE 'pieton2' | ||
| + | AND ST_Distance(t1.the_geom,t2.the_geom) = ( | ||
| + | SELECT min(ST_Distance(t1.the_geom, t2.the_geom)) FROM test t1, test t2 | ||
| + | WHERE t1.genre LIKE 'pieton2' AND t2.genre <> 'pieton2' | ||
| + | ) | ||
| </code> | </code> | ||
| - | * **4. Quel est l'objet géométrique le plus proche du pieton 2 ?** -> ST_Distance | + | - Qui est dans le bâtiment 2 ? -> ST_Within<code> |
| - | <code> | + | SELECT * FROM test WHERE genre like 'pieton%' |
| - | SELECT t1.genre FROM test t1, test t2 WHERE | + | AND ST_WithIn(test.the_geom, (SELECT the_geom FROM test WHERE genre LIKE 'batiment2')); |
| - | t2.genre LIKE 'pieton 2' | + | |
| - | AND | + | |
| - | ST_Distance(t1.geom,t2.geom) = | + | |
| - | ( | + | |
| - | SELECT min(ST_Distance(t1.geom, t2.geom)) FROM test t1, test t2 | + | |
| - | WHERE t1.genre LIKE 'pieton 2' AND t2.genre <> 'pieton 2' | + | |
| - | ) | + | |
| </code> | </code> | ||
| //(source: [[http://www.davidgis.fr|Jean David Techer]])// | //(source: [[http://www.davidgis.fr|Jean David Techer]])// | ||
geoinf14/postgis1.1417444069.txt.gz · Last modified: 2018/05/16 10:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
