"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
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 ) );
(→ cf. documentation PostGIS Reference)
voir n°2
SELECT id, genre, ST_Area2d(geom) FROM test WHERE ST_Geometrytype(geom) = 'ST_Polygon';
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'));
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)