Finding the City Based On What's Open Now
What's a city? There are lots of definitions. A Country of Cities thinks it's a place with 30+ dwellings per acre. Some new urbanists think the answer is a "15 minute city" where a person has most of what they need (food, school, park, etc.) within a 15 minute walk or short transit ride. My rural dwelling relatives might think about a traffic jam and muggings. When I was a kid I thought it was all about tall buildings. But now when I think about a city, the thing that makes it unique is people. Visible people. It's a place with a lot of visible people.
But how do you find these people? I found the existing solutions frustrating, so I decided to use my newfound GIS skills to play with a new proxy: where is open now? I came up with a map of places that I can consistently go and find visible people (demo here, screenshot below):
Similar Work: Areas of Interest in Google Maps
In 2016, Google Maps launched a new feature to show areas of interest. You've seen these areas before - it's the orange-ish area that is the background of places that have a lot of stores and places to eat. This feature is a good place to start exploring a new city, but still doesn't help me understand where I want to walk around right now.
The feature has a few main flaws:
- It shows the same shading at all times of the day. This might be accurate for 24/7 places like Times Square, but is not accurate for most places where Americans live. Time of day clearly matters.
- It thinks of parking as interesting space. For example, a nearby mall's expansive parking lots show up as "areas of interest". In reality this mall has a few interesting places to walk surrounded by massive big-box stores with few entrances and dead parking lots.
- It does not consistently identify less advantaged areas as "interesting" (at least according to Bloomberg when the feature launched). We should have a way of finding people regardless of whether we think we'll like them.
Perhaps this is a fine way for people to think about where they might want to drive and walk around, but it's not a useful way for me to figure out where I should walk around now.
Using Open Establishments to Figure Out Where People Are
But what if we could measure street-level energy by simply looking at what’s open?
I think that establishments are a good indicator of human-scale, street-level activity. These restaurants, cafes, bars, libraries, museums, art galleries, etc. interact with the urban form around them, shaping it as they are shaped by it.
I hypothesize that:
- when there are a lot of people in an area, establishments will (try to) open to serve them
- when an area no longer has people, establishments will close because there are no customers
- when there are multiple open establishments with their entrances really close they will generate street traffic as people walk between them
- concentrations of establishments draw more people to them
This will break down for some areas:
- parks and large public spaces (like a town square) can draw lots of people, but can be so large in time and space that it's hard to understand which parts are interesting at a given time
- schools, religious establishments, stadiums or conference centers can generate a lot of traffic at certain times (e.g. when school lets out), but are unlikely to be interesting to a passerby not involved in that institution
But even with these limitations, perhaps looking at open hours will be more useful?
Comparing Open Areas to Google Maps
I've found that making something visible can help us explain what we love (or hate) about it, so I started with my home area of Berkeley, CA to see how this could work.
We can compare this "open now" visualization to Google Maps.
To me, the lower map better represents Berkeley:
- there are four main commercial avenues with lots of businesses
- each of these avenues has little dead spots in the middle - there is no continuous commercial area that's pleasant to walk along
- there are little pockets of businesses spread throughout the middle meaning most Berkeley residents are a short walk from a pedestrian area
- there are no concentrations of businesses in the hills (the top right corner where all the roads are not straight)
My visualization is even more accurate when we see Berkeley at 11 PM; almost all of the streets are quiet.
So how did I build it? Read on...
How I Built It (the fun part)
One: Get the Data
Just like cities, which establishment is where and open when is constantly changing. Big players like Google or Yelp spend a lot of money to have this accurate and current point of interest (POI) data. This data is also useful, so they charge for it.
The best free POI data with open hours is on OpenStreetMap (OSM). POI only tends to be accurate and complete in areas where there are dedicated volunteers mapping the area. Using OSM data means that I will only find open businesses in areas that people have ambitiously mapped. Probably this is okay - I'd expect more people are more interested in mapping areas that are fun to walk around.
The incompleteness of the data also risks under-representing communities. The main barrier to editing OSM data is (a) knowing that OSM exists, and (b) spending the time to read the documentation and learn how. Anyone with time and a smartphone (or library computer) can improve their neighborhood and its representation on a map like this.
Once that was loaded (details below), I could play with my algorithm.
Two: Find what's open
As a sample of our analysis, consider an example of a very local shopping area near Berkeley's Monterey Market.
In 2025 Google does not show this as a "Area of Interest" when zoomed out, but on a Saturday it's a hopping place to see lots of other people.
We take the raw map data provided by OSM and overlay with a red dot the businesses that are open at 12:15 PM on a Saturday (look closely; the red dots are subtle):
We see that there are a lot of small businesses open next to each other near the Hopkins Bakery, then there is the Espresso Roma across the street, and Monterey Market across the other street. We see that the Hopkins Laundrette and Berkeley Horticultural Nursery have no dots - they are in fact open, but we don't have data about their open hours 🤷♂️.
Three: Visualize the social gravity
I imagine each open place generates a little gravity field—about 100 ft of liveliness around it:
We see that the small businesses along Hopkins form some overlapping circles that all connect into one blob. However, Monterey Market and Espresso Roma are just a bit too far way to really be in that blob.
This matches my experience on the ground: combined with 40' wide street, the parking of Monterey Market and Roma Espresso's front garden prevent them from feeling connected to the busy street life on that southwestern section of Hopkins.
Four: Cluster overlapping circles
We can then take these intersecting circles and join them together:
We now see three blobs of open businesses: one with those businesses on the Southwest corner of Hopkins, one for Monterey Market, and one for Espresso Roma.
Four: Isolate areas where 3+ circles overlap
If three or more businesses overlap, it’s we hypothesize it's a social spot. So now we simply look for clusters with 3 or more establishments:
Five: Scale across time
As a simple SQL query, we simply add group by openTime to our queries to compute these shapes at different times.
We see that these businesses all open around 10 AM Saturday and close around 6 PM Saturday. Indeed, this block is a fun place to be during the daytime, but is quiet at night.
How I Built It (the technical tricks)
Delivering novel results is easier when you pick the right tools. Here are some of the technical tricks I used.
Load OSM data in to PostGIS
I love SQL, and find it intuitive to manipulate GIS data in PostGIS. I first downloaded OSM data in the .osm.pbf format from geofabrik.de. Then I loaded it into PostGIS with osm2pgsql using a custom lua configuration to store almost all OSM data attributes in JSON.
The terminal command is something like
osm2pgsql -d osm --output=flex --style=/path/to/osm2pgsql-flex_config-generic.lua ~/Downloads/norcal-latest.osm.pbf
Interpreting OSM open hours
OSM open hours are stored in a somewhat-freeform format like Tu-Sa 10:00-16:00.
To structure that, I used the opening_hours evaluation tool to ask whether the business would be open at 2024-05-07 10:15, 2024-05-07 11:15, etc.
I chose to ask about times like 10:15 AM rather than 10:00 AM because many businesses open at these sorts of round times (like :00) and I didn't want to worry about the boundary condition (e.g. if it opens at 10:00 AM, will it show in the data as open at 10:00 AM?).
Since I only wanted to do this analysis once, I did a select distinct of all the open times and used the Chrome interactive terminal to get an open answer for all of the time strings at my sample timestamps. Then I loaded that into PostGIS and joined it onto my existing data.
Here was the simplified JS I put in the page
// https://openingh.openstreetmap.de/evaluation_tool/
var times = ['2024-05-07 10:15','2024-05-07 11:15' /*more times to model */]
var open_hrs = ['24/7','Mo-Fr 07:00-16:00; Sa, Su 08:00-15:00' /* more times from OSM */]
var outs = []
var doOneTime = function(h) {
try {
var oh = new opening_hours(h);
times.forEach(function(t) {
outs.push({
opening_hours: h,
obsTime: t,
isOpen: oh.getState((new Date(t)))
})
})
outs.push({
opening_hours: h,
obsTime: 'weekStable',
isOpen: oh.isWeekStable()
})
}
catch(e) {console.log(h);}
}
open_hrs.forEach(doOneTime)
console.log(outs.map(i=> i.opening_hours + ' ' + i.obsTime + ' ' + i.isOpen).join("\n"))
Working with data in PostGIS
I once wrote thousand-line programs using SQL Server Management Studio and grew accustomed to having an interactive SQL environment.
To recreate a similar environment with GIS visualization, I use DBeaver. I simply write select st_union(geom) from my_table; then click the spatial button to see my results.
Sharing my results with the world
To visualize the results, I use Leaflet with Leaflet Timeline starting with the Leaflet Timeline Earthquake demo.
Paid services exist to serve a beautiful background map, but for now I make due with the default map from OpenStreetMap. I make the background map 80% greyscale to help the open areas pop out while still distinguishing important colors like roads vs rivers.