How to perfrom query on multiple secondary indexes using python client?

[Nitin_Tiwari]

name and geometry both are indexed

{ ‘name’: ‘nitin’, ’ geometry’: ‘{“type”: “Polygon”, “coordinates”: [[[77.6367329816338, 12.9156783], [77.6358329816338, 12.9154783], [77.6340329816338, 12.9156783], [77.6340329816338, 12.9132783], [77.6358329816338, 12.9134783], [77.6367329816338, 12.9138783], [77.6367329816338, 12.9156783]]]}’})]

This is my data inside test namespace and demo set.

I want to perform a query based on two fields name and geometry.

q = client.query(‘test’, ‘demo’)

q.select(‘name’, ‘geometry’)

point = GeoJSON({‘type’: “Point”, ‘coordinates’: [77.634678,12.913777]})

q.where(p.equals(‘name’, ‘nitin’))

q.where(p.geo_contains_geojson_point(‘geometry’, point.dumps()))

It is giving me all the results where the name equals nitin.

How can I perform geo_contains_geojson_point with matching name field so that it should only return the geometry with a matching name field?

[rbotzer]

Currently you can only use one secondary index, which is optional. If you want to query for records with a specific name and with a polygon that contains a specific point you can do one of the following:

1. Use the secondary index over the names, use a GeoJSON predicate expression on the records matched by the index to identify the records with the right polygons.
2. Use the secondary index over the polygons, use a string equals predicate expression on the records matched by the index to identify the records with the right names.
3. Use no secondary indexes. Instead combine two predicate expressions, one for name, one for the GeoJSON ‘contains’ predicate.

There are several operational reasons for choosing whether to use a secondary index at all. In (3) you get a full table scan with the predicates applied to all records.

The rule of thumb would be to toss out as many records as you can before applying other predicates. You should know your data, and you can look at the server stats, but it makes sense that you’d use the name secondary index, if it gives you the smallest subset of the data, and only then apply the GeoJSON predicate on it. This should run faster and with less CPU. Similarly, if you’re using no secondary index, order the predicates so that the first one can quickly skip the most possible records before the other predicate is applied.

from args import options
import aerospike
from aerospike import exception as ex
from aerospike import GeoJSON
from aerospike import predicates as p
from aerospike import predexp as predexp
import pprint
import sys

try:
    client = aerospike.client(config).connect(options.username, options.password)
except ex.ClientError as e:
    print("failed to connect to the cluster with", config["hosts"])
    sys.exit(1)

pp = pprint.PrettyPrinter(indent=2)
key = ("test", "demo", "multiple-predicates")
try:
    client.remove(key)
except:
    pass
try:
    try:
        client.index_geo2dsphere_create("test", "demo", "geometry", "geometry_idx")
    except:
        pass
    try:
        client.index_string_create("test", "demo", "name", "name_idx")
    except:
        pass
    data = {
        "name": "nitin",
        "geometry": GeoJSON(
            {"type": "Polygon", "coordinates": [[[77.6367329816338, 12.9156783], [77.6358329816338, 12.9154783], [77.6340329816338, 12.9156783], [77.6340329816338, 12.9132783], [77.6358329816338, 12.9134783], [77.6367329816338, 12.9138783], [77.6367329816338, 12.9156783]]]}
        ),
    }
    client.put(key, data)
    k, m, b = client.get(key)
    pp.pprint(b)

    the_point = GeoJSON({"type": "Point", "coordinates": [77.634678,12.913777]}).dumps()
    predicate =  [
        predexp.geojson_bin("geometry"),
        predexp.geojson_value(the_point),
        predexp.geojson_contains()
    ]
    query = client.query("test", "demo")
    query.where(p.equals("name", "nitin"))
    query.predexp(predicate)
    res = query.results()
    pp.pprint(res)
except Exception as e:
    print("Error: {}".format(e))
client.close()

The results are as expected.

[Nitin_Tiwari]

Thanks rbotzer.