Need help in importing location data to ES


(Prakash Punj) #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


(David Pilato) #2

Some ideas.

With logstash

Without logstash:

HTH


(Prakash Punj) #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 {}
     }

(David Pilato) #4

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


(system) #5

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