How to use a KML file's ExtendedData ?

[damienregad]

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

[Anonymous]

Hi Damien,

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

Thanks

Janet

[damienregad]

Hello Janet,

No, I never did.

In the end I worked around the problem by extracting the structured data from the original GeoDatabase as a CSV file, and using the KML's placemark ID to match the object id in the CSV (via a calculated field).

Damien

[#4]

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.

[#5]

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, '",\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, '|', 😎 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]);

[Anonymous]

Thank you for your reply. I am going to try this out to see if it works. I’ll post how it goes so others have a record too.

Thanks again.

Janet

[#7]

I hope it works for you!  Also, I was testing on another file and just noticed there was a stray '#' in the optional code to generate the subfield lines, so that has been removed and the correct code there is now:

cat

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

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

}

[damienregad]

Thank you Robert. I think this script you provided may be helpful others..

I had never heard of "R" before, learn something new every day 🙂

I still find it amazing that Qlik is not able to do retrieve this structured data natively from the KML, forcing us to go through such hoops, but that's another story.

Cheers

Damien

[mall1m]

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])

;

[Anonymous]

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!