[postgis-users] ST_Overlaps and performance

[Stephen Woodbridge]

This makes indexing somewhat problematic. And if you try this in SQL 
'&&' is NOT the same as 'AND', it is a spatial comparison.

-Steve

On 3/7/2012 12:36 PM, Bob Basques wrote:
> 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
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users