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


geoinf18:gql2nd

Spatial Query Language

Créer une géotable :

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

Alimenter la table avec quelques géométries en dur et pas vraiment “spatiales” (SRS = -1 !!):

INSERT INTO test VALUES ( 3, 'batiment1', ST_GeometryFromText( 'POLYGON((10 10,40 20,35 8,12 4,10 10))', -1 ) ); 
INSERT INTO test VALUES ( 4, 'batiment2', 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, 'batiment3', ST_GeometryFromText( 'POLYGON((10 95,20 95,20 135,10 135,10 95))', -1 ) ); 

INSERT INTO test VALUES ( 1, 'pieton1', ST_GeometryFromText( 'POINT(10 70)', -1 ) ); 
INSERT INTO test VALUES ( 2, 'pieton2', ST_GeometryFromText( 'POINT(30 30)', -1 ) ); 
INSERT INTO test VALUES ( 6, 'pieton3', ST_GeometryFromText( 'POINT(35 70)', -1 ) ); 
INSERT INTO test VALUES ( 7, 'pieton4', ST_GeometryFromText( 'POINT(35 60)', -1 ) ); 

INSERT INTO test VALUES ( 8, 'bordureRoute1', ST_GeometryFromText( 'LINESTRING(1 85,50 85)', -1 ) ); 
INSERT INTO test VALUES ( 9, 'bordureRoute2', ST_GeometryFromText( 'LINESTRING(1 92,50 92)', -1 ) ); 

A nous de jouer

(→ cf. documentation PostGIS Reference)

  1. Quelles sont les aires des objets ? → ST_Area2d
    Cf. 2
  2. Quel sont les types géométriques des objets ? → Geometrytype
    select ST_Area(the_geom) from test where GeometryType(the_geom) = 'POLYGON';
  3. Quel est l'objet géométrique le plus proche du pieton 2 ? → ST_Distance
    select ST_distance((select the_geom from test where genre = 'pieton2'), the_geom) as dist from test where genre <> 'pieton2' order by dist limit 1

    Mais on doit faire mieux, sans le order by !

  4. Qui est dans le bâtiment 2 ? → ST_Within
    select genre from test as t1 where 
    t1.genre <> 'batiment2' 
    and 
    ST_Within(
     t1.the_geom, 
     (select t2.the_geom from test as t2 where t2.genre = 'batiment2')
    )

(source: Jean David Techer)

geoinf18/gql2nd.txt · Last modified: 2018/10/31 16:49 by oertz