Filtering using a bounding box

You can filter objects for spatial processing by using their bounding boxes.

To calculate and record the bounding box for each object in a table:

For each column that needs to be filtered, add 4 columns of type DOUBLE to a table. These are the bounding-box columns. For example, these ALTER TABLE statements add the bounding-box columns xmin, ymin, xmax, and ymax to the table geotab:

For a new table:

create table geotab (id bigint not null primary key, geo st_geometry,
           xmin double generated as (st_minx(geo)),
           ymin double generated as (st_miny(geo)),
           xmax double generated as (st_maxx(geo)),
           ymax double generated as (st_maxy(geo) ));

For an existing row-oriented table:

set integrity for geotab off;

alter table geotab add column xmin double generated as (st_minx(geo));
alter table geotab add column ymin double generated as (st_miny(geo));
alter table geotab add column xmax double generated as (st_maxx(geo));
alter table geotab add column ymax double generated as (st_maxy(geo));

set integrity for geotab immediate checked force generated;

The integrity processing automatically updates the values in the existing rows.

For an existing column-oriented table:

alter table geotab add column xmin double;
alter table geotab add column ymin double;
alter table geotab add column xmax double;
alter table geotab add column ymax double;

Note that the bounding box columns will not be automatically updated. To update them to reflect changes to the spatial data to which they refer, issue an update command, for example:

update geotab set (xmin, ymin, xmax, ymax) = (st_minx(geo), st_miny(geo), st_maxx(geo), st_maxy(geo));

Create an index on each of the columns, for example:

create index myidx on geotab (xmin, ymin, xmax, ymax)

You can then modify your queries so that they filter the objects to be processed based on their bounding boxes. For example, if 2 objects are to be processed only if the bounding box of one of them (in table a) is entirely within the bounding box of the other (in table b), you can add the following predicate to each query:
```
where a.xmin <= b.xmax
and b.xmin <= a.xmax
and a.ymin <= b.ymax
and b.ymin <= a.ymax
```
For example:
```
select a.id, b.name from geotab a, othertab b
where a.xmin <= b.xmax
and b.xmin <= a.xmax
and a.ymin <= b.ymax
and b.ymin <= a.ymax
and st_intersects(a.geo, b.geo) = 1;
```
For distance queries, increase the bounding box values based on the distance specified by the ST_DISTANCE function. For example, each of the following SELECT statements finds all rows for which the geometries are no farther apart than the specified distance. The geometries use the coordinate system WGS84 (srs_id=4326).
- In the following SELECT statement, the ST_DISTANCE function uses the WGS84 default units, which are degrees. Therefore, the value specified for the ST_DISTANCE function and for the increase in each dimension of the bounding box is the same (0.1 degrees):
```
select a.id, b.name from geotab a, othertab b
where a.xmin <= b.xmax + 0.1
and b.xmin <= a.xmax + 0.1
and a.ymin <= b.ymax + 0.1
and b.ymin <= a.ymax + 0.1
and st_distance(a.geo, b.geo) < 0.1;
```
- In the following SELECT statement, the ST_DISTANCE function uses the units MILES. One degree of latitude is between 68.7 and 69.4 miles, and one degree of longitude is 69.17 miles at the equator and decreases to 0 miles at the poles. So, for simplicity, you can use 70 miles as an upper boundary when converting miles into degrees. The value of 35 miles that is specified for the ST_DISTANCE function corresponds to about 0.5 degrees, and that is the value that is specified as the increase in each dimension of the bounding box:
```
select a.id, b.name from geotab a, othertab b
where a.xmin <= b.xmax + 0.5
and b.xmin <= a.xmax + 0.5
and a.ymin <= b.ymax + 0.5
and b.ymin <= a.ymax + 0.5
and st_distance(a.geo, b.geo, 'MILES') < 35;
```