[postgis-users] Advice on space efficient grid indexing

[Arnaud L.]

Le 29/09/2016 à 10:11, Arnaud L. a écrit :
> What would be a more efficient way to store this cell coordinates so
> that disk usage would be limited and accessing the rows would be using
> the index efficiently ?
> Using a point geometry would not be space efficient I believe, and it
> seems overkill since we do not need to do any spatial operation on this
> column (only equality queries, i.e. retrieving all rows in a given cell).
> I also thought about a bigint array of two elements, but our database is
> mainly accessed through psqlodbc and I'm not sure that arrays are very
> efficient in this case.

I should also have pointed out that there is a third parameter, because 
we have two overlapping grids (but a given geomtry can only be in one of 
them). Sot it is more a 3 columns reference than a 2 columns one.
Also, the obvious choice of adding 3 columns (gridx, gridy, gridlevel) 
would be that last choice, even after the ugly "char" column. We have 
functions returning this "grid id", and it would be very inconvenient if 
they should return setof bigints or records.
I really like the idea that a grid cell has a unique "id" that can be 
easily computed, and that a geometry can be also easily retrieved from 
this id with having to write complex sql like (gridx, gridy, gridlevel) 
= (..., ..., ...).

Regards
--
Arnaud