Ingesting Geospatial data with Logstash


(Freddie) #1

Basic logstash conf ...

input {
jdbc {
jdbc_driver_library => "services/ows/webapps/ows/WEB-INF/lib/postgresql-8.4-701.jdbc3.jar"
jdbc_driver_class => "org.postgresql.Driver"
jdbc_connection_string => "jdbc:postgresql://localhost:5432/testdb"
jdbc_user => "justme"
jdbc_password => "password"
statement => "select * from vw_test"
}
}

filter {
mutate { add_field => { "[@metadata][_index]" => "gmti_y2008-12_v1" } }
mutate { add_field => { "[@metadata][_type]" => "testidx" } }
mutate { remove_field => ["@version"] remove_field => ["@timestamp"] }
}

output {

We build the "new" elasticsearch index in the default cluster

elasticsearch {
cluster => "testdb"
document_type => "%{[@metadata][_type]}"
host => "localhost"
index => "%{[@metadata][_index]}"
manage_template => "true"
port => "9200"
protocol => "http"
template => "/usr/share/logstash-1.5.2/lib/logstash/outputs/elasticsearch/elasticsearch-template.json"
template_name => "testing"
template_overwrite => "true"
workers => "1"
}

file {
codec => "json"
path => "/tmp/debug-filters.json"
}
}

Basic mapping ...

"boundingArea": {
"type": "geo_shape",
"tree": "quadtree",
"precision": "1m"
},

for the geometry column "boundingArea" in postgresql, this GeoJson formatted syntax is built and defined as a string.

Sent from postgres: {"type":"Polygon","coordinates":[[[1.43875665801537,2.98127437512814],[1.43895665801537,2.98127437512814],
[1.43895665801537,2.98147437512814],[1.43875665801537,2.98147437512814],[1.43875665801537,2.98127437512814]]]}::text

but elasticsearch transforms into this:

"boundingArea":"{"type":"Polygon","coordinates":[[[1.43875665801537,2.98127437512814],[1.43895665801537,2.98127437512814],
[1.43895665801537,2.98147437512814],[1.43875665801537,2.98147437512814],[1.43875665801537,2.98127437512814]]]}"

and this syntax fails

Caused by: org.elasticsearch.index.mapper.MapperParsingException: failed to parse [boundingArea]
at org.elasticsearch.index.mapper.geo.GeoShapeFieldMapper.parse(GeoShapeFieldMapper.java:263)
at org.elasticsearch.index.mapper.object.ObjectMapper.serializeValue(ObjectMapper.java:706)
at org.elasticsearch.index.mapper.object.ObjectMapper.parse(ObjectMapper.java:497)
at org.elasticsearch.index.mapper.DocumentMapper.parse(DocumentMapper.java:544)
at org.elasticsearch.index.mapper.DocumentMapper.parse(DocumentMapper.java:493)
at org.elasticsearch.index.shard.IndexShard.prepareCreate(IndexShard.java:466)

"Shape must be an object consisting of type and coordinates"

The coordinates interpreted as a sting, encapsulated in double quotes, spawning the escape clauses is the problem.

Does anyone know how I can get this to work?

thanks


(Mark Walkom) #2

This appears to be a continuation of Correct method for geospatial mapping from postgres -> logstash -> Elasticsearch, it'll be easier if you keep it to one thread :slight_smile:


(Mark Walkom) #3