<html>
<head>
</head>
<body style="margin-left: 4px; margin-top: 4px; margin-bottom: 1px; font-weight: normal; font-size: 12pt; font-variant: normal; margin-right: 4px; font-family: Comic Sans MS; line-height: normal; font-style: normal">
<p style="margin-top: 0; margin-bottom: 0">
<font size="3" face="Comic Sans MS">That's my PERL heritage sneaking in, sorry.</font> </p>
<br>
<p style="margin-top: 0; margin-bottom: 0">
<font size="3" face="Comic Sans MS">bobb</font> </p>
<br>
<p style="margin-top: 0; margin-bottom: 0">
<br>
<br>
>>> Stephen Woodbridge <woodbri@swoodbridge.com> wrote:<br> </p>
<table border="0" bgcolor="#f3f3f3" style="margin-left: 15px; margin-top: 0; margin-bottom: 0; font-size: 1em; margin-right: 0">
<tr>
<td>
<div style="border-left: solid 1px #050505; padding-left: 7px">
<p style="margin-top: 0; margin-bottom: 0">
This makes indexing somewhat problematic. And if you try this in SQL<br>'&&' is NOT the same as 'AND', it is a spatial comparison.<br><br>-Steve<br><br>On 3/7/2012 12:36 PM, Bob Basques wrote:<br>> If the polygons are always going to be level (as indicated below) and<br>> non-rotated, you might be better off just using a regular Tabular select<br>> (and non-spatial geom) and use the minx,miny,max,may numbers directly.<br>><br>><br>> if (x > minx && x < max && y > miny && y < maxy) then true. . . .<br>><br>><br>> Maybe build a view of the bounding corners as numeric columns and try<br>> some simple queries to test. . . . Sometimes the spatial stuff is just<br>> that much overhead for certain queries.<br>><br>><br>> bobb<br>><br>><br>><br>> >>> DrYSG <ygutfreund@draper.com> wrote:<br>><br>> I have a table with 21 Million Rows. The Geometry field is a simple<br>> rectangular bounding box (Polygon with 4 corners).<br>><br>> Users will be doing executing queries to find all overlapping rows to a user<br>> supplied rectangle. I have been finding that my naive query of:<br>><br>> SELECT *<br>> FROM portal.metadata as cat<br>> WHERE ST_Overlaps(cat.location,ST_GeomFromText('POLYGON((-70.9433962<br>> 41.5384615,-72.5283019 41.5384615,-72.5283019 41.1538462,-70.9433962<br>> 41.1538462,-70.9433962 41.5384615))', 4326));<br>><br>> Can take up to 40 seconds on a cold location. However, an ST_Contains query<br>> of a point is only about 8 seconds. Yes, location is indexed using GIST.<br>><br>> CREATE INDEX catalog_location_idx<br>> ON portal.catalog<br>> USING gist<br>> (location );<br>><br>> Now, it first stuck me as odd that it was taking more than 4 times the point<br>> query to discover overlap. Then I thought about it, and I can see cases<br>> where there ST_Overlaps cannot assume certain geometries.<br>><br>> I am now considering doing a WHERE clause that checks for ST_Contains for<br>> both the Top-Left and Bottom-Right corners of the QueryRectangle since I<br>> know that all bboxes in the DB are rectangular.<br>><br>> But I thought I would see what those with knowledge of the internals of the<br>> spatial functions have to say.<br>><br>> Ideas?<br>><br>><br>><br>> --<br>> View this message in context:<br>> <a href="http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555465.html">http://postgis.17.n6.nabble.com/ST-Overlaps-and-performance-tp4555465p4555465.html</a><br>> Sent from the PostGIS - User mailing list archive at Nabble.com.<br>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>><br>><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br>_______________________________________________<br>postgis-users mailing list<br>postgis-users@postgis.refractions.net<br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</p>
</div>
</td>
</tr>
</table>
</body>
</html>