<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Verdana
}
--></style>
</head>
<body class='hmmessage'>
Sorry for the late reply but away from all computers for the weekend.<BR>
<BR>
Anyway, thanks again for your help.<BR>
To answer your questions:<BR>
PostgreSQL version is 8.4.4 - PostGIS version 1.5<BR>
The original queries and explain statements are below:<BR>
Also included results from your suggested solutions which do improve runtimes.<BR>
<BR>
Query1 START********************:<BR>
SELECT * FROM table1 WHERE ST_DWithin(table1.the_geom,<BR>ST_SetSRID(ST_MakePoint(693230.940102042, 739076.954923132, 115.122), 2157),<BR>5) LIMIT 100;<BR><BR>
EXPLAIN PLAN:<BR>"Limit (cost=3406.44..340848.60 rows=1 width=394)"<BR>" -> Bitmap Heap Scan on table1 (cost=3406.44..340848.60 rows=1 width=394)"<BR>" Recheck Cond: (the_geom && '01030000206D08000001000000050000000B0E55E1D32725414CAFEBE8FF8D26410B0E55E1D32725414CAFEBE8138E26410B0E55E1E72725414CAFEBE8138E26410B0E55E1E72725414CAFEBE8FF8D26410B0E55E1D32725414CAFEBE8FF8D2641'::geometry)"<BR>" Filter: (('01010000A06D0800000B0E55E1DD2725414CAFEBE8098E26412B8716D9CEC75C40'::geometry && st_expand(the_geom, 5::double precision)) AND _st_dwithin(the_geom, '01010000A06D0800000B0E55E1DD2725414CAFEBE8098E26412B8716D9CEC75C40'::geometry, 5::double precision))"<BR>" -> Bitmap Index Scan on table1_ind_point (cost=0.00..3406.44 rows=101315 width=0)"<BR>" Index Cond: (the_geom && '01030000206D08000001000000050000000B0E55E1D32725414CAFEBE8FF8D26410B0E55E1D32725414CAFEBE8138E26410B0E55E1E72725414CAFEBE8138E26410B0E55E1E72725414CAFEBE8FF8D26410B0E55E1D32725414CAFEBE8FF8D2641'::geometry)"<BR>Query1 END*********************:<BR>
<BR>
<BR>
<BR>
<BR>
Query2 START*******************:<BR>
SELECT * FROM table1 WHERE ST_DWithin(table1.the_geom, (SELECT<BR>table2.the_geom FROM table2 WHERE id = 356), 5) LIMIT 100;<BR><BR>
EXPLAIN PLAN:<BR>
"Limit (cost=8.27..102.44 rows=100 width=394)"<BR>" InitPlan 1 (returns $0)"<BR>" -> Index Scan using table2_pkey on table2 (cost=0.00..8.27 rows=1 width=32)"<BR>" Index Cond: (id = 356)"<BR>" -> Seq Scan on table1 (cost=0.00..7632345.80 rows=8104993 width=394)"<BR>" Filter: st_dwithin(the_geom, $0, 5::double precision)"<BR>
Query2 END**********************:<BR>
<BR>
Query2_Improved START************:<BR>
This solution produces the same results as Query1 and Query2, it does use the spatial index and is faster. Query2_Improved = 362ms; Query2 = 4624ms; but is slower than Query1 = 253ms which is understandable as Query1 does not have the table2 overhead.<BR>
SELECT * FROM table1 INNER JOIN (SELECT table2.the_geom FROM table2 WHERE id<BR>= 356) AS t2<BR> ON ST_DWithin(table1.the_geom, t2.the_geom, 5) <BR>LIMIT 100;<BR><BR>
EXPLAIN PLAN:<BR>
"Limit (cost=0.00..541.93 rows=1 width=426)"<BR>" -> Nested Loop (cost=0.00..541.93 rows=1 width=426)"<BR>" Join Filter: ((table2.the_geom && st_expand(table1.the_geom, 5::double precision)) AND _st_dwithin(table1.the_geom, table2.the_geom, 5::double precision))"<BR>" -> Index Scan using table2_pkey on table2 (cost=0.00..8.27 rows=1 width=32)"<BR>" Index Cond: (id = 356)"<BR>" -> Index Scan using table1_ind_point on table1 (cost=0.00..500.72 rows=122 width=394)"<BR>" Index Cond: (table1.the_geom && st_expand(table2.the_geom, 5::double precision))"<BR>
Query2_Improved END**************:<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
Query3 START********************:<BR>SELECT tb1.id, tb1.the_geom <BR> FROM table1 AS tb1<BR> WHERE ST_Within(tb1.the_geom, (<BR> SELECT ST_SetSRID(CAST(ST_MakeBox3D(ST_Translate((<BR> SELECT tb2.the_geom FROM table2 AS tb2 WHERE tb2.id = 356<BR> ), -5, -4, -2)<BR> , ST_Translate((<BR> SELECT tb3.the_geom FROM table2 AS tb3 WHERE tb3.id = 356<BR> ), 5, 4, 2)) AS geometry), 2157)<BR> )<BR>));<BR><BR>
EXPLAIN PLAN:<BR>
"Seq Scan on table1 tb1 (cost=16.57..7632362.37 rows=8104993 width=136)"<BR>" Filter: st_within(the_geom, $2)"<BR>" InitPlan 3 (returns $2)"<BR>" -> Result (cost=16.55..16.57 rows=1 width=0)"<BR>" InitPlan 1 (returns $0)"<BR>" -> Index Scan using table2_pkey on table2 tb2 (cost=0.00..8.27 rows=1 width=32)"<BR>" Index Cond: (id = 356)"<BR>" InitPlan 2 (returns $1)"<BR>" -> Index Scan using table2_pkey on table2 tb3 (cost=0.00..8.27 rows=1 width=32)"<BR>" Index Cond: (id = 356)"<BR>
Query3 END**********************:<BR>
<BR>
Query3_Improved START************:<BR>
This solution produces a much faster result, same output results as Query3 and does us the spatial index. Query3 = 435794ms; Query3_Improved = 9452ms, huge improvement so thanks again.<BR>SELECT tb1.id, tb1.the_geom <BR> FROM table1 AS tb1 INNER JOIN<BR> (SELECT ST_SetSRID(CAST(ST_MakeBox3D(ST_Translate(tb2.the_geom, -5, -4,<BR>-2)), ST_Translate(tb2.the_geom, 5,4,2)) As geometry), 2157) As bgeom FROM<BR>table2 As tb2<BR> WHERE tb2.id = 356) As t2<BR> ON ST_Within(tb1.the_geom, t2.bgeom);<BR> <BR>EXPLAIN PLAN:<BR>
"Nested Loop (cost=0.01..544.08 rows=41 width=136)"<BR>" Join Filter: _st_within(tb1.the_geom, st_setsrid((st_makebox3d(affine(tb2.the_geom, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, (-5)::double precision, (-4)::double precision, (-2)::double precision), affine(tb2.the_geom, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 5::double precision, 4::double precision, 2::double precision)))::geometry, 2157))"<BR>" -> Index Scan using table2_pkey on table2 tb2 (cost=0.00..8.27 rows=1 width=32)"<BR>" Index Cond: (id = 356)"<BR>" -> Index Scan using table1_ind_point on table1 tb1 (cost=0.01..500.73 rows=122 width=136)"<BR>" Index Cond: (tb1.the_geom && st_setsrid((st_makebox3d(affine(tb2.the_geom, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, (-5)::double precision, (-4)::double precision, (-2)::double precision), affine(tb2.the_geom, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 0::double precision, 0::double precision, 0::double precision, 1::double precision, 5::double precision, 4::double precision, 2::double precision)))::geometry, 2157))"<BR>
Query3_Improved END***************:<BR>
<BR>
Finally and just to clarify, you say that the Z check is not performed in the ST_Within and ST_DWithin cases. Thus my result set only contains points within the X and Y plane boundaries of the Box3D where some of them could be outside in the Z plane!!! If this is correct can you suggest a solution in PostGIS SQL terms or will postprocessing the results in a seperate manner be required? Will 3D awaremess of situations like this be included in future PostGIS versions.<BR>
<BR>
Anyway thanks again,<BR>
<BR>
Paul<BR> <br /><hr />Hotmail: Trusted email with Microsoft’s powerful SPAM protection. <a href='https://signup.live.com/signup.aspx?id=60969' target='_new'>Sign up now.</a></body>
</html>