<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7638.1">
<TITLE>[postgis-users] Re: GiST index seems to be ignored?</TITLE>
</HEAD>
<BODY>
<DIV id=idOWAReplyText12858 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2></FONT> </DIV></DIV>
<DIV dir=ltr>Slight correction - both of those should either be 90 or 1320<BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> Obe, Regina<BR><B>Sent:</B> Fri 3/9/2007
1:09 PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> RE:
[postgis-users] Re: GiST index seems to be ignored?<BR></FONT><BR></DIV>
<DIV>
<DIV id=idOWAReplyText45949 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Do you have a gist index on
your p.the_geom.</FONT></DIV><FONT face=Arial size=2></FONT></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT>
<DIV dir=ltr><FONT face=Arial size=2>I think expand is much more efficient than
buffer for your && and I think your centroid && instead of just
using p.the_geom is totally wasteful. So I would change to do the
falling</FONT></DIV><FONT face=Arial size=2></FONT></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>1) Add a btree index on fips
on your block groups - a attribute index is almost always more selective
than a spatial index.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>2) I think you can drop that buffer index
you have going, but if you do need to keep it change it to expand(b.the_geom,
90) - for this particular query, it probably won't be used anyway if you have an
index on fips.</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>2) Make sure you have an index on
p.the_geom</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>3) Change your query to </FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2> SELECT<BR> count(j."lfc_code") as
count,<BR> sum(J."gross_sqft") as
Sum_Gross_Sqft,<BR> sum(j."units") as
Sum_Units,<BR> j."lfc_code"<BR> FROM
kc_block_groups b, parcels_06 p inner join lfc_comm j on<BR> p.pin_key =
j.pin_key<BR> WHERE expand(b.the_geom,90) &&
p.the_geom<BR> and within(centroid(p.the_geom),
buffer(b.the_geom,1320))<BR> and b.fips =
530330001001<BR> GROUP BY j."lfc_code"</FONT><BR><BR></DIV>
<DIV dir=ltr>
<HR tabIndex=-1>
</DIV>
<DIV dir=ltr><FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net on behalf of Reid
Priedhorsky<BR><B>Sent:</B> Fri 3/9/2007 12:40 PM<BR><B>To:</B> PostGIS Users
Discussion<BR><B>Subject:</B> [postgis-users] Re: GiST index seems to be
ignored?<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Josh Livni wrote:<BR><BR>> I am basically trying to find
points within a buffered polygon. I've<BR>> created a GiST index on my
polygons:<BR>> CREATE INDEX
kc_block_groups_qm_buffer_the_geom_gist<BR>> ON
kc_block_groups<BR>> USING gist<BR>> (buffer(the_geom,
1320::double precision));<BR><BR>> SELECT<BR>>
count(j."lfc_code") as count,<BR>> sum(J."gross_sqft") as
Sum_Gross_Sqft,<BR>> sum(j."units") as
Sum_Units,<BR>> j."lfc_code"<BR>> FROM
kc_block_groups b, parcels_06 p inner join lfc_comm j on<BR>> p.pin_key =
j.pin_key<BR>> WHERE buffer(b.the_geom,90) &&
centroid(p.the_geom)<BR>> and within(centroid(p.the_geom),
buffer(b.the_geom,1320))<BR>> and b.fips =
530330001001<BR>> GROUP BY j."lfc_code"<BR><BR>within()
won't take advantage of the index, and with the operator that<BR>does
(&&), your buffer() call uses a different buffer distance.
What<BR>happens if you create the index on buffer(the_geom, 90)?<BR><BR>My other
guess is that you use a typecast in the index creation but not<BR>the
query.<BR><BR>Good
luck,<BR><BR>Reid<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></FONT></P></DIV></DIV>
</BODY>
</HTML>
<HTML><BODY><P><hr size=1></P><br>
<P><STRONG><br>
The substance of this message, including any attachments, may be<br>
confidential, legally privileged and/or exempt from disclosure<br>
pursuant to Massachusetts law. It is intended solely for the<br>
addressee. If you received this in error, please contact the sender<br>
and delete the material from any computer.<br>
</STRONG></P></BODY></HTML>