1

I've been able to load CSV having lat-long co-ordinates into leaflet using this useful solution. Looking for ways to cross-link co-ordinates from a different source and data from another. Describing the use case: Polygons representing wards in a city are loaded through geoJSON or KML or GPX file, each object having a unique name like "Ward 01". This file has the polygon co-ordinates of each unit, and its name, but not much else. Now, there is another file, a CSV, like this:

name,detail1,detail2,detail3,detail4,detail5
Ward 01,23,45,10,54,30
Ward 02,78,98,14,20,53

..and THIS has the details that we want to see in a popup when the user clicks on a polygon, or mouses over and we display on an info panel, etc etc. How does one combine the two? So far I've seen solutions where the location data and the details are all in the same file. Real-world cases aren't that simple ;)

The "name" field is the unique non-repeating connector here between the geoJSON/KML/GPX and the CSV.

An alternative is to merge this data sitting in the CSV into the file having the spatial information. Is there an automated way to do it? One hack is to convert the mapping data to a tabular row, merge the data in and convert it all back to geoJSON (or do the same with KML/GPX), but I'm a little vary of taking that complicated route, and it will hamper the occasional updates to data (by non-coders) that this use case requires. It would be a lot more efficient to just pull the details from the CSV for each object in the HTML itself.

Addendum : Yes, using MySQL DB instead of CSV is also acceptable though not preferrable (serverless, portable operation desirable). But please share how to go about it, because I still don't get how to pull it in.

Edit: One major hurdle I'm finding here is the asynchronous loading of the data. Most solutions I see have the entire code loaded into a file-loading script, to be called after the data has finished loading. But here, there's a main program with multiple overlay layers, and a CSV each to load for each layer.

Edit: Here's a link to the project, see source code: http://nikhilsheth.techydudes.net/files/pune-pb.html

  • 2
    JSON is the obvious solution seeing as JSON is essentially what would be called a Dictionary in other programming languages (associative array in Javascript). You could use the ward number as the key and add/retrieve other values via this key. I suspect you will have more pain doing this via CSV. – John Powell Apr 23 '15 at 14:21
  • Thanks @JohnBarça for the guidance! Papa.parse() helped convert the CSV to a JSON / javascript object, and then a .filter() command did the job. I've submitted the solution I got as an answer. – Nikhil VJ May 1 '15 at 6:26
1

Here is a script to turn your csv into a keyed object as suggested in the comments:

function csvJSON(csv){
  var lines=csv.split("\n");
  var result = {};
  var headers=lines[0].split(",");

  // start at 1 to skip the header row
  for(var i=1;i<lines.length;i++){
    var obj = {}; 
    var currentline=lines[i].split(",");

    var key = currentline[0];
    // start at 1 to skip the first column, which is your key
    for(var j=1;j<headers.length;j++){
      obj[headers[j]] = currentline[j];
    }
    result[key] = obj;
  }

  // return a js object
  return result;
}

// read your csv; will likely need a way to get this from a file in your application; just for demonstration here:
var csv = "name,detail1,detail2,detail3,detail4,detail5\nWard 01,23,45,10,54,30\nWard 02,78,98,14,20,53"

// construct the lookup object from the csv
var csvLookup = csvJSON(csv); 

With this in place, when constructing your popups you can do a simple lookup by key to get the value(s) of interest:

function onEachFeature(feature, layer) {
    // lookup the value from the csv for this polygon feature
    // assuming here key is in feature.properties.name = 'Ward01'
    var ward = feature.properties.name; // or whatever it really is
    var detail = csvLookup[ward]["detail1"];
    layer.bindPopup("<p> Detail1 for " + ward + ": " + detail + "</p>");
}
  • this looks great. But I wonder why I wasn't able to find a reliable way to import the contents of a CSV file as-is, as string! Everybody seems to be converting to javascript object / json. Finally I'm getting a workaround by using Papa.parse() and doing Papa.unparse() on its output data! The Papa.parse() function seems to be giving a pretty neat json/object. But's it's not nested by the name field as you've done, it's flat. So not sure how to go about looking it up. Trying to get the string into csv variable and trying your method now. – Nikhil VJ Apr 28 '15 at 6:11
  • Thanks toms for the invaluable guidance. The solution came about in a different way and I've posted it as a separate answer. – Nikhil VJ May 1 '15 at 6:23
0

Got it; followed the advice of going all-JSON. This thing went deep! The .filter() method of javascript arrays is the key element used. Steps:

1.First finish off all the other layers, overlays defining, map initiation, etc. Start after

L.control.layers(baseLayers, overlays, {collapsed: false}).addTo(map);

2.Use Papa.parse(csv) to import the CSV file as a JSON array.

Papa.parse("data/wardsPB.csv", {
    download: true,
    header: true,
    complete: function(results) { ...
    }
});

3.The stuff-we-want comes under results.data array, and looks like:

[{"name":"Ward 01","Building":"","Drainage":"8","Electrical":"4","Road":"16","SRA":"4","Water Supply":"8","Total":"40"},
{"name":"Ward 02","Building":"16","Drainage":"","Electrical":"","Road":"20","SRA":"4","Water Supply":"","Total":"40"},
...

4.Create the leaflet layer L.geoJson(.. from within the complete: function of Papa.parse.

5.Within that, define a function for onEachFeature in-line. We need this in here and not outside because it's going to need to refer to something in the results.data array.

            onEachFeature: function PBpopulate(feature, layer) {
                var popupText = "";
                var labelText = "";
                if (feature.properties && feature.properties.name) {
                    popupText += "<b>" + feature.properties.name.toString() + "</b>";
                    labelText = feature.properties.name.toString();
                    var filtered = results.data.filter(function(data){return data.name == this;}, feature.properties.name.toString());
                    popupText += "<br>" + JSON.stringify(filtered);
                    }
                if (feature.properties && feature.properties.description) {
                    popupText += "<br>" + feature.properties.description.toString();
                    }
                layer.bindPopup(popupText).bindLabel(labelText);
            }

6.The var filtered = ... line is key here.

var filtered = results.data.filter(function(data){return data.name == this;}, feature.properties.name.toString());
popupText += "<br>" + JSON.stringify(filtered);

This uses the filter() method of arrays. this stands in for the "name" value passed by the second argument, feature.properties.name.toString(). (found out about it here) It cycles through results.data array and returns only the part where the name in the CSV data matched the name of the geoJSON feature. In case of "Ward 01", filtered becomes :

[{"name":"Ward 01","Building":"","Drainage":"8","Electrical":"4","Road":"16","SRA":"4","Water Supply":"8","Total":"40"}]

7.For now I'm just using JSON.stringify() and pushing that array straight into the polygon's popup. will be working on dressing it up, putting in a panel, etc later.

8.So here's the full code block:

Papa.parse("data/wardsPB.csv", {
    download: true,
    header: true,
    complete: function(results) { //everything below runs only after the CSV has been loaded and processed.
        PuneElectoralWardsHolder = L.geoJson(PuneElectoralWards, { //defining the leaflet overlay layer that will house this data; polygons with location data is are already loaded in PuneElectoralWards variable.
                style: areastyle,
                onEachFeature: function PBpopulate(feature, layer) { //below code will be executed for each item in the layer
                    var popupText = "";
                    var labelText = "";
                    if (feature.properties && feature.properties.name) {
                        popupText += "<b>" + feature.properties.name.toString() + "</b>";
                        labelText = feature.properties.name.toString();
                        //Here it comes! The part where we look up the CSV data for a row having the same name as the map item's name
                        var filtered = results.data.filter(function(data){return data.name == this;}, feature.properties.name.toString());
                        console.log(JSON.stringify(filtered));
                        popupText += "<br>" + JSON.stringify(filtered);
                        }
                    if (feature.properties && feature.properties.description) {
                        popupText += "<br>" + feature.properties.description.toString();
                        }
                    layer.bindPopup(popupText).bindLabel(labelText);
                }   //closing onEachFeature function
            }).addTo(wards); //map layer created, added to "wards" overlay layer
    }   //Papa.parse's complete: section over
}); // Papa.parse function over.

9.See the full file here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.