[postgis-users] ST_Overlaps and performance
woodbri at swoodbridge.com
Wed Mar 7 09:35:48 PST 2012
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.
On 3/7/2012 12:34 PM, Nicolas Ribot wrote:
> 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;
> 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.
>> 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
> postgis-users mailing list
> postgis-users at postgis.refractions.net
More information about the postgis-users