The Art of PostgreSQL/

...

/

Geolocating the Nearest Pub

Geolocating the Nearest Pub

Learn about kNN search and indexing in kNN search.

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:

Press + to interact
  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 ...