Which index configuration best supports efficient geographic proximity queries on lat/lon in an address database?

Which index configuration best supports efficient geographic proximity queries on lat/lon in an address database?

• A. B-tree index on postal_code
• B. Spatial index on lat/lon
• C. Full-text index on address_line1
• D. No index

Answer: (B)

To efficiently handle geographic proximity queries on lat/lon, you need a data structure that understands two-dimensional space. A spatial index on the location field uses specialized structures (like R-trees or GiST) to organize points by their spatial regions. This lets the database quickly prune large portions of data that are outside the search area, so it only computes distances for a small, relevant subset. That makes queries like “find all addresses within X miles” or “find the nearest address to this point” fast. Using a B-tree on postal_code doesn’t capture distance relationships; many points share the same postal code, and even nearby addresses can have different codes, so the index won’t reliably accelerate proximity tests. A full-text index on address_line1 is designed for textual matching, not for location, and won’t help with distance calculations. No index would mean scanning the entire table, which becomes impractical as the dataset grows. So, a spatial index on lat/lon best supports efficient geographic proximity queries.

To efficiently handle geographic proximity queries on lat/lon, you need a data structure that understands two-dimensional space. A spatial index on the location field uses specialized structures (like R-trees or GiST) to organize points by their spatial regions. This lets the database quickly prune large portions of data that are outside the search area, so it only computes distances for a small, relevant subset. That makes queries like “find all addresses within X miles” or “find the nearest address to this point” fast.

Using a B-tree on postal_code doesn’t capture distance relationships; many points share the same postal code, and even nearby addresses can have different codes, so the index won’t reliably accelerate proximity tests. A full-text index on address_line1 is designed for textual matching, not for location, and won’t help with distance calculations. No index would mean scanning the entire table, which becomes impractical as the dataset grows.

So, a spatial index on lat/lon best supports efficient geographic proximity queries.