-- The city GIS technician suspects that some of the data in the
-- buildingfootprints table was somehow duplicated. To alleviate his
-- concern, he queries the table to determine if any of the footprints
-- multipolygons are equal.
-- The buildingfootprints table is created with the following statement.
-- The building_id column uniquely identifies the buildings, the lot_id
-- identifies the building's lot, and the footprint multipolygon stores 
-- the building's geometry.

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)
);

-- The buildingfootprints table is spatially joined to itself by the 
-- ST_Equals predicate, which returns t (TRUE) whenever it finds two
-- multipolygons that are equal.  The bf1.building_id <> bf2.building_id
-- condition eliminates the comparison of a geometry to itself.

SELECT bf1.building_id, bf2.building_id
   FROM buildingfootprints bf1, buildingfootprints bf2
   WHERE ST_Equals(bf1.footprint,bf2.footprint)
   AND bf1.building_id <> bf2.building_id;



-- To illustrate the difference between ST_Equals and Equal, 
-- consider the following example:

CREATE TABLE equal_test (id   integer,
                         line ST_LineString);

INSERT INTO equal_test VALUES
   (1, ST_LineFromText('linestring(10 10, 20 20)', 1000));
INSERT INTO equal_test VALUES
   (2, ST_LineFromText('linestring(20 20, 10 10)', 1000));

-- The following query will return both rows, because ST_Equals 
-- determines that both linestrings are spatially equivalent:

SELECT id FROM equal_test 
   WHERE ST_Equals (line, ST_LineFromText('linestring(10 10, 20 20)', 1000));

-- The following query will return only the first row, because Equals
-- only performs a memory comparison of the linestrings:

SELECT id FROM equal_test 
   WHERE line = ST_LineFromText('linestring(10 10, 20 20)', 1000);