-- The city engineer needs a list of all buildings within one foot of
-- any lot line.  The building_id column of the buildingfootprints table
-- uniquely identifies each building.  The lot_id column identifies
-- the lot each building belongs to. The footprints multipolygon
-- stores the geometry of each building's footprint.

CREATE TABLE buildingfootprints (building_id  integer,
                                 lot_id       integer,
                                 footprint    ST_Multipolygon);

-- The lots table stores the lot_id, which uniquely identifies each lot,
-- and the lot ST_MultiPolygon that contains the lot geometry.

CREATE TABLE lots (lot_id  integer,
                   lot     ST_Multipolygon);

INSERT INTO buildingfootprints VALUES(
   506, 1010,
   ST_MPolyFromText('multipolygon (((7.0 45.0,15.0 45.0,15.0 51.0,18.0 51.0,18.0 54.0,8.0 54.0,8.0 51.0,7.0 51.0,7.0 45.0)))',1000)
);

INSERT INTO buildingfootprints VALUES(
   543, 2930,
   ST_MPolyFromText('multipolygon (((26.0 55.0,38.0 55.0,38.0 48.0,34.0 48.0,34.0 50.0,26.0 50.0,26.0 55.0)))',1000)
);

INSERT INTO buildingfootprints VALUES(
   1208, 203,
   ST_MPolyFromText('multipolygon (((8.0 39.0,12.0 39.0,12.0 33.0,17.0 33.0,17.0 22.0,8.0 22.0,8.0 39.0)))',1000)
);

INSERT INTO buildingfootprints VALUES(
   178, 5192,
  ST_MPolyFromText('multipolygon (((26.0 33.0,38.0 33.0,38.0 24.0,33.0 24.0,33.0 27.0,26.0 27.0,26.0 33.0)))',1000)
);

INSERT INTO lots VALUES(
   1010,
   ST_MPolyFromText('multipolygon (((2 57,21.5 57,21.5 38,2 38,2 57)))',1000)
);

INSERT INTO lots VALUES(
   2930,
   ST_MPolyFromText('multipolygon (((21.5 57,40 57,40 38,21.5 38,21.5 57)))',1000)
);

INSERT INTO lots VALUES(
   203,
   ST_MPolyFromText('multipolygon (((21.5 38,40 38,40 20,21.5 20,21.5 38)))',1000)
);

INSERT INTO lots VALUES(
   5192,
   ST_MPolyFromText('multipolygon (((2 20,2 38,21.5 38,21.5 20,2 20)))',1000)
);

-- The query returns a list of building IDs that are within one foot of
-- their lot lines. The ST_Distance function performs a spatial join on the
-- footprints and lot ST_MultiPolygons. However, the equijoin between
-- bf.lot_id and lots.lot_id ensures that only the ST_MultiPolygons
-- belonging to the same lot are compared by the ST_Distance function.

SELECT bf.building_id
   FROM buildingfootprints bf, lots
   WHERE bf.lot_id = lots.lot_id
   AND ST_Distance(footprint,lot) <= 1.0;