# Coordinates stored as lat/lng pair, not derived from address at render time

- **Pattern:** `ab-001037` (`directory-map-location.map-rendering.coordinates-stored-explicit`)
- **Severity:** medium
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-001037
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-001037)

## Why it matters

Deriving coordinates from address text at render time introduces three concrete failure modes: geocoding API quota exhaustion that breaks your map entirely, incorrect marker placement when the geocoder returns an ambiguous result (CWE-20), and latency added to every page load. ISO 25010:2011 functional-suitability requires that data used for core functionality — map marker placement — is accurate and available. A listing that maps to the wrong location due to geocoding ambiguity can send users to the wrong address, which is a direct trust and safety issue for a directory product.

## Severity rationale

Medium because render-time geocoding degrades reliability and accuracy without being immediately obvious, but the business impact accumulates as quota costs and misplaced markers.

## Remediation

Geocode once at listing creation time, persist the result, and retrieve coordinates directly during rendering. The schema must enforce at least 6 decimal places to avoid precision loss.

```sql
CREATE TABLE listings (
  id UUID PRIMARY KEY,
  name VARCHAR(255),
  address TEXT,
  latitude DECIMAL(10, 8),
  longitude DECIMAL(11, 8),
  created_at TIMESTAMP
);
```

Call your geocoding API in the listing creation handler (`src/app/api/listings/route.ts`), store the result immediately, and never call a geocoding API in map rendering code. If coordinates are missing for existing rows, run a one-time backfill migration rather than deferring geocoding to page load.

## Detection

- **ID:** `coordinates-stored-explicit`
- **Severity:** `medium`
- **What to look for:** Check the database schema for coordinates. Look for dedicated columns (latitude, longitude) or a geometry/point type. Verify that coordinates are persisted in the database, not re-computed from address text at render time. Check whether any rendering code calls a geocoding API.
- **Pass criteria:** Database has explicit lat/lng columns (or a point geometry type) with at least 6 decimal places of precision. Enumerate all database queries that fetch listing data for map rendering and confirm none call a geocoding API. Coordinates are stored at creation/import time and retrieved directly for rendering.
- **Fail criteria:** Addresses are stored but no lat/lng columns exist. Coordinates are computed from address text each time a listing is rendered, or geocoding calls happen in render-time code. A column that stores coordinates as a comma-separated text string does not count as pass.
- **Skip (N/A) when:** No map display exists.
- **Detail on fail:** `"Database has only address field; coordinates computed by geocoding API on each page load"` or `"Map rendering calls Google Geocoding API for each marker — no stored coordinates"`
- **Remediation:** Store coordinates explicitly at the time of listing creation:

  ```sql
  CREATE TABLE listings (
    id UUID PRIMARY KEY,
    name VARCHAR(255),
    address TEXT,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    created_at TIMESTAMP
  );
  ```

  Or use a PostGIS point:

  ```sql
  CREATE TABLE listings (
    id UUID PRIMARY KEY,
    name VARCHAR(255),
    address TEXT,
    location GEOGRAPHY(POINT, 4326),
    created_at TIMESTAMP
  );
  ```

  Geocode the address once during listing creation, store the result, and retrieve it directly during rendering.

## External references

- cwe CWE-20
- iso-25010 functional-suitability

Taxons: data-integrity

HTML version: https://auditbuffet.com/patterns/ab-001037