## Prepare ground truth data

To use Population Dynamics embeddings, your ground truth data must be aggregated
to a supported geographic boundary. Because administrative boundary types vary
globally, you can align your data using either universal mathematical grid
systems (like S2 cells) or local administrative regions (such as counties or
districts, depending on the specific country dataset).

### Option 1: Incorporate embeddings into an existing model

- Prepare existing model-based ground truth: Use the embeddings as geospatial covariates to enhance an existing model.
- Train an error correction model: Improve an existing model by integrating the embeddings into a model that takes the original model output, the expected value or ground truth, and the embeddings to learn a new error correction model.

### Option 2: Tune for specific use cases

- Choose a prediction model: Any model, such as GBDT, MLP, or linear, can be used for predictions.
- Use embeddings for prediction: Use Population Dynamics embeddings as input features, alongside other contextual data, to improve prediction accuracy.

### Custom boundary aggregation

If your ground truth data uses custom polygons, such as postal codes, drive-time
isochrones or trade areas, you can perform a boundary aggregation. This process
combines multiple S2 cell vectors into a single representation for the target
polygon. Choosing the right weighting methodology ensures the aggregated
embedding accurately reflects your downstream modeling goals.

#### 1. Population-weighted average (recommended default for PDI)

Use population-weighted aggregation for human-centric use cases, such as retail
store performance or consumer behavior modeling.

Using an area-weighted spatial aggregation for demographic data can warp your
machine learning features. This occurs when unpopulated areas, such as parks,
industrial zones, or bodies of water, distort the profile of actual residents.

To resolve this, you can perform a population-weighted average in BigQuery. This
approach uses high-resolution demographic datasets, such as [WorldPop in the
Earth Engine Data
Catalog](https://developers.google.com/earth-engine/datasets/catalog/WorldPop_GP_100m_pop),
to calculate the precise density of each intersecting S2 cell segment.

To see a complete implementation example of a population-weighted workflow, run
the interactive notebook.

|---|---|
| [![Colab logo](https://developers.google.com/static/earth-engine/images/colab_logo_32px.png) Run in Google Colab](https://colab.research.google.com/github/googlemaps-samples/insights-samples/blob/main/population_dynamics_insights/notebooks/worldpop_weighted_custom_boundary_aggregation/pdi_worldpop_weighted_custom_boundary_aggregation.ipynb) | [![GitHub logo](https://developers.google.com/static/earth-engine/images/GitHub-Mark-32px.png) View source on GitHub](https://github.com/googlemaps-samples/insights-samples/blob/main/population_dynamics_insights/notebooks/worldpop_weighted_custom_boundary_aggregation/pdi_worldpop_weighted_custom_boundary_aggregation.ipynb) |

#### 2. Area-weighted average

For environmental or physical use cases, use area-weighted aggregation instead.
This is useful for land use analysis, built environment studies, or
infrastructure planning where you must assess regions regardless of population
distribution.

In these scenarios, physical land area is more relevant than human population
density. This makes sure every square kilometer within the polygon boundary
contributes equally to the aggregated vector.

Under this method, each constituent S2 cell's embedding vector is weighted by
the geographic surface area it covers within the target polygon.

## Geospatial operations \& S2 cell conversion

Population Dynamics Insights uses S2 cell Level 12 tokens (stored in the `geo_id` column
as 7-character hexadecimal strings) as its primary spatial unit. You can use the
[built-in geography functions](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions)
in BigQuery to convert point coordinates or custom spatial boundaries into
matching S2 cell tokens. To convert S2 cell tokens into polygon geometries for
visualization, you can use client-side libraries. For more information, see [Get
S2 cell geometry from an S2 Level 12 hex
token](https://developers.google.com/maps/documentation/population-dynamics-insights/use-embeddings#get_s2_cell_geometry_from_an_s2_level_12_hex_token).

### Convert point coordinates to S2 Level 12 hex tokens

To match lat/lng point coordinates with the `geo_id` column in
Population Dynamics Insights, use the BigQuery `S2_CELLIDFROMPOINT` function combined with
`ST_GEOGPOINT`.

BigQuery stores S2 cell IDs as signed `INT64` values, which causes cell IDs in
certain regions to evaluate as negative integers. In the examples below, we
define a helper function called `TO_S2_HEX` that converts the integer into a
standard hexadecimal token and strips trailing zeros per the S2 Geometry
specification.

> [!NOTE]
> **Note:** The `ST_GEOGPOINT(longitude, latitude)` function expects **longitude
> first, followed by latitude**. Reversing this order is a common source of query errors.

The following query converts point coordinates for Times Square, NYC (Latitude
`40.75796`, Longitude `-73.98554`) into its corresponding S2 Level 12 hex token:

```googlesql
CREATE TEMP FUNCTION TO_S2_HEX(s2_int INT64) RETURNS STRING AS (
  RTRIM(FORMAT('%x%015x', (s2_int >> 60) & 0xF, s2_int & 0x0FFFFFFFFFFFFFFF), '0')
);

SELECT
  'Times Square, NYC' AS location_name,
  TO_S2_HEX(S2_CELLIDFROMPOINT(ST_GEOGPOINT(-73.98554, 40.75796), level => 12)) AS geo_id;
```

To convert an existing point table with `latitude` and `longitude` columns into
joinable S2 tokens:

```googlesql
CREATE TEMP FUNCTION TO_S2_HEX(s2_int INT64) RETURNS STRING AS (
  RTRIM(FORMAT('%x%015x', (s2_int >> 60) & 0xF, s2_int & 0x0FFFFFFFFFFFFFFF), '0')
);

SELECT
  store_id,
  TO_S2_HEX(S2_CELLIDFROMPOINT(ST_GEOGPOINT(longitude, latitude), level => 12)) AS geo_id
FROM
  `your-project.internal_data.store_locations`;
```

### Generate covering S2 Level 12 cell tokens for a polygon

If your data uses custom area boundaries (such as trade areas, delivery zones,
or postal codes in WKT or GeoJSON format), convert the text representation to a
`GEOGRAPHY` using `ST_GEOGFROM` and use `S2_COVERINGCELLIDS` to extract all
intersecting S2 Level 12 cells.

The following query extracts all S2 Level 12 hex tokens covering a custom
polygon in Midtown Manhattan:

```googlesql
CREATE TEMP FUNCTION TO_S2_HEX(s2_int INT64) RETURNS STRING AS (
  RTRIM(FORMAT('%x%015x', (s2_int >> 60) & 0xF, s2_int & 0x0FFFFFFFFFFFFFFF), '0')
);

WITH CustomBoundary AS (
  SELECT ST_GEOGFROM('POLYGON((-73.99 40.75, -73.97 40.75, -73.97 40.76, -73.99 40.76, -73.99 40.75))') AS geom
)
SELECT
  TO_S2_HEX(s2_id) AS geo_id
FROM
  CustomBoundary,
  UNNEST(S2_COVERINGCELLIDS(geom, min_level => 12, max_level => 12)) AS s2_id;
```

### Get S2 cell geometry from an S2 Level 12 hex token

BigQuery GIS functions are one-way converters (Geometry → S2 Cell ID) and
don't include a function to convert an S2 cell token into a polygon `GEOGRAPHY`.
To obtain the polygon geometry of an S2 cell token for visualization or spatial
analysis, use a client-side library in Python such as
[`s2sphere`](https://pypi.org/project/s2sphere/)
to decode a 7-character S2 cell token into its boundary coordinates, and
[`shapely`](https://shapely.readthedocs.io/en/stable/)
to construct a polygon for mapping tools like Folium:

```python
import s2sphere
from shapely.geometry import Polygon

def s2_token_to_polygon(s2_token: str) -> Polygon:
    """Converts a 7-character S2 cell token into a Shapely Polygon."""
    cell_id = s2sphere.CellId.from_token(s2_token)
    cell = s2sphere.Cell(cell_id)

    # Extract the 4 corner vertices of the S2 cell
    vertices = []
    for i in range(4):
        vertex = cell.get_vertex(i)
        lat_lng = s2sphere.LatLng.from_point(vertex)
        vertices.append((lat_lng.lng().degrees, lat_lng.lat().degrees))

    # Close the polygon loop
    vertices.append(vertices[0])
    return Polygon(vertices)

# Example: Get boundary for S2 token '549056f'
cell_polygon = s2_token_to_polygon('549056f')
print(cell_polygon.wkt)
```

## Query examples

Replace `your-project.your_dataset.embeddings_table` with your actual project,
dataset, and target table name.

### SQL: Fetch embeddings

This query retrieves the embedding vector and administrative metadata for the S2
cells in your provisioned dataset.

```googlesql
SELECT
  geo_id,
  administrative_area_level_1_name AS state,
  administrative_area_level_2_name AS county,
  features -- The 330-dim vector
FROM
  `your-project.your_dataset.embeddings_table`
LIMIT 10;
```

### SQL: Find similar locations

This query identifies behaviorally similar locations without requiring external
data.

It uses the `ML.DISTANCE` function to calculate cosine similarity, returning the
top matches for a target S2 cell. This approach supports expansion planning
scenarios, such as determining where to open a new store based on the profile of
a successful existing location.

To visualize S2 cells on a map, you must convert or join the S2 cell ID to its
corresponding polygon geometry, because this dataset uses S2 cell tokens instead
of latitude and longitude points.

```googlesql
WITH TargetLocation AS (
  SELECT features AS target_vector
  FROM `your-project.your_dataset.embeddings_table`
  -- Replace with your target S2 hex token (e.g., '80ead45')
  WHERE geo_id = 'YOUR_TARGET_S2_TOKEN'
)

SELECT
  t.geo_id,
  t.administrative_area_level_1_name AS state,
  t.administrative_area_level_2_name AS county,
  -- Calculate Similarity (1.0 is identical, 0.0 is dissimilar)
  (1 - ML.DISTANCE(t.features, p.target_vector, 'COSINE')) AS similarity_score
FROM
  `your-project.your_dataset.embeddings_table` t,
  TargetLocation p
WHERE
  t.geo_id != 'YOUR_TARGET_S2_TOKEN' -- Exclude the target itself
ORDER BY
  similarity_score DESC
LIMIT 20;
```

### SQL: Join customer data

This example demonstrates how to enrich your own internal data (for instance, a
store performance table) with behavioral embeddings. Ensure your internal data
includes matching S2 cell tokens (hex strings).

```googlesql
SELECT
  store.store_id,
  store.s2_token,
  store.total_revenue,
  embeddings.features AS pdfm_vector
FROM
  `your-project.internal_data.store_performance` AS store
JOIN
  `your-project.your_dataset.embeddings_table` AS embeddings
ON
  -- Join based on the S2 hex token string
  store.s2_token = embeddings.geo_id
```

### Python: Load data for machine learning

The embeddings are stored as BigQuery Arrays. To use them in ML libraries, you
must convert the column into a NumPy matrix.

```python
from google.cloud import bigquery
import numpy as np
import pandas as pd

client = bigquery.Client()

query = """
    SELECT
        geo_id,
        features -- Returns as a list of floats
    FROM
        `your-project.your_dataset.embeddings_table`
    LIMIT 1000
"""

# 1. Load data into DataFrame
df = client.query(query).to_dataframe()

# 2. Convert the 'features' column (Series of Lists) into a Matrix (2D Array)
X_matrix = np.stack(df['features'].values)

print(f"Data Loaded. Matrix Shape: {X_matrix.shape}")
# Output: Data Loaded. Matrix Shape: (1000, 330)
```