How to search using geo_distance and two indices

[Pierre_S_Almeida (Pierre A)]

Hi,

I am trying to make a query on elasticsearch that reproduce the same result of this SQL:

    select pl.latitude, pl.longitude from places as pl
    where exists (
    select rf.id from references as rf where ST_Distance_Sphere(
            point(pl.longitude, pl.latitude),
            point(rf.longitude, rf.latitude)
        ) < 1000 
    ) 

In other words, based on two indices, to find out which place is less than 1000 meters from any reference in set registered.

My indices are:

GET /places
{
  "places" : {
    "aliases" : { },
    "mappings" : {
      "properties" : {
        "location" : {
          "type" : "geo_point"
        }
      }
    }
}

GET /references
{
"references" : {
    "aliases" : { },
    "mappings" : {
      "properties" : {
        "location" : {
          "type" : "geo_point"
        }
      }
    }
}

Is it possible in ES?

[miked187 (Mike D)]

GET /references, /places
{
...

https://www.elastic.co/guide/en/elasticsearch/reference/current/multi-index.html

[Christian_Dahlqvist]

This is a join, which Elasticsearch does not support. It is therefore not possible to do this in a single query. You probably need to break this up into multiple queries in you application or restructure your data somehow.