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.

  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 │
...