<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.emailstyle17
{mso-style-name:emailstyle17;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.emailstyle18
{mso-style-name:emailstyle18;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.emailstyle19
{mso-style-name:emailstyle19;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.emailstyle20
{mso-style-name:emailstyle20;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.emailstyle21
{mso-style-name:emailstyle21;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle24
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:612.0pt 792.0pt;
margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=PT link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='color:#1F497D'>Regina,<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>As always your input
is highly appreciated. Thank you!<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Pedro.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Obe,
Regina<br>
<b>Sent:</b> sexta-feira, 9 de Março de 2007 17:58<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> RE: [postgis-users] optimize query<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<div id=idOWAReplyText58329>
<div>
<p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>I think using expand instead of buffer would be much more
efficient since expand is a much simpler process than buffer and the &&
would then reduce back to a bounding box compare anyway, but in this case it
probably doesn't matter too much since it is a static point you are buffering.</span><span
style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
</div>
<div>
<p class=MsoNormal><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p> </o:p></span></p>
<div class=MsoNormal align=center style='text-align:center'><span
style='font-size:12.0pt;font-family:"Times New Roman","serif"'>
<hr size=2 width="100%" align=center>
</span></div>
<p class=MsoNormal style='margin-bottom:12.0pt'><b><span style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'>
postgis-users-bounces@postgis.refractions.net on behalf of Pedro Doria Meunier<br>
<b>Sent:</b> Fri 3/9/2007 12:18 PM<br>
<b>To:</b> 'PostGIS Users Discussion'<br>
<b>Subject:</b> RE: [postgis-users] optimize query</span><span
style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Paul and Rob,</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>First of all thanks
for your time!</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>This is the way I
solved it in terms of speed…</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>select freguesia,
distance(geomfromtext('POINT(-9.17266 38.68783)',4326),f.geometry) as thedist </span><o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>from
warped_freguesias as f </span><o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>WHERE f.geometry
&& buffer(geomfromtext('POINT(-9.17266 38.68783)',4326), 0.016)</span><o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>order by thedist </span><o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>limit 1;</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>I used roughly a
nautical mile… </span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Now my query returns
in 15ms :P</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Pedro Doria Meunier</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Rob
Tester<br>
<b>Sent:</b> sexta-feira, 9 de Março de 2007 16:56<br>
<b>To:</b> 'PostGIS Users Discussion'<br>
<b>Subject:</b> RE: [postgis-users] optimize query</span><o:p></o:p></p>
</div>
</div>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Yeah, it helps when
you fully read the question before answering, sorry about that.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>What I do in cases
like you present is to create a buffer around the point to create an area where
that would find another object or return an error. For instance I need to find
the ‘nearest’ road to a point, if that point falls within a city geometry, I
create a buffer that is roughly 1 statute mile, if it is rural then I use a 5
mile buffer. In these cases if I don’t find a road close by, then it would be
an error. Don’t know if that would help you in your case or not. </span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>You could write a
simple stored procedure that expanded the buffer to a max limit if the query
wasn’t returning any geometries. Of course that would only help if your table
is big and speed of executing multiple queries against the index was faster
than doing a seq scan. </span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Rob</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Pedro
Doria Meunier<br>
<b>Sent:</b> Friday, March 09, 2007 9:35 AM<br>
<b>To:</b> 'PostGIS Users Discussion'<br>
<b>Subject:</b> RE: [postgis-users] optimize query<br>
<b>Importance:</b> High</span><o:p></o:p></p>
</div>
</div>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span style='color:#1F497D'>Boy that was *<b>fast</b>*! </span><span
style='font-family:Wingdings;color:#1F497D'>J</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span style='color:#1F497D'>Txs guys!</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Anyway... you’re
right Rob; but only if the point is inside the polygons… if not the query
returns zilch…</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Think of the case
when a vehicle is crossing a bridge… ;-)</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Pedro.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Rob
Tester<br>
<b>Sent:</b> sexta-feira, 9 de Março de 2007 16:10<br>
<b>To:</b> 'PostGIS Users Discussion'<br>
<b>Subject:</b> RE: [postgis-users] optimize query</span><o:p></o:p></p>
</div>
</div>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Add a where clause on
the indexed column (presumably f.geometry).</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US style='color:#1F497D'>WHERE
f.geometry&&</span><span lang=EN-US> geomfromtext('POINT(-9.1533
38.69686)',4326) </span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>Rob.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Pedro
Doria Meunier<br>
<b>Sent:</b> Friday, March 09, 2007 9:04 AM<br>
<b>To:</b> 'PostGIS Users Discussion'<br>
<b>Subject:</b> [postgis-users] optimize query<br>
<b>Importance:</b> High</span><o:p></o:p></p>
</div>
</div>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>Hi List,</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>I’m once again in need of guru advice… </span><span
lang=EN-US style='font-family:Wingdings'>J</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>This is the query:</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>select freguesia, distance(geomfromtext('POINT(-9.1533
38.69686)',4326),f.geometry) as thedist </span><o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>from freguesias as f </span><o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>order by thedist </span><o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>limit 1;</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>as you can see it returns the closest
polygon to a given point.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>Problem: 4050 (**detailed**) polygons</span><o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>exec time: 907 ms</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>The Gist index was created, btw…</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>Is there any way to optimize this query and
avoid the dreaded seq scan?</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>Txs in advance!</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal><span lang=EN-US>Pedro Doria Meunier.</span><o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
<p class=MsoNormal> <o:p></o:p></p>
</div>
<div class=MsoNormal align=center style='text-align:center'><span
style='font-size:12.0pt;font-family:"Times New Roman","serif"'>
<hr size=1 width="100%" align=center>
</span></div>
<p class=MsoNormal><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p> </o:p></span></p>
<p><b><br>
<strong>The substance of this message, including any attachments, may be</strong><br>
<strong>confidential, legally privileged and/or exempt from disclosure</strong><br>
<strong>pursuant to Massachusetts law. It is intended solely for the</strong><br>
<strong>addressee. If you received this in error, please contact the sender</strong><br>
<strong>and delete the material from any computer.</strong></b><o:p></o:p></p>
</div>
</body>
</html>