5 Replies Latest reply: Apr 11, 2017 12:27 PM by vyacheslav guzanov RSS

    KML File - UK Electoral Wards

    Tim Maher

      HI,

       

      I have created a UK polygon map in Qliksense which is based on Unitary Authority based on these boundaries :

       

      ua_areas.PNG

       

      I now need to take this down a level to Electoral wards.

       

      So, for example, the very south west would look like this :

       

      cornwall electoral wards.PNG

       

       

       

      I'm struggling to locate a KML file at this level. Does anyone have any advice or links to files at this level??

       

      Many many thanks!

       

      TJM

        • Re: KML File - UK Electoral Wards
          vyacheslav guzanov

          Hi,

           

          this is not related to Qlik, rather where to get geodata for your app.

           

          It looks, like you can find what you are looking for here https://data.gov.uk/dataset/counties-and-unitary-authorities-december-2015-full-extent-boundaries-in-england-and-wales

           

          Alternatively you can try Mapbox or ArcGIS and download vector maps from there (needs some dev experience)

           

          Though I would suggest to stay away from KML and move to point maps. Just my 2 cents, though.

            • Re: KML File - UK Electoral Wards
              Tim Maher

              Hi,

               

              Thanks for the link to this.  I'm new to the structure of KML files  the tags in this file seem to be separating the file into tables when I try and import it :

               

              kml_post_code_sectors.PNG

               

              It has exactly what I need but is this to be expected.  It is taking very long to load as well.

              Many thanks for your time.

               

              TJM

                • Re: KML File - UK Electoral Wards
                  vyacheslav guzanov

                  Again, i would suggest to use point maps.

                  There are really beautiful extensions on branch.

                  With CartoDB you can add time dimension to election results.

                  With Google maps you can use clustering, custom markers and HTML popups.

                  And Mapbox webgl based extension is simply beauty in itself...

                   

                  If you need the QS app to geocode your data (get lats and longs) - let me know. It takes csv with any addresses (even messy ones) and clears it thru Google Maps API.

                  You can use the app in Qlik directly (then Google will be the provider of coordinates), or  you can google the Geo Python library (then you can choose from many providers, including Mapbox, which is used by Qlik itself to geocode the data).

                  Getting coords _seems_ like some overhead over what you can do with kml or with geocoding, embedded in Tableau.

                   

                  But in reality, when you have Moscow, Moskow, Moskva, Moskwa, Mooskva in your dataset and you need to normalize it, you will certainly appreciate the combined power of Google and Qlik

                   

                   

                  If this helpful, would appreciate the like or "helpful" answer. Thanks.

                    • Re: KML File - UK Electoral Wards
                      Tim Maher

                      hi, thanks for your reply.  Can you share the app to geocode my data?

                       

                      Thanks

                        • Re: KML File - UK Electoral Wards
                          vyacheslav guzanov

                          Put the code in new or existing app.

                          Create vFile and vKey expressions.

                           

                          //Populate temporary table with distinct address values

                          //These can be postcodes, street addresses or city names

                          //**Be careful of ambiguous address information as if unsure, Google will guess**

                          // create expression vFile - and add full path to csv file with addresses. Header is Address.

                          // create expression vKey and paste your Google API, which is enabled for Maps.

                           

                           

                          tmp:

                          LOAD

                            Address as tmpAddress

                          FROM

                          [$(vFile)]

                          (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

                           

                           

                          //Loop through address values, polling Google Maps for latitude and longitude info

                          for vRow = 0 to NoOfRows('tmp') -1

                           

                           

                            let vAddress = peek('tmpAddress', vRow, 'tmp');

                           

                           

                            //Latest API call update 02/03/2016

                            Addresses:

                            LOAD

                            '$(vAddress)' as Address,

                            status,

                               [result/type] as type,

                               [result/formatted_address] as formatted_address,

                               [result/place_id] as place_id,

                               [result/geometry/location_type] as location_type,

                               [result/geometry/viewport/northeast/lat] as [northeast/lat],

                               [result/geometry/viewport/northeast/lng] as [northeast/lng],

                               [result/geometry/viewport/southwest/lat] as [southwest/lat],

                               [result/geometry/viewport/southwest/lng] as [southwest/lng],

                               [result/geometry/location/lat] as [location/lat],

                               [result/geometry/location/lng] as [location/lng]

                              

                            FROM [https://maps.googleapis.com/maps/api/geocode/xml?address=$(vAddress)&key=$(vKey)] (XmlSimple, Table is [GeocodeResponse]);

                           

                           

                          next

                           

                           

                          //Drop temporary table

                          DROP Table tmp;