Spatial SQL: Most suitable datatype for a square?

Question

I have a spatially enabled database (DB2, in this case). I need to store a large number of squares in a table. Which standard spatial SQL datatype is most suitable?

I guess I could use an ST_polygon, but maybe there is a more specialized type which would give

• better performance
• better data guarantees (I want to catch it as an error if someone where to store a non-square value in the particular column)

I've tried to find an ST_rectangle or ST_square type, but they don't seem to exist(?)

While I'm working with DB2, I'm also interested in solutions which don't work on DB2, as long as they are standards-compliant.

Answer 1

You may be looking for ST_Envelope -- I don't know for sure about DB2 but it is part of the OGC standard. Any non-vertical or non-horizontal line, or polygon, will generate a rectangle via this function, storing the coordinates typically as floats.

Answer 2

In DB2 it is also a Polygon. It looks like you are storing grids, so a quick check could be that if ST_ENVELOPE(geometry) == geometry then you have a square

This code is from

DB2's documentation

SET CURRENT PATH = CURRENT PATH, db2gse;
CREATE TABLE sample_geoms (id INTEGER, geometry ST_Geometry);

INSERT INTO sample_geoms VALUES
(1, ST_Geometry(ST_Point('point EMPTY',0)));

INSERT INTO sample_geoms VALUES
(2, ST_Geometry(ST_Point('point zm (10 10 16 30)' ,0)));

INSERT INTO sample_geoms VALUES
(3, ST_Geometry(ST_Multipoint('multipoint m (10 10 5, 50 10 6, 
   10 30 8)' ,0)));

INSERT INTO sample_geoms VALUES
(4, ST_Geometry(ST_Linestring('linestring (10 10, 20 10)',0)));

INSERT INTO sample_geoms VALUES
(5, ST_Geometry(ST_Polygon('polygon((40 120, 90 120, 90 150, 
   40 150, 40 120))',0)));


SELECT id, CAST(ST_AsText(ST_Envelope(geometry)) as VARCHAR(160))  Envelope
FROM sample_geoms;

Results:

ID          ENVELOPE
----------- ---------------------------------------------------------------
      1     -

      2     POLYGON (( 9 9, 11 9, 11 11, 9 11, 9 9))

      3     POLYGON (( 10 10, 50 10, 50 30, 10 30, 10 10))

      4     POLYGON (( 10 9, 20 9, 20  11, 10 11, 10 9))

      5     POLYGON (( 40 120, 90 120, 90 150, 40 150, 40 120))

See ID = 5? the last POLYGON == ST_ENVELOPE(geometry)

Answer 3

Even if your data represents a rectangle or square, you will still need to use the ST_POLYGON type. However, when you perform a query against the data, you can use a first-order filters such as ST_EnvIntersects.

Normally, a spatial database will compare the envelopes (i.e. a rectangle that contains the polygon) for an intersection. Then it performs the more expensive polygon-to-polygon intersection calculation. In this case, since your polygons are equal to the envelope, you can skip the second more expensive step.

As far as data validation, you can add a database trigger that checks ST_EQUALS(ST_ENVELOPE(geom),geom) = 1.