How to Filter for Coordinate Range in DynamoDB

I have multiple items in DynamoDB structured like this in JSON:

{
 "MemoryID": "f...ed2d521d84eb",
 "CreationDate": "12-22-2024",
 "GeoJSON": {
  "geometry": {
   "coordinates": [
    -122.1805,
    37.8894
   ],
   "type": "Point"
  },
  "properties": {
   "dbh": 12
  },
  "type": "Feature"
 }
}

I’m trying to go through the database and filter by coordinates. In other words, I have a defined frame, and I want to get all items who have coordinates within the frame. This is the code I have right now:

export const getFiltered = async (startLat: number, endLat: number, startLong: number, endLong: number) => {
    const command = new ScanCommand({
        TableName: process.env.TABLE_NAME as string,
        // latitude usually appears first, but not when I saved it thru MapBox
        FilterExpression: "GeoJSON.geometry.coordinates[1] BETWEEN :startLat AND :endLat AND GeoJSON.geometry.coordinates[0] BETWEEN :startLong AND :endLong",
        ExpressionAttributeValues: {
            ":startLat": { N: startLat },
            ":endLat": { N: endLat },
            ":startLong": { N: startLong },
            ":endLong": { N: endLong }
        }
    });

    try {
        const result = await dbClient.send(command);
        return result.Items as Array<DataModel>;
    } catch (error) {
        console.error("Error fetching filtered results:", error);
        throw error;
    }
}

When I run this code, it shows up as internal server error 500 in console, and a status code of 400 in my terminal. I am pretty sure my mistake lies somewhere in FilterExpression and ExpressionAttributeValues because when I comment those lines, and I leave it the ScanCommand with just TableName, it retrieves all the elements just fine. The problem just lies in getting the filtered results.

Also, I’ve made sure that the startLat, endLat parameters of the function loads correctly into getFiltered

Any guidance is appreciated! 🙂

>Solution :

The issue in your code lies in how DynamoDB's FilterExpression syntax works. Specifically, you can’t use array indexing (e.g., GeoJSON.geometry.coordinates[1]) in DynamoDB’s query language.

Solution:
Store Coordinates Separately: If possible, restructure your DynamoDB table to store latitude and longitude as separate attributes rather than as part of an array in GeoJSON.geometry.coordinates. This makes filtering straightforward.

For example:

{
    "MemoryID": "f...ed2d521d84eb",
    "CreationDate": "12-22-2024",
    "Latitude": 37.8894,
    "Longitude": -122.1805
}

Dynamically construct ExpressionAttributeValues by extracting latitude and longitude values in your application code:

const command = new ScanCommand({
    TableName: process.env.TABLE_NAME as string,
    FilterExpression: "GeoJSON.#g.#c.#lat BETWEEN :startLat AND :endLat AND GeoJSON.#g.#c.#long BETWEEN :startLong AND :endLong",
    ExpressionAttributeNames: {
        "#g": "geometry",
        "#c": "coordinates",
        "#lat": "1", // Latitude index
        "#long": "0" // Longitude index
    },
    ExpressionAttributeValues: {
        ":startLat": startLat,
        ":endLat": endLat,
        ":startLong": startLong,
        ":endLong": endLong
    }
});

Debugging Internal Server Error:

Verify that startLat, endLat, startLong, and endLong are numbers. If these are strings or other types, DynamoDB will throw an error.
Ensure the attribute exists in every record; if GeoJSON.geometry.coordinates is missing in some records, the filter might fail.
Validate Environment Variables: Ensure process.env.TABLE_NAME contains the correct table name. This doesn’t directly affect the filtering but may cause errors if misconfigured.

Example Code After Fix
Here’s the updated code:

export const getFiltered = async (startLat: number, endLat: number, startLong: number, endLong: number) => {
    const command = new ScanCommand({
        TableName: process.env.TABLE_NAME as string,
        FilterExpression: "GeoJSON.geometry.coordinates[1] BETWEEN :startLat AND :endLat AND GeoJSON.geometry.coordinates[0] BETWEEN :startLong AND :endLong",
        ExpressionAttributeValues: {
            ":startLat": startLat,
            ":endLat": endLat,
            ":startLong": startLong,
            ":endLong": endLong
        }
    });

    try {
        const result = await dbClient.send(command);
        return result.Items as Array<DataModel>;
    } catch (error) {
        console.error("Error fetching filtered results:", error);
        throw error;
    }
};