[postgis-users] Several R-Trees

[Leticia]

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

 

 

 

 

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070329/fe32533d/attachment.html>