[postgis-users] ordering spatial data in tables
Vincent.Picavet at mediapost.fr
Wed Jan 7 01:50:05 PST 2009
> > 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.
> 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
CLUSTER reorders data on disk using a specified index, therefore
increasing access speed when reading rows in the index order.
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
Hope this helps.
More information about the postgis-users