[postgis-users] Re: [postgis-devel] Several R-Trees

[Mark Cave-Ayland]

On Thu, 2007-03-29 at 09:51 -0300, 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


Hi Leticia,

I think you may be using the wrong operator - PostGIS requires the "&&"
operator to make use of the spatial indices, e.g.


SELECT COUNT(*)
FROM rivers2000, cities2000
WHERE  rivers2000.geometry && cities2000.geometries  AND
interescts(rivers2000.geometry,cities2000.geometries)


(I have no idea what effect the "&" operator would have on geometry
types)

Also, assuming you are using PostgreSQL >= 8.0, you need to issue a
VACUUM ANALYZE after creating the indices so that PostGIS can build up
the statistics it needs to correctly use them.

BTW, you don't need to post to postgis-devel since the postgis-devel
list is for PostGIS developer discussion - postgis-users is the correct
list for PostGIS application development questions.


HTH,

Mark.