Location-Based Store Finder with PostGIS

Overview#

"Find a branch near me" is one of the most common location features on business websites, and one of the most often done badly. The usual shortcut is to send every store to the browser and sort them there. That works for twenty stores and falls over at two thousand.

This concept shows how I would build it with PostGIS, and makes the database's work visible. The live demo runs the same query stages in TypeScript, shows the row count after each stage, and prints the SQL that would answer the same search on a real database.

My role#

I built everything: the fixture generator, the search engine, the opening-hours logic, the SQL builder, the map and list interface and this write-up. The brand ("Corner Mart") and every branch are invented. The area names are real Lahore neighbourhoods so the map makes sense, but the shop addresses are made up.

The problem#

A good store finder answers three questions quickly:

• Which stores are near this point? Either "within 5 km" or "the 10 closest", and those are different queries. A radius can return nothing in a rural area. A nearest-k query always returns something but can return stores 80 km away.
• Which of them can I use? Open now, has a pharmacy, has parking. Opening hours are harder than they look: shops in Lahore often stay open past midnight, many close for Friday prayers, and the visitor's device may be in a different time zone from the store.
• How do I get there? A distance that matches what the user sees on the map, and a directions link.

Approach & architecture#

Stores have a geography(Point, 4326) column. Using geography rather than geometry means ST_DWithin and ST_Distance work in metres on the spheroid, with no projection to choose.

CREATE TABLE stores (
  id        bigint PRIMARY KEY,
  name      text NOT NULL,
  address   text NOT NULL,
  tz        text NOT NULL DEFAULT 'Asia/Karachi',
  services  text[] NOT NULL DEFAULT '{}',
  location  geography(Point, 4326) NOT NULL
);
CREATE INDEX stores_location_gix ON stores USING gist (location);
CREATE INDEX stores_services_gin ON stores USING gin (services);

The demo's search function follows the plan PostgreSQL would use. For a radius, it builds a latitude and longitude box that fully contains the circle, keeps the stores inside it (the index step), then rechecks the exact haversine distance:

const bbox = bboxAround(p.center, p.radiusM);
const inBox = stores.filter((s) => inBBox(s.location, bbox));
const within = inBox.map(toHit).filter((x) => x.distanceM <= p.radiusM);
const filtered = within.filter((x) => matches(x.store));
const hits = filtered.sort((a, b) => a.distanceM - b.distanceM).slice(0, RADIUS_LIMIT);

The panel under the map prints the SQL for the current search, with the real coordinates, radius and filters filled in. A 3 km search with "open now" and "pharmacy" produces a query with ST_DWithin, an array containment check on services, an EXISTS subquery on opening_hours, and ORDER BY s.location <-> point.

Key challenges#

• Shifts past midnight. A shop open 08:00 to 02:00 is open at 01:30 on Tuesday because of Monday's hours. I store intervals in minutes from local midnight and allow close to go past 1440. The open check looks at today's intervals and at yesterday's intervals shifted back by a day. The SQL version does the same with interval columns.
• Time zones. "Open now" has to use the store's clock. The demo converts the current time to Asia/Karachi with Intl.DateTimeFormat, so a visitor in London sees the same answer as one in Lahore. Time presets (Friday 13:45, Tuesday 01:30) make the edge cases easy to test.
• Radius versus nearest. I support both and say which one is running. If your browser location is far from Lahore, the demo switches to nearest-k and tells you why, instead of showing an empty list.
• Denied location. Geolocation can be refused or time out. Both cases leave a clear message and the map click and area presets still work.

Tech stack#

• Demo: TypeScript, React and Next.js, Leaflet with OpenStreetMap tiles. Distances use my own haversine and bounding-box helpers.
• Production design: PostgreSQL with PostGIS, a GiST index on geography, a GIN index on services, Next.js route handlers for the search API, and statically generated store pages with LocalBusiness structured data. See the geospatial skills page for related work.

Results#

• A working finder: radius and nearest-k search, open-now and service filters, time presets, geolocation with fallbacks, a synced map and list, and directions links.
• The stage counter shows the prefilter doing its job. For a 3 km search around Gulberg, the bounding box keeps 20 of the 140 stores and the exact check keeps 14 (counted in the demo, on its generated data).
• The SQL panel changes with every control, so the query and the UI never disagree.

I have not run this against a real PostGIS database for this write-up, so there are no query timings here. This is a concept build without real users.

Try the demo#

Open the store finder demo. Things to try:

1. Click somewhere in DHA and switch the radius from 3 km to 10 km. Watch the stage counts and the SQL change.
2. Tick Open now and set the time to Friday 13:45. Branches with a prayer break drop out.
3. Set the time to Tuesday 01:30: only late-night and 24-hour branches remain.
4. Switch to nearest k and drag the pin outside the city.

What I would do next#

• Move the search into a Next.js route handler backed by a PostGIS database, and check with EXPLAIN ANALYZE that both query shapes use the GiST index.
• Add a geocoder (Nominatim within its usage policy, with caching) for typed addresses.
• Build the store detail pages with LocalBusiness JSON-LD and opening hours in schema.org format.
• Add tests that compare search results against a brute-force distance sort for random points.