-- The CREATE TABLE statement below creates the empty_test table
-- with geotype, which stores the data type of the subclasses
-- that are stored in the g1 ST_Geometry column.

CREATE TABLE empty_test (geotype varchar(20),
                         g1      ST_Geometry);

-- The INSERT statements insert two records for the geometry subclasses
-- point, linestring, and polygon: one that is empty and one that is not.

INSERT INTO empty_test VALUES( 
    'Point', ST_PointFromText('point (10.02 20.01)',1000) 
);

INSERT INTO empty_test VALUES( 
    'Point', ST_PointFromText('point empty',1000) 
);

INSERT INTO empty_test VALUES( 
    'Linestring', 
    ST_LineFromText('linestring (10.02 20.01,10.32 23.98,11.92 25.64)',1000) 
);

INSERT INTO empty_test VALUES( 
   'Linestring',
   ST_LineFromText('linestring empty',1000) 
);

INSERT INTO empty_test VALUES( 
    'Polygon', 
    ST_PolyFromText('polygon ((10.02 20.01,11.92 35.64,25.02 34.15,19.15 33.94,10.02 20.01))',1000) 
);

INSERT INTO empty_test VALUES( 
    'Polygon',
    ST_PolyFromText('polygon empty',1000) 
);

-- The query returns the geometry type from the geotype column and
-- the results of the ST_IsEmpty function.

SELECT geotype, ST_IsEmpty(g1) Is_it_empty 
   FROM empty_test
