Hi all,
I have an application that does a spatial search based on an expanding
radius where I double the radius each time it fails to find any results.
This is problematic if you are outside the the extents of the data being
search because it should fail. Think of the US data but the point is in
Mexico. So I created a polygon covering Mexico and overlapping the US
border, and differenced out the state polygons to create an exclusion
area polygon. This works fine and as expected.
Now the performance question. I integrated this into my stored procedure
with:
if exists(SELECT * FROM pg_catalog.pg_tables
WHERE tablename='exclusion_areas') then
select into excl id from exclusion_areas a
where st_dwithin(pnt, a.geom, 0.0) limit 1;
if FOUND then
return NULL;
end if;
end if;
This added a significant amount of CPU load to the server. I modified
the code to reduce the server load with:
if st_y(pnt) < 33.0 then
if exists(SELECT * FROM pg_catalog.pg_tables
WHERE tablename='exclusion_areas') then
select into excl id from exclusion_areas a
where st_dwithin(pnt, a.geom, 0.0) limit 1;
if FOUND then
return NULL;
end if;
end if;
end if
99% of the queries hitting the server are in the US
So what is causing the additional CPU load in the first query?
I'm speculating that it is related to detoasting the
exclusion_areas.geom (there is only one record in the table) to get the
bbox, or may it has to compute the bbox each time. Or maybe something else?
Next question, is there a way to fail quickly if the point is not within
the area represented by the data, in this case the road network for the
US and Canada. I can't use a BBOX test because the BBOX of Mexico
overlaps the BBOX of the US.
I'm happy with the way it is currently working but want to better
understand what is happening in this case.
Thoughts?
Thanks,
Steve W
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/postgis-users
