9 Replies Latest reply: Aug 25, 2017 5:56 AM by Aushik Makdoom RSS

    How to use a KML file's ExtendedData ?

    Damien Regad

      I have a KML file which contains structured extended data (Schema / SimpleField) linked to each Placemark (polygon), looking like this:

       

      <SchemaData schemaUrl="#kml_schema_ft_test">
          <SimpleData name="OBJECTID">37</SimpleData>
          <SimpleData name="NOM">Le Renfort</SimpleData>
          <SimpleData name="NO_COM_FEDERAL">6604</SimpleData>
          <SimpleData name="NO_COMM">4</SimpleData>
          <SimpleData name="CODE_SECTEUR">00</SimpleData>
          <SimpleData name="SECT_VILLE"> </SimpleData>
          <SimpleData name="NUMERO">0400050</SimpleData>
          <SimpleData name="CODE_SOUS_SECT">050</SimpleData>
      </SchemaData>
      
      

       

      See attached sample file.

       

      I would like to use this extended data in QlikSense, but when I import the KML file, it only recognizes 3 fields:

       

      • sample.Name (the placemark's name)
      • sample.Point (always empty - where is this coming from ?)
      • sample.Area (this is the polygon defined in my file, i.e. the shape I want to use)

       

      The related data fields are nowhere to be found.

       

      Is it possible to retrieve the KML extended data in QlikSense ? How ?

       

      Thanks in advance for your help.

      Damien

        • Re: How to use a KML file's ExtendedData ?
          Janet Kremer

          Hi Damien,

          Did you ever get an answer to this question. I'm having the same issue.

          Thanks

          Janet

          • Re: How to use a KML file's ExtendedData ?
            Robert Klein

            I had to figure out something like this today and came up with a manual solution with Excel that, while fairly tedious (especially as the number of attributes grows), got the job done.  The idea is to create flags (used the HTML tags in the KML in this case) in consecutive Excel columns to ultimately pull the desired attribute up to the <name></name> line that Qlik Sense will import by default and copy over all the other original lines, only replacing the <name></name> line with the extended attributes stored there in a delimited string.  Copy the revised KML from the last Excel column into a text editor and save as KML.  You can then use subfield() in the load script to extract the attributes.  I would definitely recommend double-checking the Excel file before re-using because some of the formulas need to start one row below the reference row, others start where the first flag equals 1 and then copy zeros above.extended_attributes.png

              • Re: How to use a KML file's ExtendedData ?
                Robert Klein

                To minimize potential issues with the manual Excel approach, here's a script in R that will generate the delimited string with the extended attribute values in the <name></name> line and create a new KML file.  There is probably a way to do all of this within Qlik Sense itself, but that would be beyond my skill level.  This example uses the 2014 KML file (link) for Ohio census tracts from the US Census Bureau.


                folder_path <- "//Users//myname//Downloads//cb_2014_39_tract_500k"

                original_kml_file_name <- "cb_2014_39_tract_500k.kml"

                new_kml_file_name <- "kml_fix.kml"

                 

                #Find these attributes in the <th></th> rows by looking at the original KML in a text editor

                attributes <- c(

                "STATEFP",

                "COUNTYFP",

                "TRACTCE",

                "AFFGEOID",

                "GEOID",

                "NAME",

                "LSAD",

                "ALAND",

                "AWATER"

                )

                 

                #####################

                 

                setwd(folder_path)

                txt <- readLines(original_kml_file_name)

                txt.df <- data.frame("kml_txt"=txt)

                txt.df$row_num <- as.numeric(rownames(txt.df))

                 

                attributes_tags <- unlist(lapply(attributes, function(x) paste0("<th>", x, "</th>")))

                matches <- txt.df[txt.df$kml_txt %in% attributes_tags, ]

                matches$attribute_value_rows <- matches$row_num + 1

                 

                #Drop column before merge

                matches$row_num <- NULL

                 

                vals <- merge(x=txt.df, y=matches, by.x='row_num', by.y='attribute_value_rows')

                vals$kml_txt.x <- gsub("<td>|</td>", "", vals$kml_txt.x)

                vals$group_num <- unlist(lapply(1:nrow(vals[vals$kml_txt.y == paste0('<th>', attributes[1], '</th>'), ]),

                  function(i) rep(i, length(attributes))

                  ))

                val_list <- data.frame("dlm"=unique(ave(vals$kml_txt.x, vals$group_num, FUN = function(x)

                  paste0('<name>', paste(x,collapse = "|"),'</name>')))

                  )

                val_list$row_num <- as.numeric(rownames(val_list))

                 

                placemark_rows <- txt.df[substr(txt.df$kml_txt,1,6) == '<Place', ]

                placemark_rows$name_row <- placemark_rows$row_num + 1

                 

                vals_with_target_row <- data.frame(cbind(placemark_rows$name_row, as.character(val_list$dlm)))

                colnames(vals_with_target_row) <- c("name_row", "dlm")

                 

                txt.df.temp <- merge(x=txt.df, y=vals_with_target_row, by.x='row_num', by.y='name_row', all.x=T)

                txt.df.temp$final <- ifelse(is.na(txt.df.temp$dlm) == T, as.character(txt.df.temp$kml_txt), as.character(txt.df.temp$dlm))


                #This is the KML file to import into Qlik Sense

                write(txt.df.temp$final, file=new_kml_file_name)

                 

                ###################

                #Can also generate the repetitive subfield lines for the LOAD script to extract individual attribute values into columns

                 

                #cat

                #for (i in 1:length(attributes)){

                # cat(paste0("subfield(", gsub(".kml", "", new_kml_file_name), ".Name, '|', ", i, ') as "', attributes[i], '",\n'))

                #}

                 

                #oh_kml:

                #LOAD

                # subfield(kml_fix.Name, '|', 1) as "STATEFP",

                # subfield(kml_fix.Name, '|', 2) as "COUNTYFP",

                # subfield(kml_fix.Name, '|', 3) as "TRACTCE",

                # subfield(kml_fix.Name, '|', 4) as "AFFGEOID",

                # subfield(kml_fix.Name, '|', 5) as "GEOID",

                # subfield(kml_fix.Name, '|', 6) as "NAME",

                # subfield(kml_fix.Name, '|', 7) as "LSAD",

                # subfield(kml_fix.Name, '|', 8) as "ALAND",

                # subfield(kml_fix.Name, '|', 9) as "AWATER",

                # kml_fix.Area as area

                #FROM [lib://mylib/kml_fix.kml]

                #(kml, Table is [cb_2014_39_tract_500k/cb_2014_39_tract_500k]);

              • Re: How to use a KML file's ExtendedData ?
                Nicolas Aimain

                in my case, I solved it using autonumber()

                 

                    Departements.Name, ==> field generated by QlikSense in the following format p## (ex: p1, p2, p3)

                    Departements.Area ==> geometry field

                 

                ## in p## is an autonumber of field Polygon/outerBoundaryIs/LinearRing/coordinates

                 

                I created a new table from the KML getting the link between Key_Placemark and Departements.Name

                 

                Placemark:

                LOAD

                    'p'&autonumber([Polygon/outerBoundaryIs/LinearRing/coordinates]) as Departements.Name,

                    %Key_Placemark_AA84FB6C10E27BB6 as Key_Placemark

                FROM [lib://Downloads/Departements.kml]

                (XmlSimple, table is [kml/Document/Placemark])

                ;

                 

                Then with Key_Placemark, you are able to get any data from ExtendedData table in the KML

                ExtendedData:

                LOAD value as Code_Departement,

                     %Key_Placemark_AA84FB6C10E27BB6 as Key_Placemark

                FROM [lib://Downloads/Departements.kml]

                (XmlSimple, table is [kml/Document/Placemark/ExtendedData/Data])

                ;

                • Re: How to use a KML file's ExtendedData ?
                  Aushik Makdoom

                  Dear All,

                   

                  I have created a custom KML and I have built a  area map in Qlik Sense that simulates the flight seat map. It works fine, but the label of the seat does not get displayed in the map. Can you please help with this ?

                   

                  I have opened a separate thread for the same below.

                   

                   

                  https://community.qlik.com/message/1330881#1330881

                   

                  Any help is highly appreciated!

                   

                  Thanks!