[postgis-users] ST_Overlaps and performance

[Stephen Woodbridge]

Oh! good catch that would work unless the the bbox polygons can be 
rotated. Again this will collect the touches case so if you need a true 
overlaps then add the and clause.

-Steve W

On 3/7/2012 12:34 PM, Nicolas Ribot wrote:
> Hi,
>
> Correct me if i'm wrong, but as your input geometries are bbox
> rectangle, you could juste use
> an bbox filter to see if rectangles overlaps:
>
> SELECT *
> FROM portal.metadata as cat
> WHERE cat.location&&  'SRID=4326;POLYGON((-70.9433962
> 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962
> 41.1538462,-70.9433962 41.5384615))'::geometry;
>
> Nicolas
>
>
>
> On 7 March 2012 18:26, 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users