Need help in importing location data to ES

Elastic Stack Elasticsearch
1

Folks, I am quite new to this and trying to understand fundamentals of importing location data to ES using logstash. Below are the specification:

a.) I have a CSV file with bunch of columns, one of which is location which has latitude and longitude (lat, -log) format.

b.) As I understand by default ES ingest the data using string but you can use "convert" to convert that to integer, float BUT you can simple cannot use convert to geo_point, right

After reading multiple posts I think I need to do a 4 step process:

a.) create an index in Kibana (dev tools), PUT
b.) Create a mapping field say location of type geo_point
c.) use logstash to ingest the data
d.) Data is in CSV format. When I ingest using logstash and define other columns, are they going to be mapped to the same index.

I tried that but not successful.

Is there a simple step by step instruction with samle data set, logstash configuration etc. That would really be helpful. Want an expert to write an article with the complete things..

Thanks

2

Some ideas.

With logstash

Without logstash:

HTH

3

Thank you so much @dadoonet. I followed your step with logstash and manage to upload data to elastic search and I also see that field location got created with data type "Geo-point" but when I try to create visualization it says no field with geo-point found. I also don't see the location field when try to get the field information from Kibana (Get /index)

    {
  "chicago_crime-1": {
    "aliases": {},
    "mappings": {
      "doc": {
        "properties": {
          "@timestamp": {
            "type": "date"
          },
          "@version": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "arrest": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "block": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "case number": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "community area": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "date": {
            "type": "date"
          },
          "description": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "district": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "domestic": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "host": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "id": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "iucr": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "location": {
            "properties": {
              "lat": {
                "type": "float"
              },
              "lon": {
                "type": "float"
              }
            }
          },
          "location-description": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "message": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "path": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "primary type": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "ward": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "year": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      }
    },
    "settings": {
      "index": {
        "creation_date": "1520548053306",
        "number_of_shards": "5",
        "number_of_replicas": "1",
        "uuid": "47wanPrwTP-2yKkcOLwKQA",
        "version": {
          "created": "6010199"
        },
        "provided_name": "chicago_crime-1"
      }
    }
  }
}

CONF FILE:

input {
        file {
     path => "/home/ppunj/chicago-crime.csv"
     start_position => 'beginning'
     sincedb_path => '/dev/null'
        }
 }
filter {
     csv  {
                separator => ","
                columns => ["id","case number","date","block","iucr","primary type","description","location-description","arrest","domestic","district","ward","community area","year","latitude","longitude"]
        }
        date {
                match => ["date", "MM/dd/yyyy HH:mm"]
                target => "date"
                locale => "en"
                 }
                mutate {
                        convert => ["latitude", "float"]
                        convert => ["longitude", "float"]
                        }
                        mutate {
                                rename => {
                                        "latitude" => "[location][lat]"
                                        "longitude" => "[location][lon]"
                                        }
                   }
                }
output {
        elasticsearch {
                         hosts => "http://localhost:9200"
                         index => "chicago_crime-1"
                        }
                stdout {}
     }
4

You need to change the mapping for location to become a geo_point type.
And reindex

5

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