Nested multiple Geo Point locations - sorting with geo distance point not returning all distances

(krishna) #1

I have created an index which has nested locations of Geo point type.
When sorting the results are only returning one distance. How to get distance of all locations with in a single document.
Please help me with this issue.
Query

dealers/_search/
{
    "sort": [
        {
            "_geo_distance": {
                "locations.point": {
                    "lat": 50.69,
                    "lon": -3.51
                },
                "nested_path": "locations",
                "order": "asc",
                "unit": "km"
            }
        }
    ],
    "query": {
        "nested": {
            "path": "locations",
            "query": {
                "bool": {
                    "must": {
                        "match_all": {}
                    },
                    "filter": {
                        "geo_distance": {
                            "distance": "200km",
                            "locations.point": {
                                "lat": 50.69,
                                "lon": -3.51
                            }
                        }
                    }
                }
            }
        }
    }
}

Index definition
/dealers/

{
    "mappings": {
        "_doc": {
            "properties": {
                "id": {
                    "type": "integer"
                },
                "locations": {
                    "type": "nested",
                    "properties": {
                        "name": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "point": {
                            "type": "geo_point"
                        }
                    }
                }
            }
        }
    }
}

Sample Documents
/dealers/_doc/1

{
    "locations": [
        {
            "name": "Exeter",
            "point": {
                "lat": 50.69,
                "lon": -3.51
            }
        },
        {
            "name": "Swindon",
            "point": {
                "lat": 51.54,
                "lon": -1.85
            }
        },
        {
            "name": "Reading",
            "point": {
                "lat": 51.42,
                "lon": -0.97
            }
        }
    ]
}

Second Sample document
/dealers/_doc/2

{
    "locations": [
        {
            "name": "Cobham",
            "point": {
                "lat": 51.33,
                "lon": -0.41
            }
        },
        {
            "name": "Swindon",
            "point": {
                "lat": 51.54,
                "lon": -1.85
            }
        },
        {
            "name": "Coventry",
            "point": {
                "lat": 52.43,
                "lon": -1.49
            }
        }
    ]
}

Third Document sample
/dealers/_doc/3

{
    "locations": [
        {
            "name": "Durham",
            "point": {
                "lat": 54.78,
                "lon": -1.54
            }
        },
        {
            "name": "Swindon",
            "point": {
                "lat": 51.54,
                "lon": -1.85
            }
        },
        {
            "name": "Coventry",
            "point": {
                "lat": 52.43,
                "lon": -1.49
            }
        }
    ]
}

Thank you,
Krishna

(Igor Motov) #2

Thank you for providing detailed information, it would have helped even more if you created a small reproduction script that we could run in Kibana and formatted it using ``` symbols like this:

```

you code here

```

Unfortunately, it is not a trivial task. While sorting, elasticsearch only keeps the minimal value of the distance, so you need to calculate the distance either in your application or by using script field, which is quite complicated at the moment (see discussion in https://github.com/elastic/elasticsearch/issues/25796 for more details).

(krishna) #4

Hi,
I have tried using below query but getting errors.
I have searched online everywhere, i could not find any example with scripts example for multiple geo points.
Could you please help me.
Query

    {
    "sort": [
        {
            "_geo_distance": {
                "locations.point": {
                    "lat": 50.69,
                    "lon": -3.51
                },
                "nested_path": "locations",
                "order": "asc",
                "unit": "km"
            }
        }
    ],
    "query": {
        "nested": {
            "path": "locations",
            "query": {
                "script": {
                    "script": {
                        "inline": "def l = new ArrayList(); def geopoint;for(int i=0; i < doc['locations'].length;i++){ geopoint = doc['locations'][i].point; l.add(geopoint.arcDistanceInKm(50.69,-3.51))} return l;",
                        "lang": "painless"
                    }
                }
            }
        }
    }
}

Response

{
    "took": 35,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 2,
        "skipped": 0,
        "failed": 3,
        "failures": [
            {
                "shard": 2,
                "index": "dealers",
                "node": "BHNGP_rFSi-_Ef34WufiQw",
                "reason": {
                    "type": "script_exception",
                    "reason": "runtime error",
                    "script_stack": [
                        "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:81)",
                        "org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:39)",
                        "i=0; i < doc['locations'].length;i++){ ",
                        "             ^---- HERE"
                    ],
                    "script": "def l = new ArrayList(); def geopoint;for(int i=0; i < doc['locations'].length;i++){ geopoint = doc['locations'][i].point; l.add(geopoint.arcDistanceInKm(50.69,-3.51))} return l;",
                    "lang": "painless",
                    "caused_by": {
                        "type": "illegal_argument_exception",
                        "reason": "No field found for [locations] in mapping with types []"
                    }
                }
            }
        ]
    },
    "hits": {
        "total": 0,
        "max_score": null,
        "hits": []
    }
}

Thanks,
Krishna

(krishna) #5

Hi,
After searching online and also following the post that is suggested by Igor Motov, I am able to get the distances now with some changes. I thought it will be helpful to some one if I post the changes I have done. May be there is a better way of solving this problem, but currently this is the solution I have.
First updated the index definition by adding 'include_in_root' property with true value.
And also updated the query to include the inner_hits within the nested query and added scripts_fileds inline script.
Updated Index Definition

{
    "mappings": {
        "_doc": {
            "properties": {
                "id": {
                    "type": "integer"
                },
                "locations": {
                    "type": "nested",
                    "include_in_root": true,
                    "properties": {
                        "id": {
                            "type": "integer"
                        },
                        "name": {
                            "type": "text",
                            "fields": {
                                "keyword": {
                                    "type": "keyword",
                                    "ignore_above": 256
                                }
                            }
                        },
                        "point": {
                            "type": "geo_point"
                        },
                        "vehicleCodeIds": {
                            "properties": {
                                "id": {
                                    "type": "integer"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Updated Query

{
    "sort": [
        {
            "_geo_distance": {
                "locations.point": {
                    "lat": 51.42,
                    "lon": -0.97
                },
                "order": "asc",
                "unit": "km"
            }
        }
    ],
    "query": {
        "nested": {
            "path": "locations",
            "query": {
                "bool": {
                    "must": {
                        "match_all": {}
                    },
                    "filter": {
                        "geo_distance": {
                            "distance": "2000km",
                            "locations.point": {
                                "lat": 51.42,
                                "lon": -0.97
                            }
                        }
                    }
                }
            },
            "inner_hits": {
                "_source": true,
                "size": 3,
                "script_fields": {
                    "distanceInMeters": {
                        "script": {
                            "inline": "double dist=Double.MAX_VALUE; for (int i=0; i<doc['locations.point'].length; i++) { double lat1 = doc['locations.point'][i].lat; double lon1 = doc['locations.point'][i].lon; double lat2 = params.lat; double lon2 = params.lon; double TO_METERS = 6371008.7714D; double TO_RADIANS = Math.PI / 180D; double x1 = lat1 * TO_RADIANS; double x2 = lat2 * TO_RADIANS; double h1 = 1 - Math.cos(x1 - x2); double h2 = 1 - Math.cos((lon1 - lon2) * TO_RADIANS); double h = h1 + Math.cos(x1) * Math.cos(x2) * h2; double cdist = TO_METERS * 2 * Math.asin(Math.min(1, Math.sqrt(h * 0.5))); dist = Math.min(dist, cdist);} return dist*0.001;",
                            "lang": "painless",
                            "params": {
                                "lat": 51.42,
                                "lon": -0.97
                            }
                        }
                    }
                }
            }
        }
    }
}

Thanks,
Krishna