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

Site Tools

Trace:
ogo11:gql:exo1

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
ogo11:gql:exo1 [2013/04/17 14:08]
oertz 		ogo11:gql:exo1 [2018/05/15 17:18] (current)
Line 8: Line 8:
 Alimentation avec quelques géométries //en dur// : Alimentation avec quelques géométries //en dur// :
 <​code>​ <​code>​
-INSERT INTO test VALUES ( 3, '​batiment 1', ​GeometryFromText( '​POLYGON((10 10,40 20,35 8,12 4,10 10))', -1 ) );  +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', ​GeometryFromText( '​POLYGON((10 40,20 30,30 40,40 35,50 60,35 80,20 60,10 40))', -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', ​GeometryFromText( '​POLYGON((10 95,20 95,20 135,10 135,10 95))', -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', ​GeometryFromText( '​POINT(10 70)', -1 ) );  +INSERT INTO test VALUES ( 1, '​pieton 1', ​ST_GeometryFromText( '​POINT(10 70)', -1 ) );  
-INSERT INTO test VALUES ( 2, '​pieton 2', ​GeometryFromText( '​POINT(30 30)', -1 ) );  +INSERT INTO test VALUES ( 2, '​pieton 2', ​ST_GeometryFromText( '​POINT(30 30)', -1 ) );  
-INSERT INTO test VALUES ( 6, '​pieton 3', ​GeometryFromText( '​POINT(35 70)', -1 ) );  +INSERT INTO test VALUES ( 6, '​pieton 3', ​ST_GeometryFromText( '​POINT(35 70)', -1 ) );  
-INSERT INTO test VALUES ( 7, '​pieton 4', ​GeometryFromText( '​POINT(35 60)', -1 ) ); +INSERT INTO test VALUES ( 7, '​pieton 4', ​ST_GeometryFromText( '​POINT(35 60)', -1 ) ); 
  
-INSERT INTO test VALUES ( 8, '​bordure 1 route', ​GeometryFromText( '​LINESTRING(1 85,50 85)', -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', ​GeometryFromText( '​LINESTRING(1 92,50 92)', -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 GeometryFromText('​POLYGON((10 10,40 20,35 8,12 4,10 10))', -1 );+
 </​code>​ </​code>​
 ===== Premiers pas en GQL ===== ===== Premiers pas en GQL =====
Line 34: Line 25:
 <​code>​voir n°2</​code>​ <​code>​voir n°2</​code>​
   * **2. Quel sont les types géométriques des objets ?** -> Geometrytype   * **2. Quel sont les types géométriques des objets ?** -> Geometrytype
-<​code>​SELECT id, genre, ST_Area2d(geom), ST_Geometrytype(geom) ​FROM test;</​code>​+<​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   * **3. Qui est dans le bâtiment 2 ?** -> ST_Distance,​ ST_Within
-<​code>​SELECT genre AS pietons_dans_batiment_2 FROM test +<​code>​ 
 +SELECT genre AS pietons_dans_batiment_2 FROM test 
 WHERE WHERE
-      ​ST_Distance((SELECT geom FROM test WHERE genre LIKE '​batiment 2'​),​test.geom)=0 ​ + genre LIKE '​pieton%'​ 
-AND  +AND 
-      genre LIKE '​pieton%';​+ ST_Distance((SELECT geom FROM test WHERE genre LIKE '​batiment 2'​),​test.geom)=0
 </​code>​ </​code>​
 <​code>​ <​code>​
 SELECT genre AS pietons_dans_batiment_2 FROM test  SELECT genre AS pietons_dans_batiment_2 FROM test 
 WHERE WHERE
-      ST_WithIn(test.geom,​(SELECT geom FROM test WHERE genre LIKE '​batiment 2')) + genre like '​pieton%'​
- AND  +
-      ​genre like '​pieton%'; +
-</​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 AND
- ST_Distance(t1.geom,t2.geom) = ( + ​ST_WithIn(test.geom,​(SELECT geom FROM test WHERE genre LIKE 'batiment ​2')); 
- SELECT ​min(tbl_dist.dist_de_pieton2) FROM  +
- ( +
- SELECT ST_Distance(t1.geom, t2.geom) AS dist_de_pieton2 ​FROM test t1, test t2  +
- WHERE t1.genre LIKE 'pieton 2' AND t2.genre <> '​pieton ​2' +
- ) as tbl_dist +
- )+
 </​code>​ </​code>​
 +  * **4. Quel est l'​objet géométrique le plus proche du pieton 2 ?** -> ST_Distance
 <​code>​ <​code>​
 SELECT t1.genre FROM test t1, test t2 WHERE  SELECT t1.genre FROM test t1, test t2 WHERE 
- t2.genre LIKE '​pieton 2'+ t2.genre LIKE '​pieton 2'
 AND AND
- ST_Distance(t1.geom,​t2.geom) = ( + ST_Distance(t1.geom,​t2.geom) = 
- SELECT min(ST_Distance((SELECT ​geom FROM test WHERE id = 2),test.geom)) ​ + ( 
- FROM test WHERE id != 2 +  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]])//
ogo11/gql/exo1.1366200480.txt.gz · Last modified: 2018/05/16 10:05 (external edit)

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International
CC Attribution-Noncommercial-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki