I have this query to find nearest points from an array of points (geo search using “bounding box” concept):

```
SELECT d.type,
d.name,
d.`key`,
p.*,
(ACOS(SIN( RADIANS(55.55)) * SIN(RADIANS(p.lat)) + COS( RADIANS(55.55 )) * COS(RADIANS(p.lat)) * COS(RADIANS(p.lon) - RADIANS( 11.25))) * 6371) distance
FROM data d
UNNEST points p
WHERE d.type IN ['Lake','Stream','PutTakeLake','CoastLocalArea','SeaLocalArea']
AND p.lat BETWEEN 55.5 AND 55.6
AND p.lon BETWEEN 11.0 AND 11.5
ORDER BY distance ASC
LIMIT 100
```

It works - but I would like to speed it up.

The data structure looks like this:

```
{
"key": "8642",
"name": "The lake",
"points": [
{
"lat": 55.5747937187,
"lon": 11.2856648546
},
{
"lat": 55.5930342762,
"lon": 11.2790527292
},
:
:
{
"lat": 55.5566764979,
"lon": 11.3007332022
}
],
"type": "Lake"
}
```

I have looked at various samples for building the index and have come up with:

```
CREATE INDEX `def_nearest1` ON `data`(`type`,`name`,`key`,(all (array [(`p`.`lat`), (`p`.`lon`)] for `p` in `points` end))) WHERE (`type` in ["Lake", "Stream", "PutTakeLake", "CoastLocalArea", "SeaLocalArea"])
```

The query uses the index according to this “explains” - but is slow, so I obviously haven’t found the right way to do this…

```
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"as": "d",
"index": "def_nearest1",
"index_id": "ecb344c87d7ff413",
"index_projection": {
"primary_key": true
},
"keyspace": "data",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"CoastLocalArea\"",
"inclusion": 3,
"low": "\"CoastLocalArea\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"Lake\"",
"inclusion": 3,
"low": "\"Lake\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"PutTakeLake\"",
"inclusion": 3,
"low": "\"PutTakeLake\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"SeaLocalArea\"",
"inclusion": 3,
"low": "\"SeaLocalArea\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"Stream\"",
"inclusion": 3,
"low": "\"Stream\""
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "Fetch",
"as": "d",
"keyspace": "data",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Unnest",
"as": "p",
"expr": "(`d`.`points`)"
}
]
}
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((`d`.`type`) in [\"Lake\", \"Stream\", \"PutTakeLake\", \"CoastLocalArea\", \"SeaLocalArea\"]) and ((`p`.`lat`) between 55.5 and 55.6)) and ((`p`.`lon`) between 11 and 11.5))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`d`.`type`)"
},
{
"expr": "(`d`.`name`)"
},
{
"expr": "(`d`.`key`)"
},
{
"expr": "`p`",
"star": true
},
{
"as": "distance",
"expr": "(acos(((sin(radians(55.55)) * sin(radians((`p`.`lat`)))) + ((cos(radians(55.55)) * cos(radians((`p`.`lat`)))) * cos((radians((`p`.`lon`)) - radians(11.25)))))) * 6371)"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"limit": "100",
"sort_terms": [
{
"expr": "`distance`"
}
]
},
{
"#operator": "Limit",
"expr": "100"
},
{
"#operator": "FinalProject"
}
]
}
```

*I’m trying this on: Community Edition 6.6.0 build 7909*