Extracting sea level pressure from KML using Shell

Question

I'm currently working on a little project; in a kml file called weatherdata.kml, I would like to extract the sea level pressure for each <Placemark> element. I'm trying to parse the information about the sea level pressure and put it into a file called report.csv; and print the sea level pressure on a new line each time.

I think this would work with awk and so far I've tried this:

 awk -F '[>,]' '/minSeaLevelPres/ {print $2}' report.csv

But when I run this command in shell, I get this:

1002</minSeaLevelPres
1002</minSeaLevelPres
1002</minSeaLevelPres
1001</minSeaLevelPres
1001</minSeaLevelPres
1001</minSeaLevelPres
1001</minSeaLevelPres
1001</minSeaLevelPres
1001</minSeaLevelPres
1001</minSeaLevelPres
1001</minSeaLevelPres
1002</minSeaLevelPres
1002</minSeaLevelPres
1003</minSeaLevelPres

when I want to get this:

1002
1002
1002
1001
1001
1001
1001
1001
1001
1001
1001
1002
1002
1003

I can't work out how to get rid of </minSeaLevelPres. Would anyone be able to help?

Below is an example of part of a placemark element in weatherdata.kml

 <Placemark>
        <styleUrl>#ex</styleUrl>
        <lat>19.2</lat>
        <lon>-24.1</lon>
        <stormName>NINE</stormName>
    <stormNum>10</stormNum>
    <basin>AL</basin>
        <stormType>LO</stormType>
        <intensity>20</intensity>
           <intensityMPH>23</intensityMPH>
           <intensityKPH>37</intensityKPH>
           <minSeaLevelPres>1002</minSeaLevelPres>
           <atcfdtg>2020082350</atcfdtg>
        <dtg>0000 UTC JAN 07</dtg>
       </Placemark>

Answer 1

I suggest to use a tool that can handle XML correctly:

xmlstarlet select --template --value-of '//minSeaLevelPres' -n weatherdata.kml

Output:

1002

See: xmlstarlet select --help

Answer 2

KML is an XML language. XML is not a language you can reliably parse with awk. You might be in luck with the files you have – they might be more reliably structured than what the language definition allows – but there's simply no reason to write your own restricted parser in AWK when you get one that works always, with different tools. Your stuff breaks when things remove or add line breaks, comments, for example.

I think you're simply trying to use the wrong tool. Just as likely as you have awk installed on your system is that you have e.g. python installed, and then you'd have an XML parser, and could, with no external code aside from Python's standard lib, write a really small program that writes your CSVs. (Remember, the UNIX philosophy is not "you have a hammer, now everything is a nail", but "you have tools for different purposes, find the right tool for your purpose").

import sys
import xml.etree.ElementTree as ElemTree

fname = sys.argv[1]
tree = ElemTree.parse(fname)
for placemark in tree.getroot().iter("Placemark"):
    print(placemark.find("minSeaLevelPres").text)

And that's it. Save to a file, give the file execution right (chmod o+x {filename}), and then you can run /path/to/filename input.kml.

General remark:

Because weatherdata.kml is such a large file,

I don't know what constitutes "large" for you, but if you end up writing a CSV with several million rows, you're not getting a very efficient data representation. Figure out which binary formats the consumer of this data supports, and write that directly. Chances are there's a Python library for that.

Answer 3

Like the others, I wouldn't recommend doing this with awk because you are working with XML. However, if you wanted to use it for some reason and your file was formatted in such a matter that awk would work or better yet, if it were a plain text file, then I'm only going to show exactly why the command that you have in your question wasn't working:

Your command has the field separator as either > or , as denoted by [>,]

awk -F '[>,]' '/minSeaLevelPres/ {print $2}' report.csv

What that means is that the second field is what comes after the first instance of either of those two characters which in your file is > and on the line(s) that contains the string minSeaLevelPres, {print $2} is going to give you exactly what you were getting:

1002</minSeaLevelPres>

In this specific case, if you just wanted to get 1002 from the sample text that you provided, what you would need is this

awk -F '[><]' '/minSeaLevelPres/ {print $3}' weatherdata.kml

That would set the field separator as > or < which would make 1002 and only 1002 the third field which would be printed from the command above and give you want you want:

1002

Again, I'm not recommending the use of awk on an XML, or for argument's sake, an HTML file, but I'm only providing this answer to show why your command wasn't working and what you would do to have it work if you were operating on a plain text file instead. You can reference this when you use awk in the future.

Answer 4

Using Raku (formerly known as Perl_6)

~$ raku -MXML -e 'my $xml=open-xml($*ARGFILES.Str); 
        .put for $xml.lookfor(:TAG<minSeaLevelPres>)>>.[0];' weatherdata.kml

Above is an answer coded in Raku, a member of the Perl-family of programming languages. You can load the Raku XML Module at the command line with the -MXML commandline flag. Then parse XML, using the lookfor (recursive element search) command. In the final step, >>.[0] or .map(*.[0]) maps into the tag, returning only the value contained within.

Note, it's unclear what your weatherdata.kml file actually looks like, whether it has one or all of Simple, Floating, and/or Extruded Placemarks. The command above simply looks for :TAG<minSeaLevelPres> recursively, outputting values one-per-line.

Sample Input, see:

https://developers.google.com/kml/documentation/KML_Samples.kml

Sample Output (change TAG above to :TAG<tessellate> to test):

1
0
1
1
1
1
1
1
1
1

https://github.com/raku-community-modules/XML
https://raku.org/