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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
