# Kibana - intersection of two data sets on map

**URL:** <https://discuss.elastic.co/t/kibana-intersection-of-two-data-sets-on-map/277911>\
**Category:** Kibana\
**Tags:** maps, kql-kibana-query-language\
**Created:** [July 6, 2021, 6:36am UTC](https://discuss.elastic.co/t/kibana-intersection-of-two-data-sets-on-map/277911 "2021-07-06T06:36:05Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![leonidbionic](https://avatars.discourse-cdn.com/v4/letter/l/a4c791/32.png) [@leonidbionic](https://discuss.elastic.co/u/leonidbionic)\
**Post date:** [July 6, 2021, 6:36am UTC](https://discuss.elastic.co/t/kibana-intersection-of-two-data-sets-on-map/277911/1 "2021-07-06T06:36:05Z")

</div>

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 -

```auto
   "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

 ![kibana](https://us1.discourse-cdn.com/elastic/original/3X/5/2/525ad22a6d30564b1c1a37bf72b3aef3a5c9e411.jpeg)

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [July 6, 2021, 2:08pm UTC](https://discuss.elastic.co/t/kibana-intersection-of-two-data-sets-on-map/277911/2 "2021-07-06T14:08:17Z")

</div>

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.

> **[Geo-shape query | Elasticsearch Guide \[7.13\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-geo-shape-query.html#_pre_indexed_shape)**

---

<div class="post-metadata">

**Author:** ![leonidbionic](https://avatars.discourse-cdn.com/v4/letter/l/a4c791/32.png) [@leonidbionic](https://discuss.elastic.co/u/leonidbionic)\
**Post date:** [July 8, 2021, 9:03am UTC](https://discuss.elastic.co/t/kibana-intersection-of-two-data-sets-on-map/277911/3 "2021-07-08T09:03:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [July 8, 2021, 1:23pm UTC](https://discuss.elastic.co/t/kibana-intersection-of-two-data-sets-on-map/277911/4 "2021-07-08T13:23:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![leonidbionic](https://avatars.discourse-cdn.com/v4/letter/l/a4c791/32.png) [@leonidbionic](https://discuss.elastic.co/u/leonidbionic)\
**Post date:** [July 11, 2021, 9:34am UTC](https://discuss.elastic.co/t/kibana-intersection-of-two-data-sets-on-map/277911/5 "2021-07-11T09:34:38Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 8, 2021, 9:35am UTC](https://discuss.elastic.co/t/kibana-intersection-of-two-data-sets-on-map/277911/6 "2021-08-08T09:35:16Z")

</div>

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