I am using PostreSQL 9.3 w/ PostGIS 2.1.8 on Amazon RDS. I have a table named project_location, which defines "geo-fences" (each one is essentially a coordinate and radius). The geo-fence is stored using a geometry column named "location" and a double column named 'radius'. I have a spatial index on the location column.
CREATE TABLE project_location
(
...
location geography(Point,4326),
radius double precision NOT NULL,
...
)
CREATE INDEX gix_project_location_location
ON project_location USING gist (location);
The table currently has ~50,000 records in it. If I query the table to find all project_locations where the geo-fence contains a point, something like
SELECT COUNT(*)
FROM project_location
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(-84.1000, 34.0000),4326)::geography, radius);
I find that the spatial index is not used. The results of EXPLAIN show the following:
"Aggregate (cost=11651.97..11651.98 rows=1 width=0)"
" -> Seq Scan on project_location (cost=0.00..11651.97 rows=1 width=0)"
" Filter: ((location && _st_expand('0101000020E610000066666666660655C00000000000004140'::geography, radius)) AND ('0101000020E610000066666666660655C00000000000004140'::geography && _st_expand(location, radius)) AND _st_dwithin(location, '0101000020E610000066666666660655C00000000000004140'::geography, radius, true))"
However, if the radius is a constant value, like in the following
SELECT COUNT(*)
FROM project_location
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(-84.1000, 34.0000),4326)::geography, 1000);
The spatial index is used as show by EXPLAIN
"Aggregate (cost=8.55..8.56 rows=1 width=0)"
" -> Index Scan using gix_project_location_location on project_location (cost=0.28..8.55 rows=1 width=0)"
" Index Cond: (location && '0101000020E610000066666666660655C00000000000004140'::geography)"
" Filter: (('0101000020E610000066666666660655C00000000000004140'::geography && _st_expand(location, 1000::double precision)) AND _st_dwithin(location, '0101000020E610000066666666660655C00000000000004140'::geography, 1000::double precision, true))"
Having read how ST_DWithin uses indexes I understand why this is the case. Essentially a bounding box based on the radius is used to "pre-filter" the candidate points to determine the possible matches before doing the relatively expensive distance calculation on those points.
My question is there any way to do this type of search so that a spatial index can be used? Basically a way to query a table with a bunch of variable radius geo-fences?
PostGIS allows to speed up your query by using a functional index. I'm not sure how to do it in geography
datatype as there is no ST_Expand there, but the query will be quite simple if you store your data in some Mercator projection (say, SRID=3857
).
Idea:
radius
units around your point;On your project_location
table:
create index on project_location using gist (ST_Expand(location, radius));
Now you can use ST_Expand(location, radius)
as if it was a your indexed geometry column.
select count(*) from project_location where ST_Intersects(ST_Expand(location, radius), <your_point>) and ST_Distance(location, <your_point>) < radius;
Now you're skipping ST_DWithin
as you want the recheck to never attempt to use index, and use your index on function of your geometry.
For geography
you may try to stub ST_Expand with ST_Envelope(ST_Buffer(geom, radius))
.
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