# Need some help with Geo Enrichment - SOLVED

**URL:** <https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340>\
**Category:** Logstash\
**Created:** [February 21, 2016, 5:16pm UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340 "2016-02-21T17:16:34Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [February 21, 2016, 5:16pm UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/1 "2016-02-21T17:16:34Z")

</div>

Hi All,

I have a logstash configuration which is getting sales activity from a JDBC connection.  
This data has a locatorID.

My configuration looks like:  
`input {  
jdbc {  
jdbc\_connection\_string =\> "jdbcstring"  
jdbc\_user =\> "user"  
jdbc\_password =\> "password"  
jdbc\_driver\_library =\> "/Users/sqljdbc4.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
statement =\> "SELECT \* from SALES where PURCHASE\_DATE\_ET = '2016-02-19' and STANDARD\_AMOUNT \> 0"  
}  
}

```
output {
	stdout { codec => json_lines }    

	elasticsearch {
        index => "purchases"
        document_type => "purchase"

    }
}

```

`

I then have a separate file with with a reference to lookup the locatorID to a log & lat configuration. The CSV file is like this:

1,"Goroka","Goroka","Papua New Guinea","GKA","AYGA",-6.081689,145.391881,5282,10,"U","Pacific/Port\_Moresby"

Where the format is:

ID, City, Name, Country, Ref1, Ref2, Latitude,Longitude, Alt,Timezone, Offset,TzTimezone.

How do I join and enrich the two steps prior to loading into Elastic?

Thanks  
Xathras`indent preformatted text by 4 spaces`

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [February 21, 2016, 7:54pm UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/2 "2016-02-21T19:54:04Z")

</div>

Check out the translate filter - [https://www.elastic.co/guide/en/logstash/2.1/plugins-filters-translate.html](https://www.elastic.co/guide/en/logstash/2.1/plugins-filters-translate.html)

You can use that to pull the lat+long in and then merge into a single geo array.

---

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [February 21, 2016, 10:05pm UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/3 "2016-02-21T22:05:22Z")

</div>

Thanks for the help. I am getting further but now have some issues with the translation

I am now getting the following error:  
Settings: Default pipeline workers: 8  
The error reported is:  
LogStash::Filters::Translate: Bad Syntax in dictionary file geocord.yaml

## The geocord file looks like:

- ICAO: AYGA  
GEO: -6.081689,145.391881
- ICAO: AYMD  
GEO: -5.207083,145.7887
- ICAO: AYMH  
GEO: -5.826789,144.295861

My filter and translate section now includes:  
`filter{ translate { field => "destination_airport_code" destination=> "ICAO" dictionary_path => "geocord.yaml" } }`

Any ideas?

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [February 22, 2016, 1:13am UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/4 "2016-02-22T01:13:18Z")

</div>

Your file is not valid, it should be `"AYGA": -6.081689,145.39188`.  
See the example here [https://www.elastic.co/guide/en/logstash/2.1/plugins-filters-translate.html#plugins-filters-translate-dictionary\_path](https://www.elastic.co/guide/en/logstash/2.1/plugins-filters-translate.html#plugins-filters-translate-dictionary_path)

---

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [February 22, 2016, 1:24am UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/5 "2016-02-22T01:24:56Z")

</div>

Thanks, but I tried that and it didn't work so went to a yaml generator to see if that was the problem. here is an example of the file:

"AYGA":-6.081689,145.391881  
"AYMD":-5.207083,145.7887  
"AYMH":-5.826789,144.295861

---

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [February 22, 2016, 1:43am UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/6 "2016-02-22T01:43:32Z")

</div>

Ugh, I found it. My bad look at the simple difference:

"KORD": "41.978603,-87.904842"

Thank you so much for the gentle nudge

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [February 22, 2016, 8:02pm UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/8 "2016-02-22T20:02:24Z")

</div>

You should either create a template or a mapping for the index that sets this field as a geopoint, there's a bunch of threads that can help, but shout out if you get stuck.

---

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [February 23, 2016, 2:04am UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/9 "2016-02-23T02:04:55Z")

</div>

Really need some help warkolm, banging my head on this one ☹

Here is what I've been able to do thus far:

1. Add in a filter mutate to split airport by the ',' so we now have an array from the string containing the latitude and longitude
2. Add in a filter mutate to add the fields called latitude and longitude defined as airport[0] and airport[1]
3. Then add in a filter mutate to convert them from string which is what natively is happening into a float
4. In filter mutate name the longitude and latitude as: [location][lon] & [location][lat]
5. In my output section I define manage\_template as true and pass in the template location and template\_override = true

When I run I get the following error: "status"=\>400, "error"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"Failed to parse mapping [_default_]: Mapping definition for [location] has unsupported parameters: [dynamic : true]", "caused\_by"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"Mapping definition for [location] has unsupported parameters: [dynamic : true]"}}}}, :level=\>:warn}

Here is the config  
input {  
jdbc {  
jdbc\_connection\_string =\> "jdbc:sqlserver://host:1433;Database=db"  
jdbc\_user =\> "user"  
jdbc\_password =\> "password"  
jdbc\_driver\_library =\> "/Users/wtaylor/Downloads/sqljdbc\_4.0/enu/sqljdbc4.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
statement =\> "SELECT top 5 \* from SALES where PURCHASE\_DATE\_ET = '2016-02-22' and STANDARD\_AMOUNT \> 0"  
}  
}

filter {  
translate {  
field =\> "destination\_airport\_code"  
dictionary\_path =\> "/Users/wtaylor/Downloads/logstash-2.2.2/bin/geocord.yaml"  
fallback =\> "unknown"  
destination=\> "airport"  
}  
}

filter {  
mutate {  
split =\> {"airport" =\> ","}  
}  
}

filter {  
mutate {  
add\_field =\> ["latitude","%{[airport[0]}"]  
add\_field =\> ["longitude","%{[airport[1]}"]  
}  
}

filter {  
mutate {  
convert =\> { "longitude" =\> "float" }  
convert =\> { "latitude" =\> "float" }  
}  
}

filter{  
mutate {  
rename =\> {  
"longitude" =\> "[location][lon]"  
"latitude" =\> "[location][lat]"  
}  
}  
}

output {  
stdout { codec =\> json\_lines }

elasticsearch {  
index =\> "bre"  
document\_type =\> "purchase"  
manage\_template =\> true  
template =\> "/Users/wtaylor/Downloads/logstash-2.2.2/bin/template.json"  
template\_overwrite=\>"true"  
}  
}

My Template Json looks like:  
{  
"template" : "bre",  
"settings" : {  
"index.refresh\_interval" : "5s"  
},  
"mappings" : {  
"_default_" : {  
"\_all" : {"enabled" : true, "omit\_norms" : true},  
"properties" : {  
"@timestamp": { "type": "date", "doc\_values" : true },  
"@version": { "type": "string", "index": "not\_analyzed", "doc\_values" : true },  
"location" : {  
"type" : "geo\_point",  
"dynamic": true,  
"doc\_values" : true,  
"lat\_lon": true  
},  
"geoip" : {  
"type" : "object",  
"dynamic": true,  
"properties" : {  
"ip": { "type": "ip", "doc\_values" : true },  
"latitude" : { "type" : "float", "doc\_values" : true },  
"longitude" : { "type" : "float", "doc\_values" : true }  
}  
}

```
  }
}

```

}  
}

---

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [February 23, 2016, 4:35pm UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/10 "2016-02-23T16:35:56Z")

</div>

I fixed it 🙂

need to make some amendments to my template but was able to get some fields with Geo working

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [February 23, 2016, 6:28pm UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/11 "2016-02-23T18:28:53Z")

</div>

> [@Wayne\_Taylor](#):
>
> "dynamic": true,

That's not valid, so remove it from both `location` and `geoip` and you should be good.

---

<div class="post-metadata">

**Author:** ![Wayne\_Taylor](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wayne_taylor/32/45984_2.png) [@Wayne\_Taylor](https://discuss.elastic.co/u/Wayne_Taylor)\
**Post date:** [February 23, 2016, 7:24pm UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/12 "2016-02-23T19:24:44Z")

</div>

warkolm, thanks so much for all your support. Per my note above i was able to solve and as you mentioned that the change I made.

Working on some weird items on my map showing points on a heat map that I know isn't in the source but

---

<div class="post-metadata">

**Author:** ![alaviamir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alaviamir/32/10167_2.png) [@alaviamir](https://discuss.elastic.co/u/alaviamir)\
**Post date:** [June 27, 2016, 1:28pm UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/13 "2016-06-27T13:28:33Z")

</div>

How did you get it to work? It would help if you post your solution.

---

<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:** [July 6, 2017, 4:50am UTC](https://discuss.elastic.co/t/need-some-help-with-geo-enrichment-solved/42340/14 "2017-07-06T04:50:48Z")

</div>