Kibana - intersection of two data sets on map

I have a index named 'request' with the below schema -

```
"id": "160c6273-8462-4830-9a51-dabf2e292ebb",
"username": "bob_dylan",
"date": "Jul 6, 2021 @ 12:00:00.000",
"location": {
    "lat": 48.8,
    "lon": 2.35
}```

And my index properties are as below -

   "mapping": {
		"properties": {
			"date": {
				"type": "date"
			},
			"id": {
				"type": "text",
				"fields": {
					"keyword": {
						"type": "keyword",
						"ignore_above": 256
					}
				}
			},
			"location": {
				"type": "geo_point"
			},
			"username": {
				"type": "text",
				"fields": {
					"keyword": {
						"type": "keyword",
						"ignore_above": 256
					}
				},
				"fielddata": true
			}
		}

It is possible to choose 2 data sets with geo_bounding_box query and after it discover usernames that appears in both sets at least once?

P.S the same username can appear multiple times in the same set

I think you can achieve that using the Preindexed geoshape query. The bounding boxes need to exist in an index with the geo_shape type, and then you can create a search on your users index with a bool filter that combines your different boxes.

Maybe it can return all users in specific shape with Preindexed geoshape query , but I need distinct users that appear in both shapes at least once

That should be doable, you can add as many conditions as you need to a filter clause

e.g.
I have 2 disjoint(w/o common locations) areas with 5M requests inside each (as in the image above )
Area1, 5M requests and 200k unique usernames
Area2 , 5M requests and 300k unique usernames
And intersect (by username ) of two areas must return 150 unique usernames which appear in both areas.

If I understand Preindexed geoshape query can only return intersect locations.

my solution:

  1. find all unique users from the first area
POST /requests/_search
{
	"query": {
		"geo_bounding_box": {
			"ignore_unmapped": true,
			"location": {
				"bottom_right": {
					"lat": 5,
					"lon": 50
				},
				"top_left": {
					"lat": 30,
					"lon": 5
				}
			}
		}
	  
	},
		 "collapse": {
    "field": "username"
	},
  "_source": ["username"]
} 

  1. same find all unique users from second area
  2. find common usernames from response 1. and 2.

I would be glad if you have a more simple solution and can write an example query.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.