[postgis-users] ordering spatial data in tables

[Simon Greener]

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: simon at spatialdbadvisor.com
  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