The Art of PostgreSQL/

...

/

The Most Popular Pub Names

The Most Popular Pub Names

Learn more about querying for the highest count and popular pub names.

We'll cover the following...

Use case: Find the highest count of pubs

Let’s now find out which cities have the highest count of pubs, considering that a pub is affiliated with a city if it’s within five miles of the single point we have set as the city location in our dataset.

Press + to interact
  select c.name, count(cp)
    from cities c, lateral (select name
                              from pubnames p
                             where (p.pos <@> c.pos) < 5) as cp
group by c.name
order by count(cp) desc
   limit 10;

We use that method of associating pubs and cities because within the data we exported from Open Street Map, the only information we have is a single point to represent a city. So our method amounts to drawing a five-mile circle around that point and then considering anything that’s inside the circle to be part of the town.

    name     │ count 
═════════════╪═══════
 London      │  1388
 Westminster │
...