<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 http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@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:0in;
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;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle20
{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:8.5in 11.0in;
margin:70.85pt 85.05pt 70.85pt 85.05pt;}
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=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='color:#1F497D'>Yeah, it helps when you fully
read the question before answering, sorry about that.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span 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. <o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span 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. <o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Rob<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><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
[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<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span lang=PT style='color:#1F497D'>Boy that was *<b>fast</b>*!
</span><span lang=PT style='font-family:Wingdings;color:#1F497D'>J</span><span
lang=PT style='color:#1F497D'><o:p></o:p></span></p>
<p class=MsoNormal><span lang=PT style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=PT style='color:#1F497D'>Txs guys!<o:p></o:p></span></p>
<p class=MsoNormal><span lang=PT style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Anyway... you’re right
Rob; but only if the point is inside the polygons… if not the query
returns zilch…<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Think of the case when a vehicle
is crossing a bridge… ;-)<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Pedro.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><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
[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<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><span lang=PT><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Add a where clause on the
indexed column (presumably f.geometry).<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>WHERE f.geometry&&</span>
geomfromtext('POINT(-9.1533 38.69686)',4326) <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Rob.<o:p></o:p></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><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
[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<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Hi List,<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>I’m once again in need of guru advice… <span
style='font-family:Wingdings'>J</span><o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>This is the query:<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>select freguesia, distance(geomfromtext('POINT(-9.1533
38.69686)',4326),f.geometry) as thedist <o:p></o:p></p>
<p class=MsoNormal>from freguesias as f <o:p></o:p></p>
<p class=MsoNormal>order by thedist <o:p></o:p></p>
<p class=MsoNormal>limit 1;<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>as you can see it returns the closest polygon to a given
point.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Problem: 4050 (**detailed**) polygons<o:p></o:p></p>
<p class=MsoNormal>exec time: 907 ms<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>The Gist index was created, btw…<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Is there any way to optimize this query and avoid the
dreaded seq scan?<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Txs in advance!<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Pedro Doria Meunier.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
</div>
</body>
</html>