Ben, > I'm aggregating data from shapefiles into a table to cover a larger > region. > > 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. Remember Tobler's First Law of Geography (http://en.wikipedia.org/wiki/First_law_of_geography) says: "Everything is related to everything else, but near things are more related than distant things." This is a statement of spatial autocorrelation. Think of it this way, if you zoom in to a particular area then the objects in that area share the "same" location. If these objects are on the same blocks on disk then PostgreSQL will be able to read them efficiently. If they are not on the same bit of disk, the head has to jump all over the place to get the objects for the one logical query. Anything that can avoid that head jumping is important. (Part of the reason why we defragment our hard-drives.) 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); Hope this helps. regards Simon -- SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: [email protected] Voice: +613 9016 3910 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18" E) Latitude: -43.01530 (43° 00' 55" S) NAC:W80CK 7SWP3 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
