CODEWITHSUNDEEP
sqlpostgresqlgispostgresql-performance
DonutGaz
DonutGaz

Reputation: 1542

Most efficient way to query for lat-long rectangle in SQL

I'm currently making consistent queries for a block of land within a given latitude, longitude rectangle. The coordinates are stored as individual double precision values. I've created a single index of both columns, so the current query containing 15240 tiles takes .10 seconds on my local machine.

At the moment, there's 23 million rows in the table, but there's going to be around 800 million upon completion of the table, so I expect this query time to get much slower.

Here's the query I'm running, with example values:

SELECT * FROM territories
WHERE nwlat < 47.606977 and nwlat > 47.506977
and   nwlng < -122.232991 and nwlng > -122.338991;

Is there a more efficient way of doing this? I'm fairly new to large databases, so any help is appreciated. FYI, I'm using PostgreSQL.

Upvotes: 3

Views: 1601

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656814

It would be much more efficient with a GiST or an SP-GiST index and a "box-contains-points" query ...

GiST index

The index is on a box with zero area, built from the same point (point(nwlat, nwlng)) twice.

There is a related code example in the manual for CREATE INDEX.

CREATE INDEX territories_box_gist_idx ON territories
USING gist (box(point(nwlat, nwlng), point(nwlat, nwlng)));

Query with the "overlaps" operator &&:

SELECT *
FROM   territories
WHERE  box(point(nwlat, nwlng), point(nwlat, nwlng))
    && '(47.606977, -122.232991), (47.506977, -122.338991)'::box;

SP-GiST index

Smaller index on just points:

CREATE INDEX territories_box_spgist_idx ON territories
USING spgist (point(nwlat, nwlng));

Query with the contains operator @>:

SELECT *
FROM   point
WHERE  '(47.606977, -122.232991), (47.506977, -122.338991)'::box
    @> point(nwlat, nwlng);

I get fastest results for the SP-GiST index in a simple test with 1M rows on Postgres 9.6.1.

For more sophisticated needs consider the PostGIS extension.

Upvotes: 4

Related Questions