BRIN indexing and cluster

# 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);