[postgis-devel] What's the point of this exercise in intersects?

Obe, Regina robe.dnd at cityofboston.gov
Fri Sep 12 05:02:39 PDT 2008


> Really, it's the tests that will determine it's value.  I may run a
few
> of my own today and see what I get.

> That would be great if you get to test it.  I'm still fighting with my
> various boxes so probably won't be able to get to it for another week.

I finally got around to running a quick test with ST_Contains (removing
the bbox check vs. keeping it). Haven't bothered testing the other
functions.  I presume the results should be similar.

the .sql file is attached if anyone wants to run similar tests or make
fun of my example.  My objective was to determine
how much of a penalty we are paying in cases where you have many points
contained in a polygon.  As was pointed out recently, the main smoking
gun that I overlooked was that a point in MULTIPOLYGON doesn't share the
same short-circuit advantage as a point in POLYGON (so that is the main
reason for the stark difference between ST_DWithin and ST_Contains when
applied to point in MULTIPOLYGON situation).  

There still does seem to be a penalty paid for having this extra
bounding box check.  See stats below
This I ran on OpenSUSE 10.3 - POSTGIS="1.3.4" GEOS="3.0.0-CAPI-1.4.1"
PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS

--100 records (max number of points contained: 14218, min number of
points 4326
--current st_contains (with redundant bbox check) -- 7,484 ms, 7,344 ms,
7,531 ms 
SELECT poly.gid, count(poi.gid) as numpois
FROM test_poly poly INNER JOIN test_poi poi
	ON ST_Contains(poly.the_geom, poi.the_geom)
GROUP BY poly.gid
ORDER BY numpois DESC;

--st_contains (no redundant bbox check) -- 6,797 ms | 6,813 ms | 6,859
ms  
SELECT poly.gid, count(poi.gid) as numpois
FROM test_poly poly INNER JOIN test_poi poi
	ON ST_Contains(poly.the_geom, poi.the_geom)
GROUP BY poly.gid
ORDER BY numpois DESC;

Thanks,
Regina


-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: piptest080912.sql
Type: application/octet-stream
Size: 1725 bytes
Desc: piptest080912.sql
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20080912/ebbef92a/attachment.obj>


More information about the postgis-devel mailing list