Using Logstash JDBC connector for geo_shape datatype

Elastic Stack Logstash
1

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?

2

Yes.

You will need to set the mapping/template before, but just concatenate them into a single field.

3

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.

4

What does the document look like?

5

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.

6

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"
     }
  }
}
1 Like
7

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