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)