Geek Answers Handbook

Spatial SQL: the most appropriate data type for a square?

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

I think I could use ST_polygon, but maybe there is a more specialized type that 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 a specific column)

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

While I am working with DB2, I am also interested in solutions that do not work on DB2 if they are compliant.

+5
source share
3 answers

In DB2, it is also a polygon. It looks like you are saving meshes, so a quick check might be that if ST_ENVELOPE (geometry) == geometry, then you have a square

This code is from

DB2 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? last POLYGON == ST_ENVELOPE (geometry)

+1
source

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

(.. , ) . . , , .

, , ST_EQUALS (ST_ENVELOPE (geom), geom) = 1.

+4

, ST_Envelope - DB2, OGC, , , , .

+1

More articles:


All Articles