Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I wonder if there could be a performance gain to re organize the rows in a table by Geohash and then CLUSTER this table on the Geohash index of the geometries.

CREATE TEMPORARY TABLE tmptbl AS
SELECT *, (st_geohash(st_transform(geom,4326),10))as geohash
FROM mytable  ORDER BY geohash;

DELETE FROM  mytable;

ALTER TABLE tmptbl DROP COLUMN geohash;

INSERT INTO mytable SELECT * FROM tmptbl;

DROP INDEX mytable _geom_gist;

CREATE INDEX mytable _geom_gist
ON mytable USING gist(geom);

CREATE INDEX mytable_geom_geohash
ON mytable  (st_geohash(st_transform(geom,4326),10));

CLUSTER mytable USING mytable_geom_geohash;

Thanks

share|improve this question
    
The most important thing your question is missing is how will you be using the database. Most performance tweaks make some thing perform better but other things perform worse. –  Jakub Kania May 15 '13 at 18:28
    
For me, spatial index speeds up spatial request and geohash ordering speeds up sequential read of the table for a given zoom. –  aurel_nc May 16 '13 at 0:28
    
I'm interested in increasing reactivity of my clients app (QGIS and GEOSERVER). –  aurel_nc May 16 '13 at 0:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.