# I want to take Lat Long from MYSQL database and map as geopoint in elasticsearch index

**URL:** <https://discuss.elastic.co/t/i-want-to-take-lat-long-from-mysql-database-and-map-as-geopoint-in-elasticsearch-index/194060>\
**Category:** Logstash\
**Created:** [August 6, 2019, 4:34pm UTC](https://discuss.elastic.co/t/i-want-to-take-lat-long-from-mysql-database-and-map-as-geopoint-in-elasticsearch-index/194060 "2019-08-06T16:34:06Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sandeep\_Bind](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sandeep_bind/32/47746_2.png) [@Sandeep\_Bind](https://discuss.elastic.co/u/Sandeep_Bind)\
**Post date:** [August 6, 2019, 4:34pm UTC](https://discuss.elastic.co/t/i-want-to-take-lat-long-from-mysql-database-and-map-as-geopoint-in-elasticsearch-index/194060/1 "2019-08-06T16:34:06Z")

</div>

Below is my logstash configuration file.

input {  
jdbc {  
jdbc\_driver\_library =\> "mysql-connector-java-8.0.16/mysql-connector-java-8.0.16.jar"  
jdbc\_driver\_class =\> "com.mysql.cj.jdbc.Driver"  
jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/VAMT\_NX"  
jdbc\_user =\> ""  
jdbc\_password =\> ""  
jdbc\_paging\_enabled =\> true  
tracking\_column =\> "unix\_ts\_in\_secs"  
use\_column\_value =\> true  
tracking\_column\_type =\> "numeric"  
schedule =\> "\*/5 \* \* \* \* \*"  
statement =\> "SELECT t1.id,t3.name as entity,t1.name as asset\_code,t1.serial as serial,t1.date\_mod,t4.name as branch,concat(t4.latitude,',',t4.longitude) as location,t5.name as domain,  
t6.name as computer\_model,t7.name as computer\_type,t8.name as manufacturer,t9.name user,t1.date\_creation,t1.uniqueqrcode,  
t2.macaddressfield,t2.ipaddressfield,t2.defaultgatewayfield,t2.subnetmaskfield,t2.dnsentryfield,t2.employeenumberfield,  
t2.employeenamefield,t2.employeecontactfield,t2.employeedesignationfield,t10.name as cpu\_make,t11.name as cpu\_speed,  
t12.name as hard\_disk,t12.name as ram,t14.name as service\_type,t15.name as os\_name,t16.name as os\_bit,t17.name as asset\_status,  
t18.name as status\_remarks,t1.is\_deleted,UNIX\_TIMESTAMP(t1.date\_mod) AS unix\_ts\_in\_secs FROM glpi\_computers t1  
LEFT JOIN glpi\_plugin\_fields\_computercomputers t2  
ON t1.id=t2.items\_id  
LEFT JOIN glpi\_entities t3  
ON t1.entities\_id=t3.id  
LEFT JOIN glpi\_locations t4  
ON t1.locations\_id=t4.id  
LEFT JOIN glpi\_domains t5  
ON t1.domains\_id=t5.id  
LEFT JOIN glpi\_computermodels t6  
ON t1.computermodels\_id=t6.id  
LEFT JOIN glpi\_computertypes t7  
ON t1.computertypes\_id=t7.id  
LEFT JOIN glpi\_manufacturers t8  
ON t1.manufacturers\_id=t8.id  
LEFT JOIN glpi\_users t9  
ON t1.users\_id=t9.id  
LEFT JOIN glpi\_plugin\_fields\_cpumakefielddropdowns t10  
ON t2.plugin\_fields\_cpumakefielddropdowns\_id=t10.id  
LEFT JOIN glpi\_plugin\_fields\_cpuspeedfielddropdowns t11  
ON t2.plugin\_fields\_cpuspeedfielddropdowns\_id=t11.id  
LEFT JOIN glpi\_plugin\_fields\_harddiskfielddropdowns t12  
ON t2.plugin\_fields\_harddiskfielddropdowns\_id=t12.id  
LEFT JOIN glpi\_plugin\_fields\_ramfielddropdowns t13  
ON t2.plugin\_fields\_ramfielddropdowns\_id=t13.id  
LEFT JOIN glpi\_plugin\_fields\_servicetypefielddropdowns t14  
ON t2.plugin\_fields\_servicetypefielddropdowns\_id=t14.id  
LEFT JOIN glpi\_plugin\_fields\_osnamefielddropdowns t15  
ON t2.plugin\_fields\_osnamefielddropdowns\_id=t15.id  
LEFT JOIN glpi\_plugin\_fields\_osbitfielddropdowns t16  
ON t2.plugin\_fields\_osbitfielddropdowns\_id=t16.id  
LEFT JOIN glpi\_plugin\_fields\_assetstatusfielddropdowns t17  
ON t2.plugin\_fields\_assetstatusfielddropdowns\_id=t17.id  
LEFT JOIN glpi\_plugin\_fields\_statusremarkfielddropdowns t18  
ON t2.plugin\_fields\_statusremarkfielddropdowns\_id=t18.id  
WHERE (UNIX\_TIMESTAMP(t1.date\_mod) \> :sql\_last\_value AND t1.date\_mod \< NOW()) ORDER BY t1.date\_mod ASC

"  
}  
}  
filter {  
mutate {  
copy =\> { "id" =\> "[@metadata][\_id]"}  
remove\_field =\> ["id", "@version", "unix\_ts\_in\_secs"]  
}  
}  
output {

# stdout { codec =\> "rubydebug"}

elasticsearch {  
index =\> "nx\_logs"  
document\_id =\> "%{[@metadata][\_id]}"  
}  
}

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [August 6, 2019, 4:47pm UTC](https://discuss.elastic.co/t/i-want-to-take-lat-long-from-mysql-database-and-map-as-geopoint-in-elasticsearch-index/194060/2 "2019-08-06T16:47:33Z")

</div>

> [@Sandeep\_Bind](#):
>
> concat(t4.latitude,',',t4.longitude) as location

So what do you end up with in the location field?

You are going to need a [mapping](https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-put-mapping.html). [This](https://discuss.elastic.co/t/elk-and-mysql-geopoint/192888) thread might help but I have not tested what I suggested there.

---

<div class="post-metadata">

**Author:** ![Sandeep\_Bind](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sandeep_bind/32/47746_2.png) [@Sandeep\_Bind](https://discuss.elastic.co/u/Sandeep_Bind)\
**Post date:** [August 6, 2019, 4:48pm UTC](https://discuss.elastic.co/t/i-want-to-take-lat-long-from-mysql-database-and-map-as-geopoint-in-elasticsearch-index/194060/3 "2019-08-06T16:48:58Z")

</div>

Once i run this logstash file, i am not able to map the location field as geo\_point.

---

<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:** [September 3, 2019, 4:49pm UTC](https://discuss.elastic.co/t/i-want-to-take-lat-long-from-mysql-database-and-map-as-geopoint-in-elasticsearch-index/194060/4 "2019-09-03T16:49:11Z")

</div>

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