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

[Simon Greener]

Oops. 

Much faster to do this:

drop table tmp_gis_roads;
create table tmp_gis_roads as 
SELECT * 
  FROM gis_roads a 
 WHERE a.the_geom && (SELECT ST_Force_2d(ST_SetSRID(ST_GEOMETRY(ST_EXTENT(b.THE_GEOM)),
                                                    (SELECT ST_SRID(c.the_geom) 
                                                       FROM gis_roads c LIMIT 1))) 
                        FROM gis_roads b);

regards
SImon
On Thu, 08 Jan 2009 10:26:04 +1100, Simon Greener <simon at spatialdbadvisor.com> wrote:

> 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