I'm trying to construct a DynamoDb table supporting these 3 access patterns:
- Access list of institutions within XX radius of a given zip code.
- Access list of institutions within XX radius of a given zip code for a given education level (4-year, 2-year, high school, etc).
- Access list of institutions within XX radius of a given zip code for a given job code and education level.
The Partition Key is generated as part of the Geo Library for DynamoDb (AWS Geo Library). So basically, I'm grouping institutions together by geo-location supporting the ability to search for institutions within XX radius of a supplied zip code (using zip code's lat/lon).
Below is my CloudFormation template for my DynamoDb table:
dynamoDbInstitutions:
Type: AWS::DynamoDB::Table
Properties:
TableName: institutions
AttributeDefinitions:
- AttributeName: hashKey
AttributeType: N
- AttributeName: rangeKey
AttributeType: S
- AttributeName: geohash
AttributeType: N
KeySchema:
- AttributeName: hashKey
KeyType: HASH
- AttributeName: rangeKey
KeyType: RANGE
ProvisionedThroughput:
ReadCapacityUnits: 50
WriteCapacityUnits: 2
LocalSecondaryIndexes:
- IndexName: geohash-index
KeySchema:
- AttributeName: hashKey
KeyType: HASH
- AttributeName: geohash
KeyType: RANGE
Projection:
ProjectionType: ALL
Here's an example of data in my table:
- 176008 represents an institution.
- 2 represents education level (4-year institution).
- 11-1011.00 represents a job code
Hashkey value 966762 is the first 6 digits from GeoHash value. This allows me to group institutions together by proximity. This allows me to search for institutions within a radius. I have a Local Secondary Index (LSI) using Hashkey and GeoHash. My query is using LSI to find institutions within a radius.
I'm wanting to also be able to further filter institutions within the radius by these access patterns:
- education level. Example: 2_*
- job code and education level. Example: 11-1011.00_2_*
I've run into a wall where DynamoDb only allows querying based on 2 key conditions. In order to do geo querying (within a radius), I have to use hash key and LSI (using geohash). I'm prevented from further filtering using my range key which is modeling relationships an institution has with education level and job codes.
How do I use DynamoDb to filter by radius AND filter using related data like education level and/or job code?