[postgis-users] ST_Overlaps and performance

[Bob Basques]

If the polygons are always going to be level (as indicated below) and non-rotated, you might be better off just using a regular Tabular select (and non-spatial geom) and use the minx,miny,max,may numbers directly. 

if (x > minx && x < max && y > miny && y < maxy) then true. . . .  

Maybe build a view of the bounding corners as numeric columns and try some simple queries to test. . . .  Sometimes the spatial stuff is just that much overhead for certain queries. 

bobb 


>>> DrYSG <ygutfreund at draper.com> wrote:


I have a table with 21 Million Rows. The Geometry field is a simple
rectangular bounding box (Polygon with 4 corners).

Users will be doing executing queries to find all overlapping rows to a user
supplied rectangle. I have been finding that my naive query of:

SELECT *
FROM portal.metadata as cat
WHERE ST_Overlaps(cat.location,ST_GeomFromText('POLYGON((-70.9433962
41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
41.1538462,-70.9433962 41.5384615))', 4326));

Can take up to 40 seconds on a cold location. However, an ST_Contains query
of a point is only about 8 seconds. Yes, location is indexed using GIST.

CREATE INDEX catalog_location_idx
  ON portal.catalog
  USING gist
  (location );

Now, it first stuck me as odd that it was taking more than 4 times the point
query to discover overlap. Then I thought about it, and I can see cases
where there ST_Overlaps cannot assume certain geometries.

I am now considering doing a WHERE clause that checks for ST_Contains for
both the Top-Left and Bottom-Right corners of the QueryRectangle since I
know that all bboxes in the DB are rectangular.

But I thought I would see what those with knowledge of the internals of the
spatial functions have to say.

Ideas?



--
View this message in context: http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555465.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120307/91d425e6/attachment.html>