[postgis-users] Spatial Index Workings

[Paul & Caroline Lewis]

Hi,

  First off, thank you for your help with the St_Expands issues, your solution is what I need, however I'm not sure if I understand it properly or if I'm implementing it correctly.

  Therefore, my question relates to how postGIS SQL should be set up to ensure the spatial index is used.

 

To start could someone explain what is wrong/the-difference between how the following two queries operate.

 

Query1: SELECT * FROM table1 WHERE ST_DWithin(table1.the_geom, ST_SetSRID(ST_MakePoint(693230.940102042, 739076.954923132, 115.122), 2157), 5)  LIMIT 100;


Query2: SELECT * FROM table1 WHERE ST_DWithin(table1.the_geom, (SELECT table2.the_geom FROM table2 WHERE id = 356), 5)  LIMIT 100;

 

Both queries produce the same results, however the first uses the spatial index while the second doesn't, according to the pgAdmin3 explain tool. The first query takes 1432ms while the second takes 141239ms and there are 24million+ geometries in table1. The point defined in Query1 is taken from id 359 of table2, as is used in the nested select statement of Query2, so both queries are using the exact same geometry point as the second argument of the DWithin function. I obviously am not understanding the SQL properly to ensure that the correct data type is used to force the spatial index to be used as the performance difference is very significant.

 

In relation to this and in particular to the previous St_Expands solution my SQL is as follows:

Query3:

SELECT tb1.id, tb1.the_geom 
 FROM table1 AS tb1
 WHERE ST_Within(tb1.the_geom, (
  SELECT ST_SetSRID(CAST(ST_MakeBox3D(ST_Translate((
   SELECT tb2.the_geom FROM table2 AS tb2 WHERE tb2.id = 356
   ), -5, -4, -2)
   , ST_Translate((
   SELECT tb3.the_geom FROM table2 AS tb3 WHERE tb3.id = 356
   ), 5, 4, 2)) AS geometry), 2157)
 )
));

 

This takes about 3 minutes to run but I'm sure I've got lots of performance gain that can be made here as the pgAdmin3 explain tool shows no spatial index is used at all when this query is run.

So, any help would be much appreciated in any form, particularly in helping me understand what I'm doing wrong that causes the spatial index to be ignored.

 

Thanks in advance,

 

Paul
 		 	   		  
_________________________________________________________________
Hotmail: Free, trusted and rich email service.
https://signup.live.com/signup.aspx?id=60969
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100611/0218fe13/attachment.html>