Search⌘ K
Home
Courses
The Art of PostgreSQL

Geolocating the Nearest Pub

Explore how to implement a k-nearest neighbor search in PostgreSQL to locate nearby pubs by their geographical coordinates. Understand the use of the point data type, distance operator, and GiST indexing to optimize query performance on large datasets. This lesson guides you through practical SQL examples for accurate and efficient geospatial queries.

We'll cover the following...

kNN search

To implement a kNN search in PostgreSQL, we need to order the result set with a distance operator, written <->. Here’s the full SQL for searching the pubs near a known position:

PostgreSQL
  select id, name, pos
    from pubnames
order by pos <-> point(-0.12,51.516)
   limit 3;

With this geolocation, we obtain the following nearby pubs:

    id     │          name          │           pos           
═══════════╪════════════════════════╪═════════════════════════
  21593238 │ All Bar One            │ (-0.1192746,51.5163499)
  26848690 │ The Shakespeare's Head │ (-0.1194731,51.5167871)
 371049718 │ The Newton Arms        │ (-0.1209811,51.5163032)
(3 rows)

The ...