topic How to use a KML file's ExtendedData ? in App Development

How to use a KML file's ExtendedData ?

damienregad — Fri, 06 May 2016 08:40:21 GMT

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 ?

Anonymous — Tue, 27 Sep 2016 20:42:13 GMT

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 ?

damienregad — Wed, 28 Sep 2016 06:52:16 GMT

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

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

Fri, 30 Sep 2016 21:12:52 GMT

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.

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

Sun, 02 Oct 2016 16:33:53 GMT

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

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

Anonymous — Mon, 03 Oct 2016 17:45:39 GMT

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

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

Mon, 03 Oct 2016 21:14:42 GMT

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

}

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

damienregad — Tue, 04 Oct 2016 07:20:25 GMT

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

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

mall1m — Mon, 02 Jan 2017 14:34:43 GMT

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 ?

Anonymous — Fri, 25 Aug 2017 09:56:08 GMT

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!

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

mikegrattan — Thu, 17 May 2018 17:49:21 GMT

Hi Robert,

I'm interested in using your R script to reformat a KML (SHP) file in order to create columns for "Ranch", "Lot", and "Grower" from the attached file. Would you be able to help me revise your script so it will work with this file?

Thanks!

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

abhalla2 — Wed, 30 Jan 2019 08:44:53 GMT

Read .KML file using .xml format, you would information in various tables, just import the one you want.

Thanks!

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

colinfly88 — Fri, 01 Mar 2019 16:09:27 GMT

I believe this should be the correct answer (Abhalla2 provided) unless there is a better way. Reading the KML as an XML will allow you to access the additional data. You may need to add logic in your load script to transform/normalize the data, but I've never had issues going this route.

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

JossMatthias — Tue, 05 Dec 2023 13:11:39 GMT

More simple would it be to convert the kml to csv and then import both files and join them. Then you'll have all data with a simple script.