google maps, fusion tables and markers

Question

I have searched everywhere in the documentation to explain how I can show only markers for a given area of a fusion table.

At the moment all markers appear on the map like so:

Fusion Table Google Maps

JSFiddle (note jsfiddle wont load the uri from website so markers wont show)

If you click on an area of the fusion table/google map I get the area name in a pop up as expected, however I dont want to show any of the markers initially. When an area of the fusion table/map is clicked I want it to show the markers for that given area only, not the whole map.

This is how I add the markers to the map from my Web Api:

         var uri = 'http://mountainsandweather.azurewebsites.net/api/Mountains';

            $(document).ready(function () {
                //Get web api json data
                $.getJSON(uri)
                    .done(function (data) {
                        // On success, 'data' contains a list of mountains.
                        $.each(data, function (key, item) {
                            // Add a list item for the mountain.
                            $('<li>', { text: formatItem(item) }).appendTo($('#mountains'));

                            //Put seperate data fields into one variable 
                            var latLng = new google.maps.LatLng(item.Latitude, item.Longitude);

                            //Add info window to each marker
                            var infowindow = new google.maps.InfoWindow({
                                content: formatItemInfoWindow(item)
                            });


                            // Creating a marker and putting it on the map
                            var marker = new google.maps.Marker({
                                position: latLng,
                                title: formatItemInfoWindow(item.Name),
                                infowindow: infowindow
                            });
                            marker.setMap(map);
                            google.maps.event.addListener(marker, 'click', function () {
                                //this.infowindow.close(); //not working correctly info windows still show
                                this.infowindow.open(map, marker);

                            });      
                        });
                    });
            });
            function formatItemInfoWindow(item) {
                return item.Name + '<br />' + item.Height_ft + '<br />' + item.humidity + '<br />' + item.snowCover + '<br />' + item.temperature;
            }
            function formatItem(item) {
                return item.Latitude +', '+ item.Longitude;
            }
        }

I did see in the documentation a where statement that can be added to the fusion tables. Like so:

 var layer = new google.maps.FusionTablesLayer({
                    query: {
                        select: 'geometry',
                        from: '11RJmSNdTr7uC867rr2zyzNQ6AiE1hcREmGFTlvH3'
                        where: //not sure if I could use this or what to put.
                    },

However the data from the Web Api is not segmented into specific areas it is simply one long list of Latitudes and Longitudes. Like so:

<Mountain>
<Height_ft>2999</Height_ft>
<Height_m>914</Height_m>
<ID>c1</ID>
<Latitude>57.588007</Latitude>
<Longitude>-5.5233564</Longitude>
<Name>Beinn Dearg</Name>
<humidity>0.81</humidity>
<snowCover>4.99</snowCover>
<temperature>63</temperature>
</Mountain>

Does google have anything in the way of mixing fusion table geometry with coordinates? A simple way of displaying all markers for a given area? Or can anyone think of a way this could be done?

Some extra details about the webapi incase it is needed:

    private MountainContext db = new MountainContext();

    // GET: api/Mountains
    public IQueryable<Mountain> GetMountains()
    {
        return db.Mountains;
    }

    // GET: api/Mountains/5
    [ResponseType(typeof(Mountain))]
    public IHttpActionResult GetMountain(string id)
    {
        Mountain mountain = db.Mountains.Find(id);
        if (mountain == null)
        {
            return NotFound();
        }

        return Ok(mountain);
    }
    public IQueryable<Mountain> GetMountainByName(string name)
    {

        return db.Mountains.Where(n => string.Equals(n.Name, name));
    }

Answer 1

Unfortunately there is no containsLocation function in FusionTablesLayer.

One solution is to create Google Maps Polygon from the FusionTablesLayer allowing us to use containsLocation to determine whether or not to add the marker to the map.

First we need the coordinates to create the polygon. We can use google.visualization.Query to grab the coordinates of the selected area from fusion table:

function getMountainPolygonFromFusionTable(label) {
    // Return a new promise.
    return new Promise(function(resolve, reject) {

    var sql = encodeURIComponent("SELECT 'geometry' FROM 11RJmSNdTr7uC867rr2zyzNQ6AiE1hcREmGFTlvH3 WHERE label ='" + label + "'");
    var query = new google.visualization.Query('http://www.google.com/fusiontables/gvizdata?tq=' + sql);

    query.send(function (response) {
        var data = response.getDataTable().getValue(0, 0);
        // Create a XML parser
        if (window.DOMParser) {
            var parser = new DOMParser();
            var kml = parser.parseFromString(data, "text/xml");
        } else { 
            var kml = new ActiveXObject("Microsoft.XMLDOM");
            kml.loadXML(data);
        }

        // Get the coordinates of Mountain Areas
        var latLngs = kml.getElementsByTagName("coordinates")[0].childNodes[0].nodeValue.split(' ');

        var mountainPolygonLatLngs = [];
        for (var i = 0; i < latLngs.length; i++) {
            var latLng = latLngs[i].split(',');
            mountainPolygonLatLngs.push(new google.maps.LatLng(latLng[1], latLng[0]));
        }

        // Create the polygon
        mountainPolygons = new google.maps.Polygon({
            paths: mountainPolygonLatLngs,
            fillColor: 'transparent',
            strokeColor : 'transparent'
        });

        resolve(mountainPolygons);

    });

  });
}

Then we just loop through the array of mountains and check to see if the selected area contains the mountain:

// On mountain area click
google.maps.event.addListener(layer, 'click', function(event) {

    // Clear all markers
    while(markers.length) { 
      markers.pop().setMap(null); 
    }

    // Get Polygon from FustionTable
    getMountainPolygonFromFusionTable(event.row.label.value).then(function(mountainPolygons) {

    // Loop through the mountains
    for(var i = 0; i < mountains.length; i++) {

        // Get the mountain LatLng
        var mountain = mountains[i];
        var mountainLat = mountain.getElementsByTagName("Latitude")[0].childNodes[0].nodeValue;
        var mountainLng = mountain.getElementsByTagName("Longitude")[0].childNodes[0].nodeValue;
        var mountainLatLng = new google.maps.LatLng(mountainLat, mountainLng);

        // If mountain is in the selected polygon, create a marker for it.
        if (google.maps.geometry.poly.containsLocation(mountainLatLng, mountainPolygons)) {

            // @todo set infowindow, title...
            var marker = new google.maps.Marker({
                position: mountainLatLng,
                title: 'Marker info here',
            });
            marker.setMap(map);
            markers.push(marker);

        }
    }

  });
}); 

Here's the JSON version:

google.maps.event.addListener(layer, 'click', function(event) {

    // Clear all markers
    while(markers.length) { 
        markers.pop().setMap(null); 
    }

    // Get Polygone from FustionTable
    getMountainPolygonFromFusionTable(event.row.label.value).then(function(mountainPolygons) {

        $.getJSON(uri).done(function (data) {

            // On success, 'data' contains a list of mountains.
            $.each(data, function (key, item) {

                //Put seperate data fields into one variable 
                var latLng = new google.maps.LatLng(item.Latitude, item.Longitude);

                if (google.maps.geometry.poly.containsLocation(latLng, mountainPolygons)) {

                    // @todo set infowindow, title...
                    var marker = new google.maps.Marker({
                        position: latLng,
                        title: 'Marker info here',
                    });
                    marker.setMap(map);
                    markers.push(marker);

                }

            });

        });
    });
}); 

Here's the Fiddle - XML

Here's the Fiddle - JSON

And here's what the JSON API might look like