Skip to content

Instantly share code, notes, and snippets.

@giohappy
Created November 3, 2017 15:39
Show Gist options
  • Save giohappy/5b77d9a70c6b9845ea5a09372e18eccf to your computer and use it in GitHub Desktop.
Save giohappy/5b77d9a70c6b9845ea5a09372e18eccf to your computer and use it in GitHub Desktop.
# data test for PostGIS BRIN Indexes
# a small dataset ( ~400K rows for roads)
wget http://download.geofabrik.de/europe/france/bretagne-latest.shp.zip
unzip bretagne-latest.shp.zip
shp2pgsql -s 4326:2154 -I landuse.shp landuse | psql -h localhost -p 32770 -U pggis -d pggis
shp2pgsql -s 4326:2154 -I natural.shp natural | psql -h localhost -p 32770 -U pggis -d pggis
shp2pgsql -s 4326:2154 -I places.shp places | psql -h localhost -p 32770 -U pggis -d pggis
shp2pgsql -s 4326:2154 -I points.shp points | psql -h localhost -p 32770 -U pggis -d pggis
shp2pgsql -s 4326:2154 -I railways.shp railways | psql -h localhost -p 32770 -U pggis -d pggis
shp2pgsql -s 4326:2154 -I roads.shp roads | psql -h localhost -p 32770 -U pggis -d pggis
shp2pgsql -s 4326:2154 -I waterways.shp waterways | psql -h localhost -p 32770 -U pggis -d pggis
# Work on roads
# get a road table clustered on geography
create table roads_dense as select * from roads order by (st_geohash(st_transform(geom, 4326, 10));
# We can also do it with cluster :
# first create an index on geohash
# create index idx_roads_geohash_geom on roads (st_geohash(st_transform(geom, 4326, 10));
# Then cluster the table on it
# cluster roads using idx_roads_geohash_geom;
# Get a totally unordered table
create table roads_random as select * from roads order by random();
# Now we can create a brin index on the tables and compare results for an intersection query
# create indexes and all
# TODO
# Now query
select
*
from
roads_dense
where
st_intersects(
st_setsrid(
st_geomfromewkt(
'Polygon ((166731.58908349758712575 6790991.81909713987261057, 169987.24971000739606097 6791081.67809532396495342, 170035.63532441412098706 6790190.0003441134467721, 166773.06246727477991953 6790107.05357655975967646, 166731.58908349758712575 6790991.81909713987261057))'
), 2154)
, geom);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment