<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=gb2312">
<META content="MSHTML 6.00.3790.3959" name=GENERATOR>
<STYLE>@font-face {
font-family: ËÎÌå;
}
@font-face {
font-family: Verdana;
}
@font-face {
font-family: @ËÎÌå;
}
@page Section1 {size: 595.3pt 841.9pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; layout-grid: 15.6pt; }
P.MsoNormal {
TEXT-JUSTIFY: inter-ideograph; FONT-SIZE: 10.5pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; TEXT-ALIGN: justify
}
LI.MsoNormal {
TEXT-JUSTIFY: inter-ideograph; FONT-SIZE: 10.5pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; TEXT-ALIGN: justify
}
DIV.MsoNormal {
TEXT-JUSTIFY: inter-ideograph; FONT-SIZE: 10.5pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; TEXT-ALIGN: justify
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
FONT-WEIGHT: normal; COLOR: windowtext; FONT-STYLE: normal; FONT-FAMILY: Verdana; TEXT-DECORATION: none; mso-style-type: personal-compose
}
DIV.Section1 {
page: Section1
}
BLOCKQUOTE {
MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 2em
}
OL {
MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
UL {
MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
</STYLE>
</HEAD>
<BODY>
<DIV><FONT face=Verdana color=#0000ff size=2></FONT>Thanks all </DIV>
<DIV>I got it</DIV>
<DIV> </DIV>
<DIV><FONT face=Verdana size=2>
<HR>
</FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><STRONG>From:</STRONG> Paul
Ramsey</FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT
size=2><STRONG>Sent:</STRONG> 2008-05-21 06:04:05</FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><STRONG>To:</STRONG> PostGIS Users
Discussion</FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT
size=2><STRONG>CC:</STRONG> </FONT></FONT></DIV>
<DIV><FONT face=Verdana><FONT size=2><STRONG>Subject:</STRONG> Re:
[postgis-users] 3D index in postgis</FONT></FONT></DIV>
<DIV><FONT face=Verdana size=2></FONT> </DIV>
<DIV><FONT face=Verdana size=2>
<DIV>Bah humbug, right you are :)</DIV>
<DIV> </DIV>
<DIV>P</DIV>
<DIV> </DIV>
<DIV>On Tue, May 20, 2008 at 12:31 PM, Kevin Neufeld</DIV>
<DIV><kneufeld@refractions.net > wrote:</DIV>
<DIV>> Paul Ramsey wrote:</DIV>
<DIV>> ></DIV>
<DIV>>
> On Tue, May 20, 2008 at 8:40 AM, Kevin Neufeld
<kneufeld@refractions.net ></DIV>
<DIV>> > wrote:</DIV>
<DIV>> > ></DIV>
<DIV>> > > a1001800 wrote:</DIV>
<DIV>> > > ></DIV>
<DIV>> > > > Thanks Kevin,</DIV>
<DIV>> > > ></DIV>
<DIV>> > >
> It looks like ~= not invoke the index.</DIV>
<DIV>> > ></DIV>
<DIV>> >
> That's why I suggested the && operator as well. It does use the index.</DIV>
<DIV>> > ></DIV>
<DIV>> > >
> Do we have a way to deal with an index with third value?</DIV>
<DIV>> > >
> For example, point (x, y) and userid</DIV>
<DIV>> > > ></DIV>
<DIV>> > >
> I need to do a query like "select point(x,y) from table where point in</DIV>
<DIV>> > > > rectangle and userid=xxx"</DIV>
<DIV>> > ></DIV>
<DIV>> >
> Sure. Add the userid=xxx to your filter list like you were doing.</DIV>
<DIV>> > > ie.</DIV>
<DIV>> > > SELECT ST_MakePoint(x,y)</DIV>
<DIV>> > > FROM mytable</DIV>
<DIV>> > > WHERE geom &&
<insert rectangle geom here ></DIV>
<DIV>> > > AND userid = xxx;</DIV>
<DIV>> > ></DIV>
<DIV>> >
> You can additionally add an ST_Contains() filter if you need to have</DIV>
<DIV>> >
> your points exactly inside the rectangle.</DIV>
<DIV>> ></DIV>
<DIV>>
> No, don't do that :) "point && rectangle" is logically the same as</DIV>
<DIV>>
> "st_contains(rectangle, point)" and somewhat cheaper.</DIV>
<DIV>> ></DIV>
<DIV>></DIV>
<DIV>> :) I disagree Paul. These are not logically the same - they are very close,</DIV>
<DIV>> but not the same. The bounding box coordinates are stored using 4 bytes</DIV>
<DIV>> instead of the 8 bytes used to hold the rectangle's actual coordinates. The</DIV>
<DIV>> bbox is rounded up to guarantee that the rectangle is contained entirely</DIV>
<DIV>> within it. So, you could have a point that is contained within the bounding</DIV>
<DIV>> box but not the rectangle.</DIV>
<DIV>></DIV>
<DIV>> Consider,</DIV>
<DIV>></DIV>
<DIV>> SELECT</DIV>
<DIV>> a.poly && b.point AS bbox,</DIV>
<DIV>> ST_Contains(a.poly, b.point)</DIV>
<DIV>> FROM</DIV>
<DIV>> (SELECT 'POLYGON((0 0, 0 1.0000001, 1 1.0000001, 1 0, 0 0))'::geometry AS</DIV>
<DIV>> poly) a,</DIV>
<DIV>> (SELECT 'POINT(0.5 1.00000011)'::geometry AS point) b;</DIV>
<DIV>></DIV>
<DIV>> bbox | st_contains</DIV>
<DIV>> ------+----------</DIV>
<DIV>> t | f</DIV>
<DIV>> (1 row)</DIV>
<DIV>></DIV>
<DIV>> -- Kevin</DIV>
<DIV>></DIV>
<DIV>>
> The key is knowing for 100% sure that your polygonal geometry will</DIV>
<DIV>>
> *always* be a rectangle. If there's a chance it won't, you need the</DIV>
<DIV>> > st_contains.</DIV>
<DIV>> ></DIV>
<DIV>> > P</DIV>
<DIV>></DIV>
<DIV>></DIV>
<DIV>> _______________________________________________</DIV>
<DIV>> postgis-users mailing list</DIV>
<DIV>> postgis-users@postgis.refractions.net</DIV>
<DIV>> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV>
<DIV>></DIV>
<DIV>_______________________________________________</DIV>
<DIV>postgis-users mailing list</DIV>
<DIV>postgis-users@postgis.refractions.net</DIV>
<DIV><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV></FONT></DIV></BODY></HTML>