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', GeometryFromText( 'POLYGON((10 10,40 20,35 8,12 4,10 10))', -1 ) ); 
INSERT INTO test VALUES ( 4, 'batiment 2', 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', GeometryFromText( 'POLYGON((10 95,20 95,20 135,10 135,10 95))', -1 ) ); 

INSERT INTO test VALUES ( 1, 'pieton 1', GeometryFromText( 'POINT(10 70)', -1 ) ); 
INSERT INTO test VALUES ( 2, 'pieton 2', GeometryFromText( 'POINT(30 30)', -1 ) ); 
INSERT INTO test VALUES ( 6, 'pieton 3', GeometryFromText( 'POINT(35 70)', -1 ) ); 
INSERT INTO test VALUES ( 7, 'pieton 4', GeometryFromText( 'POINT(35 60)', -1 ) ); 

INSERT INTO test VALUES ( 8, 'bordure 1 route', GeometryFromText( 'LINESTRING(1 85,50 85)', -1 ) ); 
INSERT INTO test VALUES ( 9, 'bordure 2 route', GeometryFromText( 'LINESTRING(1 92,50 92)', -1 ) ); 

Comment sont stockées les géodonnées ?

SELECT AsText(the_geom), Srid(the_geom), the_geom FROM pedestrians;

d'où la syntaxe “lisible” que nous utilisons ci-dessus :

SELECT GeometryFromText('POLYGON((10 10,40 20,35 8,12 4,10 10))', -1 );

(→ 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), ST_Geometrytype(geom) FROM test;

• 3. Qui est dans le bâtiment 2 ? → ST_Distance, ST_Within

SELECT genre AS pietons_dans_batiment_2 FROM test 
WHERE
      ST_Distance((SELECT geom FROM test WHERE genre LIKE 'batiment 2'),test.geom)=0 
AND 
      genre LIKE 'pieton%';

SELECT genre AS pietons_dans_batiment_2 FROM test 
WHERE
      ST_WithIn(test.geom,(SELECT geom FROM test WHERE genre LIKE 'batiment 2'))
 AND 
      genre like 'pieton%';

• 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(tbl_dist.dist_de_pieton2) FROM 
			(
			SELECT ST_Distance(t1.geom, t2.geom) AS dist_de_pieton2 FROM test t1, test t2 
				WHERE t1.genre LIKE 'pieton 2' AND t2.genre <> 'pieton 2'
			) as tbl_dist
	)

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((SELECT geom FROM test WHERE id = 2),test.geom)) 
		FROM test WHERE id != 2
		);

(source: Jean David Techer)