<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<html >
<head>
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
font-face
{font-family:Wingdings;}
font-face
{font-family:"Cambria Math";}
font-face
{font-family:Calibri;}
font-face
{font-family:Tahoma;}
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{
color:purple;
text-decoration:underline;}
span.EmailStyle17
{
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle19
{
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle20
{
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle21
{
font-family:"Calibri","sans-serif";
color:#1F497D;}
..MsoChpDefault
{
font-size:10.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=PT link=blue vlink=purple>
<DIV id=idOWAReplyText58329 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>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.</FONT></DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
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<BR></FONT><BR></DIV>
<DIV>
<DIV class=Section1>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">Paul and
Rob,</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">First of all thanks
for your time!</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">This is the way I
solved it in terms of speed…</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </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></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">from
warped_freguesias as f </SPAN></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></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">order by thedist
</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">limit 1;</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">I used roughly a
nautical mile… </SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">Now my query returns
in 15ms :P</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">Pedro Doria
Meunier</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<DIV>
<DIV
style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: #b5c4df 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: medium none; PADDING-TOP: 3pt; BORDER-BOTTOM: medium none">
<P class=MsoNormal><B><SPAN lang=EN-US
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'">From:</SPAN></B><SPAN
lang=EN-US style="FONT-SIZE: 10pt; 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></P></DIV></DIV>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </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></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </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></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </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></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">Rob</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<DIV>
<DIV
style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: #b5c4df 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: medium none; PADDING-TOP: 3pt; BORDER-BOTTOM: medium none">
<P class=MsoNormal><B><SPAN lang=EN-US
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'">From:</SPAN></B><SPAN
lang=EN-US style="FONT-SIZE: 10pt; 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></P></DIV></DIV>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN style="COLOR: #1f497d">Boy that was *<B>fast</B>*!
</SPAN><SPAN style="COLOR: #1f497d; FONT-FAMILY: Wingdings">J</SPAN><SPAN
style="COLOR: #1f497d"></SPAN></P>
<P class=MsoNormal><SPAN style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN style="COLOR: #1f497d">Txs guys!</SPAN></P>
<P class=MsoNormal><SPAN style="COLOR: #1f497d"></SPAN> </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></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">Think of the case
when a vehicle is crossing a bridge… ;-)</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">Pedro.</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<DIV>
<DIV
style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: #b5c4df 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: medium none; PADDING-TOP: 3pt; BORDER-BOTTOM: medium none">
<P class=MsoNormal><B><SPAN lang=EN-US
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'">From:</SPAN></B><SPAN
lang=EN-US style="FONT-SIZE: 10pt; 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></P></DIV></DIV>
<P class=MsoNormal> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d">Add a where clause on
the indexed column (presumably f.geometry).</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </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></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>Rob.</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US style="COLOR: #1f497d"></SPAN> </P>
<DIV>
<DIV
style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0cm; BORDER-TOP: #b5c4df 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: medium none; PADDING-TOP: 3pt; BORDER-BOTTOM: medium none">
<P class=MsoNormal><B><SPAN lang=EN-US
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'">From:</SPAN></B><SPAN
lang=EN-US style="FONT-SIZE: 10pt; 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></P></DIV></DIV>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>Hi List,</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </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><SPAN
lang=EN-US></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>This is the query:</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>select freguesia,
distance(geomfromtext('POINT(-9.1533 38.69686)',4326),f.geometry) as thedist
</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US>from freguesias as f </SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US>order by thedist </SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US>limit 1;</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>as you can see it returns the closest
polygon to a given point.</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>Problem: 4050 (**detailed**)
polygons</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US>exec time: 907 ms</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>The Gist index was created, btw…</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>Is there any way to optimize this query and
avoid the dreaded seq scan?</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>Txs in advance!</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US>Pedro Doria Meunier.</SPAN></P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </P>
<P class=MsoNormal><SPAN lang=EN-US></SPAN> </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>