The Art of PostgreSQL/

...

/

Pubs and Cities

Pubs and Cities

Learn how to find pubs in faraway cities.

We'll cover the following...

Use case: Find the farthest pub

Just as easily as we found the nearest pubs, we can also, of course, query for the pubs that are farthest away from any location.

Press + to interact
  select name, round((pos <@> point(-0.12,51.516))::numeric, 3) as miles
    from pubnames
order by pos <-> point(-0.12,51.516) desc
   limit 5;

It’s unclear how useful that particular query would be. That said, it shows that the kNN search supports the ORDER BY DESC variant:

      name       │  miles  
═════════════════╪═════════
 Tig Bhric       │ 440.194
 TP's            │ 439.779
 Begley's        │ 439.752
 Ventry Inn      │ 438.962
 Fisherman's Bar │ 439.153
(5 rows)

Pubs and the cities

Now we want to know what city those pubs are in, right? With the following URL and using the Open Street Map APIs, we can download a list of cities in the same area where the pub names were fetched:

http://www.overpass-api.de/api/xapi?*[place=city][bbox=-10.5,49.78,1.78,59]

Tweaking the parser and import code at https://github.com/dimitri/pubnames was easy and allowed ...