topic Re: Extracting attributes from KML descriptions in Data Management Questions

Extracting attributes from KML descriptions

BruceHarold — Fri, 03 Mar 2017 17:50:35 GMT

Hello All

I am regularly asked how to extract the tabular data visible in KML description balloons in ArcGIS Earth into properly typed attributes that can be written to a Geodatabase feature class.

While KML does support a schema object that Data Interoperabilty's Quick Import geoprocessing tool would see and honour, often KML files are received that contain only HTML data in description objects, and the data is frustratingly close but inaccessible.

Data Interoperability is still the answer, but you need to author a Spatial ETL tool tailored to your data. First, inspect the KML features in Earth and decide what attributes you want and what data type they are. Unfortunately there is no magic wand to wave to help with this, the data is untyped in the KML description and you need to do this clerical review.

Usually only one Placemark geometry type is of interest, but if you need to convert multiple Placemark geometry types repeat the review for each one.

Then author a Spatial ETL tool with KML Reader and Geodatabase Writer, manually specifying each output feature class' geometry and attributes.

The data workflow between the Reader and Writer is this:

How to expose feature attributes from KML tag - FME Knowledge Center

You may need a GeometryFilter transformer after the Reader to filter Placemarks of interest, and if handling date fields, a DateFormatter to make the values writable to Geodatabase (say %x input, FME date output).

The attributes you expose will then be written as properly typed fields in a Geodatabase.

There is a related workflow to extract attributes from extended data - if anyone needs this message me in GeoNet.

Re: Extracting attributes from KML descriptions

DylanWarburg2 — Thu, 09 Nov 2017 20:55:56 GMT

I've been able to make this work using cursors and lists to split the PopupInfo xml field into useful values

1. use the arcpy KML to layer tool and Project tools to get into to the desired coordinate system (i ran into problems adding fields to the original conversion output, which may be due to the layer file associated with it)

2. Use .da.SearchCursor to get the PopupInfo string from the first row. Then split it into a list based on '<', delete the first two values (the label field from Google Earth) and put remaining values that have the 'td>' tag but not the 'td>' closing tag into a new list of field names (even indexes) and field values (odd indexes)

3. Loop through field names list with arcpy.AddField_management to add all fields (skip if they already exist)

4. Use .da.UpdateCursor to get PopupInfo's for all rows, then split and create new lists just like with search cursor

5. this time, use all the odd index values to update rows ( if i%2 <> 0: row[(i-1)/2] = list ) and then cursor.updateRow(row)

Dylan

Re: Extracting attributes from KML descriptions

KhalidMahmood — Fri, 02 Aug 2019 11:35:31 GMT

One of the easiest way, I have discovered and prepared a video (link below);

All Attributes Conversion, Google Earth (KML or KMZ) to Shapefile

https://youtu.be/_e_besxeAlY

Regards.

Khalid Mahmood

GIS Expert since 2008

kmc786pak@gmail.com

Re: Extracting attributes from KML descriptions

Anonymous User — Fri, 20 Sep 2019 18:06:51 GMT

Dylan, This is an extremely helpful script! Thank you for sharing with everyone else.

Re: Extracting attributes from KML descriptions

CourtneyWatson — Tue, 06 Sep 2022 23:42:17 GMT

Easiest way to do this is to open the KML is QGIS and export it as a shp., The attributes will be added to the table in the output.

Re: Extracting attributes from KML descriptions

DavidBranca — Thu, 26 Oct 2023 14:00:56 GMT

Hey All.
I stumbled on this and a few other threads after running into the same problem. I ended up making a python script with chatGPT that uses the "beautifulsoup" package (which i found was already installed in my instance) to scrape the HTML table from the layer attribute, in my case "Popupinfo", and creates a column for each row in the table. I then created a tool in arcgis using the script to make it easier to repeat this process. The tool/script takes two inputs, the layer name, and then a dropdown for the attribute that's holding the html table. The rest is magic. Here's the code I used in case anyone still has a need.

I forgot to mention, I first used the KML to layer tool in AGP to convert the KML to a feature layer, then ran this tool on the newly created layer. Also, some caveats, I was getting an error about fields not starting with a number. I had no need for those rows throwing an error so I had the script skip those. I also had to remove spaces for the same reason.

Hope this helps someone!

Re: Extracting attributes from KML descriptions

DavinShokes1 — Wed, 08 Nov 2023 19:42:04 GMT

Worked like a charm. Of course adding dozens of fields individually takes a bit of time. Thanks!

Re: Extracting attributes from KML descriptions

MereFyfe — Tue, 30 Jul 2024 20:22:07 GMT

For whatever reason, I wasn't able to make this work as a standalone script or as a custom geoprocessing tool. Lots of errors and ultimately it just gave me null values for every new field from the HTML popupinfo. I tried using Copilot to debug but no luck.

Re: Extracting attributes from KML descriptions

DavidBranca — Wed, 31 Jul 2024 14:18:43 GMT

Did you check to make sure the 'beautifulsoup' package was installed first?

Did you catch the piece where I converted the KML to a layer first?

Also i did this with ArcGIS Pro 3.x (unsure what the minor revision was at the time) in case that makes a difference in your case.

Re: Extracting attributes from KML descriptions

MattStarryACE — Wed, 11 Dec 2024 18:47:13 GMT

Yes, this seems to work. It puts the fields in random order it seems but it works. Thanks!