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
ogo11:gql:exo1
Differences
This shows you the differences between two versions of the page.
|
ogo11:gql:exo1 [2014/05/15 14:04] oertz [GQL :: EXO1] |
ogo11:gql:exo1 [2018/05/15 17:18] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== GQL :: EXO1 ====== | ||
| - | Création d'une //géotable// : | ||
| - | <code> | ||
| - | CREATE TABLE test (id serial PRIMARY KEY, genre text); | ||
| - | SELECT AddGeometryColumn( 'test', 'geom', -1, 'GEOMETRY', 2 ); | ||
| - | </code> | ||
| - | Alimentation avec quelques géométries //en dur// : | ||
| - | <code> | ||
| - | INSERT INTO test VALUES ( 3, 'batiment 1', ST_GeometryFromText( 'POLYGON((10 10,40 20,35 8,12 4,10 10))', -1 ) ); | ||
| - | INSERT INTO test VALUES ( 4, 'batiment 2', ST_GeometryFromText( 'POLYGON((10 40,20 30,30 40,40 35,50 60,35 80,20 60,10 40))', -1 ) ); | ||
| - | INSERT INTO test VALUES ( 5, 'batiment 3', ST_GeometryFromText( 'POLYGON((10 95,20 95,20 135,10 135,10 95))', -1 ) ); | ||
| - | |||
| - | INSERT INTO test VALUES ( 1, 'pieton 1', ST_GeometryFromText( 'POINT(10 70)', -1 ) ); | ||
| - | INSERT INTO test VALUES ( 2, 'pieton 2', ST_GeometryFromText( 'POINT(30 30)', -1 ) ); | ||
| - | INSERT INTO test VALUES ( 6, 'pieton 3', ST_GeometryFromText( 'POINT(35 70)', -1 ) ); | ||
| - | INSERT INTO test VALUES ( 7, 'pieton 4', ST_GeometryFromText( 'POINT(35 60)', -1 ) ); | ||
| - | |||
| - | INSERT INTO test VALUES ( 8, 'bordure 1 route', ST_GeometryFromText( 'LINESTRING(1 85,50 85)', -1 ) ); | ||
| - | INSERT INTO test VALUES ( 9, 'bordure 2 route', ST_GeometryFromText( 'LINESTRING(1 92,50 92)', -1 ) ); | ||
| - | </code> | ||
| - | |||
| - | Comment sont stockées les géodonnées ? | ||
| - | <code> | ||
| - | SELECT AsText(the_geom), Srid(the_geom), the_geom FROM pedestrians; | ||
| - | </code> | ||
| - | d'où la syntaxe "lisible" que nous utilisons ci-dessus : | ||
| - | <code> | ||
| - | SELECT ST_GeometryFromText('POLYGON((10 10,40 20,35 8,12 4,10 10))', -1 ); | ||
| - | </code> | ||
| - | ===== Premiers pas en GQL ===== | ||
| - | (-> cf. documentation [[http://postgis.refractions.net/documentation/manual-1.5/reference.html|PostGIS Reference]]) | ||
| - | * **1. Quelles sont les aires des objets ?** -> ST_Area2d | ||
| - | <code>voir n°2</code> | ||
| - | * **2. Quel sont les types géométriques des objets ?** -> Geometrytype | ||
| - | <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> | ||
| - | SELECT genre AS pietons_dans_batiment_2 FROM test | ||
| - | WHERE | ||
| - | genre like 'pieton%' | ||
| - | AND | ||
| - | ST_WithIn(test.geom,(SELECT geom FROM test WHERE genre LIKE 'batiment 2')); | ||
| - | </code> | ||
| - | * **4. Quel est l'objet géométrique le plus proche du pieton 2 ?** -> ST_Distance | ||
| - | <code> | ||
| - | SELECT t1.genre FROM test t1, test t2 WHERE | ||
| - | 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> | ||
| - | |||
| - | //(source: [[http://www.davidgis.fr|Jean David Techer]])// | ||
ogo11/gql/exo1.txt · Last modified: 2018/05/15 17:18 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
