Question 987

Two Columns, One Circle

A restaurants table has separate B-tree indexes on lat and lng, and the query "everything within 2 km of this point" scans hundreds of thousands of rows before filtering down to 40. Why does adding a spatial index help?

A

R-trees only index polygons, so for point data the correct approach is to hash lat and lng together into a single key and index that key with a plain B-tree

B

Each B-tree prunes one dimension only, leaving a wide crossing strip of candidates, while an R-tree indexes the point as one two-dimensional key and prunes both together

C

A composite B-tree on (lat, lng) already solves this, because once the leading column is bounded to a latitude range the second column still narrows the scan the same way

D

A circular region cannot be indexed at all, so the fix is a precomputed distance column maintained on write, with an ordinary B-tree index built over that column