Nearest store

Amperity can be configured to calculate the store that is nearest to an individual customer, as represented by their Amperity ID.


Nearest store calculations must be enabled for use in Amperity. This effort typically requires some customization and is not the same across tenants.

Contact your Amperity support representative via the Amperity Support Portal (or send email to to discuss your options for adding nearest store capabilities to your tenant.


Calculating nearest store requires data to be provided to Amperity that includes the latitude and longitude for each store location. Amperity then uses a publicly available dataset to convert home zip codes to latitude and longitude. Amperity then uses the geographic center of each zip code to calculate the straight-line distance for the store nearest to the individual.

Calculated fields

For each record in the customer 360 database, the following fields are added:

  • nearest_store_id

  • nearest_store_name

  • nearest_store_distance_miles


A very simple example of calculating nearest stores starts with a common table expression that uses a Haversine formula to calculate latitude and longitude. United States zip codes are joined to a table that contains data about the physical location of stores, after which stores are ranked by distance. The store with the highest rank is the closest store.


This example is appropriate for use with small, static data sources, or for defining a proof of concept for early iteration that will require a more complex workflow.

  distance_cte AS (
      2 * 3959 * asin(
              radians(CAST(stores.Latitude AS decimal(15, 10))) -
              radians(CAST(census.latitude AS decimal(15, 10))))
            / 2), 2)
          + cos(radians(CAST(stores.Latitude AS decimal(15, 10))))
          * cos(radians(CAST(census.latitude AS decimal(15, 10))))
          * power(
              radians(CAST(stores.Longitude AS decimal(15, 10))) -
              radians(CAST(census.longitude AS decimal(15, 10))))
            / 2), 2)
        ) distance
    FROM Stores_Table stores
    CROSS JOIN Census_Table census
    WHERE stores.Residence = true
  ranked_distance AS (
    RANK() OVER (PARTITION BY zip_code ORDER BY distance asc, store_id) AS rank
    FROM distance_cte
    WHERE distance IS NOT NULL

FROM ranked_distance
WHERE rank = 1