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


ogo11:gql:exo1

GQL :: EXO1

Création d'une géotable :

CREATE TABLE test (id serial PRIMARY KEY, genre text);
SELECT AddGeometryColumn( 'test', 'geom', -1, 'GEOMETRY', 2 );

Alimentation avec quelques géométries en dur :

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 ) ); 

Premiers pas en GQL

(→ cf. documentation PostGIS Reference)

  • 1. Quelles sont les aires des objets ? → ST_Area2d
voir n°2
  • 2. Quel sont les types géométriques des objets ? → Geometrytype
SELECT id, genre, ST_Area2d(geom) FROM test WHERE ST_Geometrytype(geom) = 'ST_Polygon';
  • 3. Qui est dans le bâtiment 2 ? → ST_Distance, ST_Within
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
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')); 
  • 4. Quel est l'objet géométrique le plus proche du pieton 2 ? → ST_Distance
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'
 )

(source: Jean David Techer)

ogo11/gql/exo1.txt · Last modified: 2018/05/15 17:18 (external edit)