Hi Regina,
On 11/18/2016 3:28 AM, Regina Obe wrote:
Since you are doing 0.0, you might be better off using ST_Intersects
So
st_dwithin(pnt, a.geom, 0.0)
change to:
ST_Intersects(pnt,a.geom)
Did this change recently? I was under the impression that st_dwithin was
faster than st_intersects? Anyway, this is good to know, Thanks!
Or did you try that already?
How many points does ST_NPoints return for your exclusion area? If
a lot, then detoasting might be an issue and then you might want to
consider simplifying your exclusion areas just a little.
The polygon has 33,747 points. It is in SRID:4326
select st_npoints(ST_SimplifyPreserveTopology(geom, 0.001)) from
exclusion_areas; -- 2528
select st_npoints(ST_SimplifyPreserveTopology(geom, 0.0007)) from
exclusion_areas; -- 3226
select st_npoints(ST_SimplifyPreserveTopology(geom, 0.0005)) from
exclusion_areas; -- 4040
select st_npoints(ST_SimplifyPreserveTopology(geom, 0.0002)) from
exclusion_areas; -- 7029
select st_npoints(ST_SimplifyPreserveTopology(geom, 0.0001)) from
exclusion_areas; -- 10424
I can probably live with 0.0007 * 111120.0 = 77.784 m tolerance, which
reduces the npoint of the polygon by an order of magnitude.
Thanks for the suggestions.
-Steve
-----Original Message----- From: postgis-users
[mailto:[email protected]] On Behalf Of Stephen
Woodbridge Sent: Thursday, November 17, 2016 4:15 PM To: PostGIS
Users Discussion <[email protected]> Subject:
[postgis-users] Question on performance probably related to detoast
and/or bboxes
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
_______________________________________________ postgis-users mailing
list [email protected]
http://lists.osgeo.org/mailman/listinfo/postgis-users
---
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
