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)

1. Quelles sont les aires des objets ? → ST_Area2d

   cf. 2

2. Quel sont les types géométriques des objets ? → Geometrytype

   select genre, ST_Area2d(the_geom) from test where ST_GeometryType(the_geom) = 'ST_Polygon';

3. Quel est l'objet géométrique le plus proche du pieton 2 ? → ST_Distance

   select genre, ST_Distance((select the_geom from test where genre = 'pieton2'),the_geom) as dist
   from test where genre <> 'pieton2' order by dist limit 1;

   ou

   select genre from test where id <> 2 
   and
   ST_Distance(
     the_geom, 
     (select the_geom from test where id = 2)
   ) = (select min(ST_Distance(
       the_geom, 
       (select the_geom from test where id = 2)
     )) from test where id <> 2);

4. Qui est dans le bâtiment 2 ? → ST_Within

   Etape 1:
   select genre, ST_Within(
               the_geom,
               (select the_geom from test where genre = 'batiment2')) 
   from test;

   Etape2: 
   select genre from test as test1 
   where ST_Within(test1.the_geom, (select test2.the_geom from test as test2 where genre = 'batiment2')) and genre <> 'batiment2';

(source: Jean David Techer)