<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">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.</font> </p>
<br>
<p style="margin-top: 0; margin-bottom: 0">
<font size="3" face="Comic Sans MS">if (x > minx && x < max && y > miny && y < maxy) then true. . . . </font> </p>
<br>
<p style="margin-top: 0; margin-bottom: 0">
<font size="3" face="Comic Sans MS">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.</font> </p>
<br>
<p style="margin-top: 0; margin-bottom: 0">
<font size="3" face="Comic Sans MS">bobb</font> </p>
<p style="margin-top: 0; margin-bottom: 0">
<br>
<br>
>>> DrYSG <ygutfreund@draper.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">
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: <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>
</p>
</div>
</td>
</tr>
</table>
</body>
</html>