Search code examples
cssjsongoogle-maps-api-3google-maps-markersxls

Google Maps Markers - lat, long, size and color from Excel

I am trying to reproduce something that I have created in Google Earth/KML but have problems in Google Maps due to my inexperience with JS.

I want to load around 1000 markers using lat, long, size and color data from XLS.

I have seen an example of how to control market position and size using data from a JSON source,

I have also seen an example of an import from XLS to a JS array.

In the XLS file the data is in columns: Lat, Long, Size, Color and Label. The color can be expressed in CSS. The label would be the basis of a tooltip.

Any suggestions welcome.

Many thanks

Steve

Solution

  • I have done this several times. There are a few options...

    1. Have a look at Google Fusion Tables - these are really easy to use and lightning fast - able to deal with 500,000 points.

    2. For 1,000 points, I would export the Excel data to a CSV file, then convert the CSV to a Javacript array, and embed the array and some Javascript into your webpage. I am providing a Perl script that will convert the CSV you get out of Excel into a Javascript array - it may not be perfect but you will get the idea. Then I am providing some Javascript that creates the markers, which may also not be perfect, but you will get the idea.

    So, this converts a CSV file into Javascript array:

    #!/usr/bin/perl

use strict;
use warnings;

my $printcomma=0;
print "var Points = new Array(\n";

while (<>) {

    # Trim end of line character
    chomp;

    my $line = $_;

    # Define variables to hold each CSV field
    my ($lat,$lon,$size,$color,$label) = split (',',$line);

    print ",\n" if $printcomma;

    print "{lat:'$lat',lon:'$lon',size:'$size',color:'$color',label:'$label'}";
    $printcomma = 1;
END
}
print ");\n";

    You save it as "csv2js", then you go to a Terminal and type:

    chmod +x csv2js
./csv2js < yourCSVfile  > points.js

    Assuming your CSV file looks something like this:

    53.1,0.002,100,255,Some label
52.7,0.011,1000,200,Another label
55.89,-0.78,10000,128,Yet another label

    it will create a file that looks something like this:

    var Points = new Array(
{lat:'53.1',lon:'0.002',size:'100',color:'255',label:'Some label'},
{lat:'52.7',lon:'0.011',size:'1000',color:'200',label:'Another label'},
{lat:'55.89',lon:'-0.78',size:'10000',color:'128',label:'Yet another label'});

    Then you inclue the file created above (points.js) into the top of the following file and use that as the basis of your HTML webpage:

    <HTML>
<head>

<!-- Pull in Javascript version of Excel CSV -->
<script src="points.js"></script>

<!-- A bunch of your own stuff -->

<script type="text/javascript">
////////////////////////////////////////////////////////////////////////////////
// This function is called dynamically to build the Infowindow as it pops up as
// a result of the user clicking a pin on the map.
////////////////////////////////////////////////////////////////////////////////
function DeriveInfowindowContent(i){

    // Build the wording for the pop-up Infowindow, one piece at a time;
    var t1 = "<h4>"  + Points[i].label +  "</h4>";
    return t1;
}
////////////////////////////////////////////////////////////////////////////////
// Function called when webpage first loads
////////////////////////////////////////////////////////////////////////////////
function initialize() {

    ////////////////////////////////////////////////////////////////////////////////
    // Next line sets the latitude/longitude of map centre at startup. Use tools at
    // http://www.getlatlon.com to find a nice, new map centre's coordinates.
    ////////////////////////////////////////////////////////////////////////////////
    var myMapCentre = new google.maps.LatLng(51.50,-0.135);

    ////////////////////////////////////////////////////////////////////////////////
    // "zoom" sets the zoom (or scale) of the map
    // Use any value between 0-20, 12 seems about right to me.
    // Smaller numbers mean you see a larger area of ground in your browser.
    ////////////////////////////////////////////////////////////////////////////////
    var myOptions = {
        zoom: 12,
        center: myMapCentre,
        mapTypeId: google.maps.MapTypeId.ROADMAP
    }

    var map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
    gmap = map; // Store in global var gmap

    // Just use one Infowindow and set its content each time before use, this 
    // saves memory and means old infowindow closes when user opens a new one.
    var Infowindow = new google.maps.InfoWindow();

    // Go through plotting all points on map
    for(var i=0;i<Points.length;i++){

       Points[i].LatLon     = new google.maps.LatLng(Points[i].lat,Points[i].lon);
           Points[i].Marker     = new google.maps.Marker({position:Points[i].LatLon,map:map});
       Points[i].Marker.index = i;

           google.maps.event.addListener(Points[i].Marker, 'click', function() {
                    var s=DeriveInfowindowContent(this.index);
                    Infowindow.setContent(s);
                    Infowindow.open(map,this);});
    }
}
</script>
</head>
<body onload="initialize()">

    Note that this code doesn't do anything with the "color" or the "size" parameters in your Excel spreadsheet, but I guess you can work those bits out yourself now you have a basic structure.

    UPDATE: Since doing this I have found a nice Javascript library that will parse your Excel-produced CSV file into an array for you, which means you won't need my Perl above. See Evan's answer to this question.