Question

KML to CSV

  • 5 August 2014
  • 8 replies
  • 41 views
J
Hello,

 

I am trying a simple conversion from KML to csv. I need to write all data from ExtendedData element, it may be SimpleData or Data key/value. All I want is csv file with custom data from kml.

 

 

In a workbench I have

 

 

Generic reader which reads KML ( GENERIC Reader is requirement)

 

 

CSV Writer to write to csv

 

 

I have connected reader to writer directly no transformer and I expecting csv file. I yet to put CoordinateExtractor to get coords from file. So I am expexting only data to be written to csv. But when I run the workspace all I get is blank csv.

 

 

Here is a sample KML.

 

 

<kml xmlns="http://www.opengis.net/kml/2.2">

 

<Document>

 

  <name>My Golf Course Example</name>

 

  <Placemark>

 

    <name>Club house</name>

 

    <ExtendedData>

 

      <Data name="holeNumber">

 

        <value>1</value>

 

      </Data>

 

      <Data name="holeYardage">

 

        <value>234</value>

 

      </Data>

 

      <Data name="holePar">

 

        <value>4</value>

 

      </Data>

 

    </ExtendedData>

 

    <Point>

 

      <coordinates>-111.956,33.5043</coordinates>

 

    </Point>  </Placemark>

 

</Document>

 

</kml>

 

 

CSV should be

 

 

holeNumber,holeYardage,holePar,lat,lon

 

 

1,234,4,33.5043,-111.956

 

 

Is there I am doing wrong here?

8 replies

G
Badge +3
This piece of kml has data in PLacemark.

 

If u read it using a kml reader, expose the format attribute kml_data{} and explode this list and add the attributes to the csv writer, u will get the your golf_hole_stats. (you can keep the default settings from the csv reader)
takashi
Rank
Userlevel 2
Badge +17
Hi,

 

 

I looked at your sample data with Data Inspector. The attribute names and values are stored as hidden list attribute called "kml_data{}.name" and "kml_data{}.value".

 

 

 

So, you can expose them with the AttributeExposer, decompose the feature with the ListExploser, and then rename "value" to the value of "name" with the BulkAttributeRenamer.

 

Merge them back to the original feature, and expose the required attribute names, i.e."holeNumber", "holeYardage", and "holePar".

 

For example:

 

 

Takashi
J
Thanks Gio/Takashi. But I have to use GENERIC reader to read KML then how I can use AttributeExposer?
takashi
Rank
Userlevel 2
Badge +17
If the extension of the source data file name is "kml", there is no problem since the Generic reader can  recognize the format as KML. Otherwise, you will have to set explicitly the Input Format parameter of the reader to KML.

 

J
But schema is dynamic you might not get same attributes everytime. How to handle this?
takashi
Rank
Userlevel 2
Badge +17
If source dataset is always KLM format and also the formal schema is common to every source dataset, creating a custom format which can be used as a schema source could be a solution.

 

G
Badge +5
My Placemark tag on my kml is neither an extended data or has a stylesheet to use HTML to XHTML Converter transformer. How do I parse the kml_description.using the <br/> tags? 
 <Placemark> <name>Grand Bay</name> <description>9/16/2015 1:17:52 PM <br/>Status -  Normal <br/>2440-Grand Bay<br/> Height - 110 <br/>Received Messages - 2170<br/>Direct Messages - 2134<br/>Buddy Messages - 36<br/>Normal Noise - 14 <br/>ATT<br/>MeterID - Null <br/>Feeder - 27492 <br/>Switch - M519 <br/>IP Address - xxx.xxx.xx.xxx <br/>FirmWare Version - abcdeR12mn <br/>Timing Mode - BASIC SYNCHRONIZED <br/>Backup Power - N <br/>TGB Type - Standard Outdoor TGB w/ Argus/Alpha Supply <br/>Power Provider - APC <br/>Remote Reboot - N <br/>NC IP - 148.199.180.32 <br/>RX Channels - 10,2,6,14 <br/> PCC Installed - NO <br/> <![CDATA[  <a href="http://alxapap21/webstat/Default.aspx?opco=APC&id=2440&stat=rcvd_msgs_hr">Webstat</a><br/>]]> </description>\n<Style> <LabelStyle><color>ffffff</color><scale>.6</scale></LabelStyle> <IconStyle><scale>.3</scale><Icon><href>http://alxapap21/TGB_Dash/KMLimages/green_dot_gradient.png</href></Icon></IconStyle> </Style> <Point><extrude>1</extrude><altitudeMode>relativeToGround</altitudeMode><coordinates>-88.3154858,30.4725354,50</coordinates></Point> </Placemark>
 Thanks!

 

 
salvaleonrp
Rank
Badge +10
Hi,

 

 

I looked at your sample data with Data Inspector. The attribute names and values are stored as hidden list attribute called "kml_data{}.name" and "kml_data{}.value".

 

 

 

So, you can expose them with the AttributeExposer, decompose the feature with the ListExploser, and then rename "value" to the value of "name" with the BulkAttributeRenamer.

 

Merge them back to the original feature, and expose the required attribute names, i.e."holeNumber", "holeYardage", and "holePar".

 

For example:

 

 

Takashi

Working on a customer question, I found out that the Text To Find parameter for the BulkAttributeRenamer is not working anymore and needs to the changed from ".*" to ".+" for the translation to work.

 

Using an AttributeCreator in lieu of the BulkAttributeRenamer will also work, thanks to Andrea E. of Safe for this option and figuring out the Regex fix.

 

image

Reply


Community Stats

28,731
Posts
109,527
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.