Problem with Multiple Lat/Lon fields in a CSV


(Celtics 2018) #1

Hello,

I am trying to index a csv data set on Divvy bikeshare ride data. I want to be able to visualize the locations of the bike docks on a map, so both the location of the beginning of the rental and the location of the drop off point of the rental both have Lat/Long fields. i.e.:

FromStationID	FromLatitude	FromLongitude	FromStationName	
159	   41.9077     -87.685854     Claremont Ave & Hirsch St	

ToStationID	ToLatitude	ToLongitude	  ToStationName
69        41.90939601	-87.67769193	Damen Ave & Pierce Ave

However when loading this into Elasticsearch the second lat/long fields overwrite the first. Here is my config:

input {
  file {
    path => "/Users/Celtics/Desktop/63/divvyMaster.csv"
    start_position => "beginning"
    sincedb_path => "/dev/null"
  }
}

filter {
    csv{
      separator => ","
      columns =>     ["TripID","Date", "StartTime","EndTime","BikeID",
      "TripDuration","FromStationID","FromLatitude",
      "FromLongitude","FromStationName","ToStationID","ToLatitude","ToLongitude","ToStationName","UserType", "Gender", "BirthYear"]

    }
    date   {match => ["Date", "yyyy-MM-dd]"]}
    mutate {convert => ["TripID", "integer"]}
    mutate {convert => ["BikeID", "integer"]}
    mutate {convert => ["TripDuration", "integer"]}
    mutate {convert => ["BirthYear", "integer"]}
    mutate {convert => ["FromLatitude", "float"]}
    mutate {convert => ["FromLongitude", "float"]}
    mutate {convert => ["ToLatitude", "float"]}
    mutate {convert => ["ToLongitude", "float"]}

    mutate {rename =>  ["FromLatitude", "[location][lat]"]}
    mutate {rename =>  ["FromLongitude", "[location][lon]"]}
    mutate {rename =>  ["ToLatitude", "[location][lat]"]}
    mutate {rename =>  ["ToLongitude", "[location][lon]"]}

    

}



output {
  elasticsearch {
    hosts => "localhost"
    index => "divvy"
    document_type => "logs"
  }
  stdout{}
}

Does anyone have any insight as to what to do here to separate the two lat/long to be separate?

Thanks!


(Mark Walkom) #2

You need to use different field names here;

Maybe [from_location][lat], or similar.
As is it now, you just overwrite the From values.

I would suggest not doing that, just use the default.


(system) #3

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