[postgis-users] ordering spatial data in tables - WARNING! BUG?

[Simon Greener]

Given the 8.3.5 bug in CLUSTER for GiST indexes, in the meantime, what if Ben loaded his data, spatially indexed it and then did this:

create table gis_roads 
as 
select * 
  from tmp_gis_roads a 
 where a.the_geom && ST_Envelope( (SELECT ST_Collect(Force_2d(b.the_geom)) 
                                                                   FROM tmp_gis_roads b);

That should, at least, order the data being written to disk via the spatial index and give him the performance boost he is after.

regards
S
On Thu, 08 Jan 2009 09:17:11 +1100, Kevin Neufeld <kneufeld at refractions.net> wrote:

> This is a known PostgreSQL 8.3.5 bug (#4567).
>
> http://archives.postgresql.org/pgsql-hackers/2008-12/msg00278.php
> http://archives.postgresql.org/pgsql-bugs/2008-12/msg00037.php
>
> It looks like the there's fix for it as well.
>
> Cheers,
> Kevin
>
>
> Ben Madin wrote:
>> G'day Vincent,
>>
>> This didn't work as anticipated - in fact it appears to have truncated
>> the table!
>>
>> On 08/01/2009, at 5:01 AM, postgis-users-request at postgis.refractions.net
>> <mailto:postgis-users-request at postgis.refractions.net> wrote:
>>
>>> 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;
>>
>> CLUSTER works on a normal index :
>>
>> # select count(*) from gis_roads;
>>  count
>> -------
>>  89618
>> (1 row)
>>
>> # CLUSTER gis_roads USING gis_roads_gid_key;
>>
>> CLUSTER
>> # select count(*) from gis_roads;
>>  count
>> -------
>>  89618
>> (1 row)
>>
>> all good!
>>
>> But when I try on the gist index
>>
>>
>> # CLUSTER gis_roads USING gis_roads_gist;
>> CLUSTER
>>
>> # select count(*) from gis_roads;
>>  count
>> -------
>>      0
>> (1 row)
>>
>> whoops!
>>
>> Is this something I missed? Is this a PostgreSQL bug... surely the
>> cluster command shouldn't be allowed to destroy a whole table.
>>
>> cheers
>>
>> Ben
>>
>>
>>
>>
>>
>> --
>>
>> Ben Madin
>> REMOTE INFORMATION
>>
>> t : +61 8 9192 5455
>> f : +61 8 9192 5535
>> m : 0448 887 220
>> Broome   WA   6725
>>
>> ben at remoteinformation.com.au <mailto:ben at remoteinformation.com.au>
>>
>>
>>
>> Out here, it pays to know...
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 



-- 
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