[postgis-users] Partitioning spatial table

[Arnaud Lesauvage]

Hi List !

I have to build a database to hold a very large spatial dataset.
(all the roads of a country, ~5.000.000 MULTILINESTRINGS).
I think this would make queries too slow, so I would like to 
implement partitioning.
The document 
http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html
talks about Constraint Exclusion, and it sounds like a very good 
idea to me.

I have two options right now :
-build one child table for every road-class in the master table 
(~10 different classes). Many queries are based on this road-class 
field.
-build one child table for every administrative area in the master 
table (~100 administrative areas).

The first case would be easy to implement (just a check constraint 
on the road-class), but there would still be some big tables (some 
road-classes have a lot more element than others).
The second case seems better (more partitioning, smaller 
partitions, roughlu the same number of element per partition), but 
I believe it would be quite hard to implement.
I could use a constraint like CHECK ( Within(this_geometry, 
AdministrativeAreaGeometry) ), but then querying country-wide 
would be quite difficult...

At the present time, I have just one huge table with a BTree index 
on the road-class and a gist index on the spatial column, but 
simple queries like
SELECT my_geom FROM theTable WHERE roadclass=2 AND (somebox_geom 
&& my_geom)
take ages to run...

Do you have any better idea on how to implement this ?

Thanks a lot for your help !

Regards
--
Arnaud