# Using Logstash JDBC connector for geo\_shape datatype
**URL:** https://discuss.elastic.co/t/using-logstash-jdbc-connector-for-geo-shape-datatype/80767
**Category:** Logstash
**Created:** [March 31, 2017, 1:09am UTC](https://discuss.elastic.co/t/using-logstash-jdbc-connector-for-geo-shape-datatype/80767 "2017-03-31T01:09:15Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Rory](https://discuss.elastic.co/u/Rory)
#### Post date: [March 31, 2017, 1:09am UTC](https://discuss.elastic.co/t/using-logstash-jdbc-connector-for-geo-shape-datatype/80767/1 "2017-03-31T01:09:15Z")
Can we use the Logstash JDBC connector to store data into geo\_shape datatype field? The sql statement outputs 2 columns [Latitude] and [Longitude], how do we use these columns to create a geo\_shape field and Index into ElasticSearch?
---
### Author:  [@warkolm](https://discuss.elastic.co/u/warkolm)
#### Post date: [March 31, 2017, 4:40am UTC](https://discuss.elastic.co/t/using-logstash-jdbc-connector-for-geo-shape-datatype/80767/2 "2017-03-31T04:40:18Z")
Yes.
You will need to set the mapping/template before, but just concatenate them into a single field.
---
### Author:  [@Rory](https://discuss.elastic.co/u/Rory)
#### Post date: [March 31, 2017, 1:22pm UTC](https://discuss.elastic.co/t/using-logstash-jdbc-connector-for-geo-shape-datatype/80767/3 "2017-03-31T13:22:52Z")
> [@warkolm](#):
>
> You will need to set the mapping/template before, but just concatenate them into a single field.
Thank you for the reply. This is how my mapping for the geo\_shape attribute looks like:
```
"CurrentLocation": {
"type": "geo_shape",
"tree": "quadtree",
"precision": "1.0m",
"points_only": true
}
```
Inside the sql select statement, I am concatenating the Latitude and Longitude fields as follows:
SELECT
Concat([Longitude],',',[Latitude]) as [CurrentLocation]
FROM Table
Doing this gives me the following logstash error:
"error"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"failed to parse [CurrentLocation]", "caused\_by"=\>{"type"=\>"parse\_exception", "reason"=\>"shape must be an object consisting of type and coordinates"}}}}}
Is there something in the SELECT statement that I need to change?
Thanks for the help.
---
### Author:  [@warkolm](https://discuss.elastic.co/u/warkolm)
#### Post date: [March 31, 2017, 9:55pm UTC](https://discuss.elastic.co/t/using-logstash-jdbc-connector-for-geo-shape-datatype/80767/4 "2017-03-31T21:55:14Z")
What does the document look like?
---
### Author:  [@Rory](https://discuss.elastic.co/u/Rory)
#### Post date: [March 31, 2017, 10:44pm UTC](https://discuss.elastic.co/t/using-logstash-jdbc-connector-for-geo-shape-datatype/80767/5 "2017-03-31T22:44:07Z")
This is how I defined the Index:
PUT testindexgeoshape
{
"mappings": {
"testingtype": {
"properties": {
"userid": {
"type": "keyword"
},
"currentlocation": {
"type": "geo\_shape",
"tree": "quadtree",
"precision": "1.0m",
"points\_only":true
}
}
}
}
}
The table in SQL Server also has 3 columns UserID, Latitude, Longitude
The select statement inside logstash jdbc looks like:
Select [UserID] as userid,
Concat([Longitude],',',[Latitude]) as [currentlocation]
FROM UserDB.dbo.UserLocations
Longitude and Latitude are stored as float in the SQL table
This is a sample row in the table:
2455896807, 37.383251, -121.834505
Concat([Longitude],',',[Latitude]) as [currentlocation] this statement works if I change the ElasticSearch datatype to geo\_point instead of geo\_shape...
I believe I am concatenating the Longitude and Latitude fields in an incorrect manner for geo\_shape datatype in ElasticSearch in the SQL Select statement.
Thanks.
---
### Author:  [@Rory](https://discuss.elastic.co/u/Rory)
#### Post date: [April 3, 2017, 2:16pm UTC](https://discuss.elastic.co/t/using-logstash-jdbc-connector-for-geo-shape-datatype/80767/6 "2017-04-03T14:16:27Z")
I found a solution by using the mutate filter in Logstash. This is what it looks like:
```
if [latitude] and [longitude] {
mutate {
add_field => {
"[currentlocation][coordinates]" => ["%{longitude}", "%{latitude}"]
"[currentlocation][type]" => "point"
}
}
}
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [May 1, 2017, 2:16pm UTC](https://discuss.elastic.co/t/using-logstash-jdbc-connector-for-geo-shape-datatype/80767/7 "2017-05-01T14:16:29Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.