#!/usr/bin/env ruby

#
# This program produces files readable for Google Earth, containing GPS data of sampling stations.
#
# 2009-06-02                   Michael Matschiner <michaelmatschiner@mac.com>
#
# run this script with a table produced by the below SQL query. Copy the part between
# =begin and =end into the Command window of Sequel Pro (replace 'ICEFISH' with any other field_trip
# names) and press 'Run All'. Then use the Sequel Pro menu to export the results table in CSV format
# (File > Export > Custom Query Result > CSV file... ).
=begin
SELECT station.name, station.latitude, station.longitude FROM station INNER JOIN field_trip ON station.field_trip_id = field_trip.id
WHERE field_trip.name = "ICEFISH"
=end
# Run this script by typing
# 'ruby google_earth_exporter.rb input_filename'
# in the Terminal



# Class "Array2" is used to build a two-dimensional matrix which stores
# station names and coordinates as given in the CSV file.
#
class Array2
  def initialize
    @store = [[]]
  end
  def [](a,b)
    if @store[a]==nil ||
      @store[a][b]==nil
      return nil
    else
      return @store[a][b]
    end
  end
  def []=(a,b,x)
    @store[a] = [] if @store[a]==nil
    @store[a][b] = x
  end
end


# The main program starts here. A number of default values is set. See below for dimX.
progName = "google_earth_exporter.rb"


# The input file is the first command line argument.
fileIn = ARGV[0]


# The input file is read as a single string. Line endings are detected,
# and used to split the string into an array of strings.
#
str = IO.read( fileIn )
ary = str.split(/\n/) if str.include?("\n")

# The header line is deleted
#
ary.shift

# The dimensions of the table is detected.
#
rowNum = ary.size
colNum = 3
inquotes = false

mat = Array2.new
rowNum.times do |r|
  tmpstr = ary[r]
  commapositions = []
  tmpstr.length.times do |l|
    if tmpstr[l..l] == '"'
      if inquotes == false
        inquotes = true
      else
        inquotes = false
      end
    elsif tmpstr[l..l] == ","
      commapositions << l if inquotes == false
    end
  end
  mat[r,0] = tmpstr[0..commapositions[0]-1].delete '"'
  mat[r,1] = tmpstr[commapositions[0]+1..commapositions[1]-1]
  mat[r,2] = tmpstr[commapositions[1]+1..tmpstr.length].chomp
end



# The kml output is written.
#
kmlOutput = String.new
kmlOutput << "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"
kmlOutput << "<kml xmlns=\"http://www.opengis.net/kml/2.2\" xmlns:gx=\"http://www.google.com/kml/ext/2.2\" xmlns:kml=\"http://www.opengis.net/kml/2.2\" xmlns:atom=\"http://www.w3.org/2005/Atom\">\n"
kmlOutput << "<Document>\n"
kmlOutput << "\t<Folder>\n"
kmlOutput << "\t\t<name>database export</name>\n"
kmlOutput << "\t\t<open>1</open>\n"
rowNum.times do |r|
  kmlOutput << "\t\t<Placemark>\n"
  kmlOutput << "\t\t<name>#{mat[r,0]}</name>\n"
  kmlOutput << "\t\t<Point>\n"
  kmlOutput << "\t\t\t<coordinates>#{mat[r,2]},#{mat[r,1]},0</coordinates>\n"
  kmlOutput << "\t\t</Point>\n"
  kmlOutput << "\t\t</Placemark>\n"
end
kmlOutput << "\t</Folder>\n"
kmlOutput << "</Document>\n"
kmlOutput << "</kml>\n"



# The string "kmlOutput" is written to a new file with the same name as the input file,
# but with "_kml" added to the name.
#
fileOutName = fileIn.scan(/\w+./)[0].chomp(".") + ".kml"
fileOut = File.new(fileOutName, "w")
fileOut.print kmlOutput.to_s
puts "Google Earth file written to \"#{fileOutName}\""