Listing 1.6 under section 1.4.4 (page 26 of the PDF) includes a join on "r.franchise = f.franchise_code" and makes two references to f.franchise_name. However, the lu_franchise table is set up in listing 1.2 to use the column names "id" and "franchise." (And as I saw mentioned here on the forum already, ch01.roads should be ch01.highways.)
So the correct query appears to be:
SELECT f.franchise
, COUNT(DISTINCT r.id) As total
FROM ch01.restaurants As r
INNER JOIN ch01.lu_franchises As f ON r.franchise = f.id
INNER JOIN ch01.highways As h ON ST_DWithin(r.geom, h.geom, 1609)
GROUP BY f.franchise
ORDER BY total DESC;
|