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.

Lau

Answer 1

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)

Shir

Answer 2

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.

Macmacabre

Answer 3

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.

Amalee

But ST_envolope is a function, not a type, as far as I can see? – Lau

Sorry, I removed a lot of convoluted nonsense just now. The points are stored as a sequence of float4's in Postgis, not sure about DB2 but it must be roughly the same, or maybe one of the db2/Oracle 'number' types. I am guessing that all rectangles are just coordinates stored as some basic float type, with the geo functionality enabled via smart indexing for common spatial queries. – Amalee

Troels is correct. ST_ENVELOPE is a function that returns an ST_POLYGON; ST_ENVELOPE is not a type. The "float" optimization is specific to PostGIS. – Macmacabre