[postgis-devel] Several R-Trees

[Chris Hodgson]

This is more of a question for the postgis-users list but I'm not on it 
so I'll just answer here.

Firstly, I believe it is the && operator you want, I'm not sure what the 
& operator does on geometries...

Secondly, it doesn't make sense for both indexes to be used. To answer 
your query, the database must look at every city (table scan) and check 
to see if there is a river nearby (spatial index scan). This works 
exactly the same as any join - this IS how a spatial join works. You 
only need a spatial index on the table that you are querying spatially.

You're query is exactly correct for what you are trying to do, use the 
spatial index to find near matches and then the intersects() function to 
find exact matches. However, you will likely find that the distance() 
function is much faster than intersects(), so if you write your query as 
this:

SELECT COUNT(*)
FROM rivers2000, cities2000
WHERE rivers2000.geometry && cities2000.geometries
AND distance( rivers2000.geometry, cities2000.geometries ) = 0;

it will probably be noticeably faster.

Hope that answers your questions.

Chris


Leticia wrote:
> Hello!
> 
>  
> 
> I have two tables, each one with a geometry column.
> 
>  
> 
> I have built two R-Tree indexes. i.e.
> 
>  
> 
>  
> 
> Create index  idxrivers2000 on rivers2000 using gist (geometry   
> gist_geometry_ops)                    
> 
>  
> 
> Create index  idxcities2000 on cities2000 using gist (geometry 
>   gist_geometry_ops)                       
> 
>  
> 
>  
> 
>  
> 
> Now, I need to make spatial join queries like this “how many cities are 
> crossed by rivers?”
> 
>  
> 
> SELECT COUNT(*)
> 
> FROM rivers2000, cities2000
> 
> WHERE  rivers2000.geometry & cities2000.geometries  AND 
>  interescts(rivers2000.geometry,cities2000.geometries  )
> 
>  
> 
> 1)     The use of the “&” operator is to force the R-tree using in the 
> query plan. If I do not use it, the index will not be chosen. Am I ok?
> 
>  
> 
> 2)     If I only use the subexpression “WHERE rivers2000.geometry & 
> cities2000.geometries” it only use MBRs and It is not correct, as I need 
> not only to use FIRST FILTER, but obtain and exact result.
> 
>  
> 
> 3)     When I look the query plan, I see that it uses only one of the 
> indexes (INDEXSCAN on one of them), and then use a lookup table over the 
> other table (SCAN TABLE over the other table). Is it possible to force 
> Postgress/Postgis to use a Spatial Join (use the two indexes instead of 
> using a table scan) ? How?
> 
>  
> 
> Thanks in advance,
> 
> Leticia
> 
>  
> 
>  
> 
>  
> 
>  
> 
>  
> 
>  
> 
>  
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel