PostgreSQL GIST

www.postgresql.org/docs/15/gist.html
www.postgresql.org/docs/15/datatype-geometric.html
medium.com/postgres-professional/indexes-in-postgresql-5-gist-86e19781b5db the only example on the net!

The highly underdocumented built-in module, that supports SQL spatial index and a lot more.

Quite horrendous as it only seems to work on geometric types and not existing columns. But why.

And it uses custom operatores, where standard operators would have been just fine for points...

Minimal runnable example with points:

set -x
time psql -c 'drop table if exists t'
time psql -c 'create table t(p point)'
time psql -c "insert into t select (point ('(' || generate_series || ',' || generate_series || ')')) from generate_series(1, 10000000)"
time psql -c 'create index on t using gist(p)'
time psql -c "select count(*) from t where p <@ box '(1000000,1000000),(9000000,2000000)'"

The index creation unfortunately took 100s, so it will not scale to 1B points very well whic his a shame.

Some sources about it:

stackoverflow.com/questions/28292198/how-to-port-simple-spatial-index-using-sqlite-r-trees-to-postgres

PostgreSQL GIST

 Ancestors (13)