[postgis-users] ordering spatial data in tables

[Picavet Vincent]

Hi,

> > My question is, is there any benefit to be gained by 
> ordering the data 
> > by some sort of spatial representation, ie centroid when retrieving 
> > the data? Or does the creation of the GiST index obviate 
> the need for such a thing.
> 
> The spatial index is vital for select performance. 
Agree.

> Personally, if the data is read only data sourced externally, 
> I would import and store the data in some sort of spatial order. 
[...]
> So, import the data (into a temporary table) and then 
> consider sorting the data using some sort of spatial key. A 
> centroid should be OK but consider using a larger bucket such 
> as a gridded area.
> 
> create table gis_roads as select * from tmp_gis_roads order 
> by centroid(the_geom);

Another and more <classic> solution (despite slower on big tables) to
keep spatially close rows together on disk is to use postgresql CLUSTER
statement.
http://www.postgresql.org/docs/8.3/static/sql-cluster.html

CLUSTER reorders data on disk using a specified index, therefore
increasing access speed when reading rows in the index order.

Example :
CREATE INDEX idx_mytable_the_geom ON mytable USING GIST(the_geom);
CLUSTER mytable USING idx_mytable_the_geom;

Efficiency of this method can clearly be seen for example when using
postgis with mapserver to generate a map, since close geometry are drawn
together, and therefore read together in the database.

Note that if you significally update or insert / delete data from your
table, you'll have to re-cluster it.

All other informations are detailled in postgresql documentation cited
above.

Hope this helps.
Vincent