topic Retrieve Extended data from KML files. in App Development

Retrieve Extended data from KML files.

jcf — Sat, 16 Nov 2024 20:56:19 GMT

This is a solution rather than a question. In essence I built a load script that can be imported such that a user can simply pass the kml file location to a subroutine and create a table for extended data. It does not matter what the key:value pairs are or how many. The users load script should look like this:

//Include the sub routine file which includes the vShpKey f(x)
$(must_include=[lib://KMLParser.txt])

LOAD
cb_2017_us_county_20m.Name,
cb_2017_us_county_20m.Point,
cb_2017_us_county_20m.Area,
//you need to add this line to create a key back to the extended data
$(vShpKey(cb_2017_us_county_20m.Area)) as ShpKey,
cb_2017_us_county_20m.Line
FROM [lib://TIAA/cb_2017_us_county_20m.kml]
(kml, Table is [cb_2017_us_county_20m/cb_2017_us_county_20m]);

call KMLParse('[lib://cb_2017_us_county_20m.kml]');

I have attached the kmlParser file and the QVF. let me know if you have any additional questions. thanks

Re: Retrieve Extended data from KML files.

LZucco — Wed, 10 Jul 2019 17:35:27 GMT

Hi @jcf

Can you help me to retrieve LT_handle ExtendedData from my KML file?

Check attached file.

Thanks in advance + regards

Re: Retrieve Extended data from KML files.

LZucco — Wed, 10 Jul 2019 18:05:58 GMT

when changing GEOID to LT_handle inside KMLParser.txt

//create a key to join back to the kml file
Join(ExtndData)
LOAD
right(mid(description,index(description,'LT_Handle'),29),5) as LT_handle
left(replace("Polygon/outerBoundaryIs/LinearRing/coordinates",'0.0 ','')
,len(replace("Polygon/outerBoundaryIs/LinearRing/coordinates",'0.0 ',''))-4) as ShpKey
FROM $(vFileLoc)
(XmlSimple, table is [kml/Document/Folder/Placemark]);

That's the error I get (see attached image)

Unexpected token: 'left', expected one of: ',', 'AutoGenerate', 'From', 'From_Field', 'Inline', 'Resident', 'Extension', ...

Re: Retrieve Extended data from KML files.

QlikTom — Thu, 23 Apr 2020 18:30:27 GMT

Works like a charm

Edit:
Works, mostly like a charm. This method has challenges entries with more than one polygon. Any extended data associated with these such as the image below, will not be associated with the original KML load . This is because the KML connector will combine multiple geometries into one field.

That being said, it seems to only effect .4% of counties in the US, so a 99.6% hit rate, is pretty good.