I'm working on an app that uses DynamoDB as the main data store, and I'm facing a challenge with one of my use cases: querying items based on proximity. Each item has latitude and longitude data, and I want users to be able to search within a certain radius (like 10 km) while also applying additional filters such as creation date, object type, and target age.
The problem with DynamoDB is that it's designed around a single partition and sort key, which complicates this type of query. I considered using geohashes as the sort key, but there are some trade-offs involved:
- If I use a shorter geohash for lower precision, I'll have fewer partitions to query but will need to filter out many items that don't fall within the set radius.
- Conversely, if I use a longer geohash for better accuracy, I'll have to query multiple adjacent hash keys to encompass the search area.
One idea I had is to maintain a separate "query table" in a different database that holds the queryable attributes (latitude, longitude, creation date, etc.) along with the item's DynamoDB ID. This way, I could query that table first, avoiding the limitations of DynamoDB, and then use `BatchGetItem` to retrieve the full records from DynamoDB.
So, I'm curious: what's the most cost-effective database solution for managing geospatial queries with multiple filters? Do you have any recommendations for a specific database that could fit this need, or do you think DynamoDB might still be the most affordable choice despite all the complexity?
3 Answers
For this kind of geospatial and filtered querying, I would suggest looking into RDS with PostgreSQL and PostGIS extensions. It supports indexed nearest-neighbor queries really well and can be efficient if you're using it as an index into DynamoDB. A smaller instance should be enough as the data rows will be compact.
You might also want to consider using H3 tessellations as your geohash. It allows you to select the precise level of granularity based on the size of the hexagons, which could align better with your use case.
Here are some useful links to explore more options:
- https://aws.amazon.com/products/databases/
- https://aws.amazon.com/rds/
- https://aws.amazon.com/dynamodb/
- https://aws.amazon.com/aurora/
- https://aws.amazon.com/redshift/
- https://aws.amazon.com/documentdb/
- https://aws.amazon.com/neptune/
And you can also try searching for database-related questions to gather more insights.
Thanks for the tip! I'll definitely check out PostGIS.