Using Amazon DynamoDb for IP and co-ordinate based geo-location services part 10: querying the coordinate range table

Introduction

In the previous post we loaded the limited lng/lat range records into DynamoDb. As we’re only talking about about 50 records we could have added them in code one by one. However, that strategy would never work for the full MaxMind data set even after discarding the duplicates. So instead we looked at the built-in Import/Export functionality in DynamoDb. You’ll be able to go through the same process when you’re ready to import the full data set.

In this post we’ll see how to query the lnglat range database to extract the ID of the nearest geolocation. We’ll get to use the AWS Java SDK.

Querying DynamoDb

We inserted the necessary JAR dependencies into our Maven project in the previous post. We saw an example of querying DynamoDb in this post where we constructed a compound query to find the geoname ID belonging to a single IP address.

In this post, however, we’ll instead use the query endpoints built into the AWS DynamoDb geo-library. The query will involve 3 parameters: the longitude, a latitude and a radius in metres. So in fact we might not find just a single result from our query, but a range of results: all geographical data points within the radius of ‘n’ metres around a given pair of coordinates. The value of ‘n’ is very important as you can imagine. You might stand in the middle of a desert with no city or town within a radius of several kilometres. The same value of ‘n’ will give a list of towns and cities if you move to a densely inhabited area, like California.

We’ll go with a list of increasing values for ‘n’ testing each one by one until we find at least one geoname ID. If we get more than one locations then we’ll find the nearest based on their geohashes.

Before we go on I’d like to draw your attention to another query type that the AWS DynamoDb geo-library includes. You can construct a rectangle based on two coordinate pairs which will give the lower left and upper right hand corners of a rectangle. The library can provide the geoname IDs that lie within that rectangle. If you need that query type in your project then you can check out the QueryRectangleRequest and QueryRectangleResult objects.

We already saw in the post referenced above how to get hold of a DynamoDb client, we’ll reuse that here.

Let’s first see two helper methods. The following simply extracts the “geoname_id” property from the attribute list of a DynamoDn records:

private String extractGeonameId(Map<String, AttributeValue> rawPropertyMap)
{
    AttributeValue get = rawPropertyMap.get("geoname_id");
    String s = get.getN();
    return s;
}

…and the following one will find the closest number from a list of numbers. It will be used when we need to find one location if the query resulted in a range of locations.

private long closest(long of, List<Long> in)
{
    long min = Long.MAX_VALUE;
    long closest = of;

    for (long v : in)
    {
        final long diff = Math.abs(v - of);
        if (diff < min)
        {
            min = diff;
            closest = v;
        }
    }

    return closest;
}

The following method will test for a single pair of coordinates:

public void testSingleCoordinateLookup()
{
    String lngLatRangeTable = "geo-ip-coords-test";
    double latitude = 35.4;
    double longitude = 133.2;
    List<Integer> rangeLookupListInMeters = new ArrayList<>();
    rangeLookupListInMeters.add(1000);
    rangeLookupListInMeters.add(5000);
    rangeLookupListInMeters.add(10000);
    rangeLookupListInMeters.add(30000);
    rangeLookupListInMeters.add(50000);

    try
    {
        AmazonDynamoDBClient dynamoClient = getDynamoDbClient();
        GeoDataManagerConfiguration geoDataManagerConfig = new GeoDataManagerConfiguration(dynamoClient, lngLatRangeTable);
        GeoDataManager geoDataManager = new GeoDataManager(geoDataManagerConfig);
        GeoPoint centerPoint = new GeoPoint(latitude, longitude);
        String geonameId = "";
        for (int i = 0; i < rangeLookupListInMeters.size(); i++)
        {
            int radius = rangeLookupListInMeters.get(i);
            QueryRadiusRequest queryRadiusRequest = new QueryRadiusRequest(centerPoint, radius);
            QueryRadiusResult queryRadiusResult = geoDataManager.queryRadius(queryRadiusRequest);
            List<Map<String, AttributeValue>> queryResults = queryRadiusResult.getItem();
            if (queryResults.size() == 1)
            {
                geonameId = extractGeonameId(queryResults.get(0));
                break;
            } else if (queryResults.size() > 0)
            {
                long geoHash = S2Manager.generateGeohash(centerPoint);
                List<Long> geohashes = new ArrayList<>();
                Map<Long, Map<String, AttributeValue>> geohashAttribs = new HashMap<>();
                for (Map<String, AttributeValue> queryResult : queryResults)
                {
                    AttributeValue geoHashAttrib = queryResult.get("geohash");
                    String geoHashAttribValue = geoHashAttrib.getN();
                    long hash = Long.parseLong(geoHashAttribValue);
                    geohashes.add(hash);
                    geohashAttribs.put(hash, queryResult);
                }
                long closest = closest(geoHash, geohashes);
                geonameId = extractGeonameId(geohashAttribs.get(closest));
                break;
            }
        }
        if (geonameId.equals(""))
        {
            throw new Exception("Geoname id was not found lat " + latitude + ", and lng " + longitude);
        }
    } catch (Exception ex)
    {
        System.out.println(ex.getMessage());
    }
}

We build a list of radii in metres. We then build a couple of objects that are necessary for the query: GeoDataManagerConfiguration, GeoDataManager and GeoPoint. The request we’re about to make is represented by the QueryRadiusRequest object which accepts a centre point and a radius in metres. If we get just one result then we extract the geoname ID from it. Otherwise if we have a range of data records then we find the nearest one based on its geohash and the geohashes of the list of geolocations.

If you run the above code against the same limited data set I’ve been testing with you should get geoname ID 1848277 which was found when the radius was set at 30000 metres. It seems correct, that geoname ID corresponds to the following record in DynamoDb:

If we go with a wider radius…

List<Integer> rangeLookupListInMeters = new ArrayList<>();
rangeLookupListInMeters.add(100000);

…then the query will return 2 data points and the “closest” method will find geoname ID 1848277, i.e. the same as above.

So now we have seen two methods to extract a geoname ID from our tables: by a single IP lookup and a query based on a longitude-latitude pair.

In the next post we’ll create test geolocation database so that we can finally find the actual location of the IP/coordinate pair – or at least the nearest city or town.

View all posts related to Amazon Web Services and Big Data here.