Extracting data from CSV file (fusion table and kml workaround)

Question

In Xamarin google maps for Android using C# you can create polygons like so based on this tutorial:

    public void OnMapReady(GoogleMap googleMap)
    {
        mMap = googleMap;
        PolylineOptions geometry = new PolylineOptions()
        .Add(new LatLng(37.35, -37.0123))
        .Add(new LatLng(37.35, -37.0123))
        .Add(new LatLng(37.35, -37.0123));


        Polyline polyline = mMap.AddPolyline(geometry);

    }

However I have downloaded a CSV file from my Fusion Table Layer from google maps as I think this might be the easiest option to work with polygon/polyline data. The output looks like this:

    description,name,label,geometry
    ,Highland,61,"<Polygon><outerBoundaryIs><LinearRing><coordinates>-5.657018,57.3352 -5.656396,57.334463 -5.655076,57.334556 -5.653439,57.334477 -5.652366,57.334724 -5.650064,57.334477 -5.648096,57.335082 -5.646846,57.335388 -5.644733,57.335539 -5.643309,57.335428 -5.641981,57.335448 -5.640451,57.33578 -5.633217,57.339118 -5.627278,57.338921 -5.617161,57.337649 -5.607948,57.341015 -5.595812,57.343583 -5.586043,57.345373 -5.583581,57.350648 -5.576851,57.353609 -5.570088,57.354017 -5.560732,57.354102 -5.555254,57.354033 -5.549713,57.353146 -5.547766,57.352275 -5.538932,57.352255 -5.525891,57.356217 -5.514888,57.361865 -5.504272,57.366027 -5.494515,57.374515 -5.469829,57.383765 -5.458661,57.389781 -5.453695,57.395033 -5.454057,57.402943 -5.449189,57.40731 -5.440583,57.411447 -5.436133,57.414616 -5.438312,57.415474 -5.438628,57.417955 -5.440956,57.417909 -5.444013,57.414976 -5.450778,57.421362 -5.455035,57.422333 -5.462081,57.420719 -5.468775,57.416975 -5.475205,57.41135 -5.475976,57.409117 -5.47705,57.407092 -5.478101,57.406056 -5.478901,57.40536 -5.479489,57.404534 -5.480051,57.403782 -5.481036,57.403107 -5.484538,57.402102 -5.485647,57.401856 -5.487358,57.401287 -5.488709,57.400962 -5.490175,57.400616 -5.491116,57.400176 -5.493832,57.399318 -5.495279,57.399134 -5.496726,57.39771 -5.498724,57.396836 -5.49974,57.396314 -5.501317,57.39627 -5.502869,57.395426</coordinates></LinearRing></innerBoundaryIs></Polygon>"
    ,Strathclyde,63,"<Polygon><outerBoundaryIs><LinearRing><coordinates>-5.603129,56.313564 -5.603163,56.312536 -5.603643,56.311794 -5.601467,56.311875 -5.601038,56.312481 -5.600697,56.313489 -5.60071,56.31535 -5.60159,56.316107 -5.600729,56.316598 -5.598625,56.316058 -5.596203,56.317477 -5.597024,56.318119 -5.596095,56.318739 -5.595432,56.320116 -5.589343,56.322469 -5.584888,56.325178 -5.582907,56.327169 -5.581414,56.327472 -5.581435,56.326663 -5.582355,56.325602 -5.581515,56.323891 -5.576993,56.331062 -5.57886,56.331475 -5.57676,56.334449 -5.572748,56.335689 -5.569012,56.338143 -5.564802,56.342113 -5.555237,56.346668 -5.551214,56.347448 -5.547651,56.346391 -5.54444,56.344945 -5.541247,56.345945 -5.539099,56.349674 -5.533874,56.34763 -5.525195,56.342888 -5.523518,56.345066 -5.52345,56.346605 -5.526417,56.354361 -5.535455,56.353681 -5.537463,56.35508 -5.536035,56.356271 -5.538923,56.357205 -5.53891,56.359336 -5.539952,56.361491 -5.538102,56.36372 -5.535934,56.36567 -5.53392,56.367705 -5.531369,56.369729 -5.529853,56.371022 -5.532371,56.371274 -5.534177,56.371708 -5.532846,56.373256 -5.529845,56.37496 -5.527675,56.375327 -5.528531,56.375995 -5.526732,56.376343 -5.525442,56.377809 -5.524739,56.379843 -5.526069,56.380561</coordinates></LinearRing></innerBoundaryIs></Polygon>"

I uploaded a KML file to Google Maps Fusion Table Layer, it then created the map. I then went File>Download>CSV and it gave me the above example.

I have added this csv file to my assets folder of my xamarin android google map app and my question would be because LatLng takes two doubles as its input, is there a way I could input the above data from the csv file into this method and if so how?

Not sure how to read the above csv and then extract the <coordinates> and then add those coordinates as new LatLng in the example code above?

If you notice however the coordinates are split into lat and lng and then the next latlng is seperated by a space -5.657018,57.3352 -5.656396,57.334463.

Sudo code (this may or may not require xamarin or android experience and may just require C#/Linq):

Read CSV var sr = new StreamReader(Read csv from Asset folder);
Remove description,name,label,geometry
Foreach line in CSV
  Extract Item that contains double qoutes
     Foreach Item Remove Qoutes and <Polygon><outerBoundaryIs><LinearRing><coordinates> from start and end
         Foreach item seperated by a space Extract coordinates
       (This will now leave a long list of 37.35,-37.0123 coordinates for each line)
        Place in something like this maybe?:

    public class Row
    {
        public double Lat { get; set; }
        public double Lng { get; set; }

        public Row(string str)
        {
            string[] separator = { "," };
            var arr = str.Split(separator, StringSplitOptions.None);
            Lat = Convert.ToDouble(arr[0]);
            Lng = Convert.ToDouble(arr[1]);
        }
    }


    private void OnMapReady()
    var rows = new List<Row>();

      Foreach name/new line
         PolylineOptions geometry = new PolylineOptions()
           ForEach (item in rows) //not sure how polyline options will take a foreach
            .Add(New LatLng(item.Lat, item.Lng))
         Polyline polyline = mMap.AddPolyline(geometry);

As there is no way of using Fusion Table Layers in Xamarin Android with Google Maps API v2 this may provide a quick and easier workaround for those that need to split maps into regions.

Answer 1

If I understand correctly, the question is how to parse the above CSV file.

Each line (except the first one with headers) can be represented with the following class:

class MapEntry
{
    public string Description { get; set; }
    public string Name { get; set; }
    public string Label { get; set; }
    public IEnumerable<LatLng> InnerCoordinates { get; set; }
    public IEnumerable<LatLng> OuterCoordinates { get; set; }
}

Note the Inner and Outer coordinates. They are represented inside the XML by outerBoundaryIs (required) and innerBoundaryIs (optional) elements.

A side note: the Highland line in your post is incorrect - you seem to trimmed part of the line, leading to incorrect XML (<outerBoundaryIs>...</innerBoundaryIs>).

Here is the code that does the parsing:

static IEnumerable<MapEntry> ParseMap(string csvFile)
{
    return from line in File.ReadLines(csvFile).Skip(1)
           let tokens = line.Split(new[] { ',' }, 4)
           let xmlToken = tokens[3]
           let xmlText = xmlToken.Substring(1, xmlToken.Length - 2)
           let xmlRoot = XElement.Parse(xmlText)
           select new MapEntry
           {
               Description = tokens[0],
               Name = tokens[1],
               Label = tokens[2],
               InnerCoordinates = GetCoordinates(xmlRoot.Element("innerBoundaryIs")),
               OuterCoordinates = GetCoordinates(xmlRoot.Element("outerBoundaryIs")),
           };
}

static IEnumerable<LatLng> GetCoordinates(XElement node)
{
    if (node == null) return Enumerable.Empty<LatLng>();
    var element = node.Element("LinearRing").Element("coordinates");
    return from token in element.Value.Split(' ')
           let values = token.Split(',')
           select new LatLng(XmlConvert.ToDouble(values[0]), XmlConvert.ToDouble(values[1]));
}

I think the code is self explanatory. The only details to be mentioned are:

let tokens = line.Split(new[] { ',' }, 4)

Here we use the string.Split overload that allows us to specify the maximum number of substrings to return, thus avoiding the trap of processing the commas inside the XML token.

and:

let xmlText = xmlToken.Substring(1, xmlToken.Length - 2)

which strips the quotes from the XML token.

Finally, a sample usage for your case:

foreach (var entry in ParseMap(csv_file_full_path))
{
    PolylineOptions geometry = new PolylineOptions()
    foreach (var item in entry.OuterCoordinates)
        geometry.Add(item)
    Polyline polyline = mMap.AddPolyline(geometry);
}

UPDATE: To make Xamarin happy (as mentioned in the comments), replace the File.ReadLines call with a call to the following helper:

static IEnumerable<string> ReadLines(string path)
{
    var sr = new StreamReader(Assets.Open(path));
    try
    {
        string line;
        while ((line = sr.ReadLine()) != null)
            yield return line; 
    }
    finally { sr.Dispose(); }
}