"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é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 ) );
(→ cf. documentation PostGIS Reference)
cf. 2
SELECT *, ST_Area(the_geom) FROM test WHERE ST_Geometrytype(the_geom) = 'ST_Polygon'
SELECT genre, ST_Distance(the_geom, (SELECT the_geom FROM test WHERE id = 2)) as dist FROM test WHERE id <> 2 ORDER BY dist LIMIT 1 OU SELECT t1.* FROM test t1, test t2 WHERE t2.genre LIKE 'pieton2' AND ST_Distance(t1.the_geom,t2.the_geom) = ( SELECT min(ST_Distance(t1.the_geom, t2.the_geom)) FROM test t1, test t2 WHERE t1.genre LIKE 'pieton2' AND t2.genre <> 'pieton2' )
SELECT * FROM test WHERE genre like 'pieton%' AND ST_WithIn(test.the_geom, (SELECT the_geom FROM test WHERE genre LIKE 'batiment2'));
(source: Jean David Techer)