Spatial indices
To optimize spatial queries, spatial indices are supported on the Geometry columns of regular tables (not shapefiles). The syntax is the following:
CREATE SPATIAL INDEX [index_name] ON table_name(geometry_column);
The spatial index is stored on disk. Several spatial operators (such as the
&&
predicate operator for bounding box overlap) use this index.
Example
In this example, we calculate the number of roads that intersect several
polygonal areas. First, we create the area
and roads
tables, putting a
spatial index on their Geometry columns:
CREATE TABLE area(idarea INT PRIMARY KEY, the_geom GEOMETRY);
CREATE SPATIAL INDEX myspatialindex ON area(the_geom);
INSERT INTO area VALUES (1,
'POLYGON((0 0, 20 0, 20 10, 0 10, 0 0))');
INSERT INTO area VALUES (2,
'POLYGON((25 5, 40 5, 40 15, 25 15, 25 5))');
INSERT INTO area VALUES (3,
'POLYGON((45 10, 50 10, 50 13, 45 13, 45 10))');
CREATE TABLE roads(idroad INT PRIMARY KEY, the_geom GEOMETRY);
CREATE SPATIAL INDEX ON roads(the_geom);
INSERT INTO roads VALUES (1, 'LINESTRING(2 2, 7 7)');
INSERT INTO roads VALUES (2, 'LINESTRING(15 -1, 30 13)');
Now we execute the request:
SELECT idarea, COUNT(idroad) roadscount
FROM area, roads
WHERE area.the_geom && roads.the_geom
AND ST_Intersects(area.the_geom, roads.the_geom)
GROUP BY idarea
ORDER BY idarea;
Result:
IDAREA | ROADSCOUNT |
---|---|
1 | 2 |
2 | 1 |
Note that ST_Intersects
does not yet support spatial
indices, but it will in a future release.