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
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.
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With