-- In the example below two tables are created: one, buildingfootprints,
-- contains a city's building footprints while the other, lots, contains
-- its lots. The city engineer wants to make sure that all the building
-- footprints are completely inside their lots.
-- In both tables the multipolygon data type stores the ST_Geometry
-- of the building footprints and the lots. The database designer selected
-- ST_MultiPolygons for both features because she realizes a lot can be
-- separated by a natural feature such as a river, and a building footprint
-- can be made up of several buildings. 

CREATE TABLE buildingfootprints (building_id  integer,
                                 lot_id       integer,
                                 footprint    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)
);

CREATE TABLE lots (lot_id  integer,
                   lot     ST_MultiPolygon);

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 city engineer first selects the buildings that are not completely
-- within a lot.

SELECT building_id
   FROM buildingfootprints, lots
   WHERE ST_Within(footprint,lot);

-- The city engineer realizes that although the first query will
-- provide her with a list of all building_id that have footprints
-- outside of a lot polygon, it won't tell her if the rest have
-- the correct lot_id assigned to them. This second query performs
-- a data integrity check on the lot_id column of the buildingfootprints table.

SELECT bf.building_id, bf.lot_id bldg_lot_id, lots.lot_id lots_lot_id 
   FROM buildingfootprints bf, lots
   WHERE ST_Within(footprint,lot)
   AND lots.lot_id <> bf.lot_id;