SitePoint Sponsor

# Thread: Convert values and load CSV to MDB

1. ## Convert values and load CSV to MDB

This is my first Ruby Script, so I apolgize if it is cobbled together.
I have a number of GPS transmitters that send there updated position during the day. I am capturing this data and saving it to a csv file.
Information details Time, Date, Station Name, Coordinates (X, Y, Z)

The Script I wrote takes the CSV date loads it into a database and saves certain parts for plotting via Google Maps.

The issue I am having is converting the X, Y, Z coordinates to Lat long.
I created the following script which works when you manually enter the data into the first three lines
X = 837408.3500
Y = -4828031.6767
Z = 4069551.1998

pi = 3.14159265358979323846264338327950288

a = 6378137
f = 1/298.257223563
b = a*(1-f)
e2 = 2*f-f**2
ep2 = f*(2-f)/((1-f)**2)
r2 = X**2+Y**2
r = Math.sqrt(r2)
E2 = a**2 - b**2
F = 54*b**2*Z**2
G = r2 + (1-e2)*Z**2-e2*E2
c = (e2*e2*F*r2)/(G*G*G)
s = (1+c+Math.sqrt(c**2+2*c))**(1/3)
P = F/(3*(s+1/s+1)**2*G*G)
Q = Math.sqrt(1+2*e2*e2*P)
r0 = -(e2*P*r)/(1+Q)+Math.sqrt((a*a/2)*(1+1/Q)-((1-e2)*P*Z**2)/(Q*(1+Q))-P*r2/2)
tmp = (r-e2*r0)**2
U = Math.sqrt(tmp+Z**2)
V = Math.sqrt(tmp+(1-e2)*Z**2)
z0 = (b**2*Z) / (a*V)

altitude = U*(1-b**2/(a*V))
latitude = Math.atan((Z+ep2*z0)/r)*180/pi
longitude = (Math.atan2(Y,X))*180/pi

puts altitude
puts latitude
puts longitude

But I am unable to figure out how I pass the X, Y, Z numbers or incorporate this as part of my script.

The Script that I have created is

require 'win32ole'
connection = WIN32OLE.new('ADODB.Connection')
connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=positions.mdb')
INPUT_DATAFILE = "output.txt"

def writefile(file, *linedata)

linedata.each do |line|

file << line.join(",") + "\n"

end

end

# Read file line-by-line and extract relevant information
def readfile(file, outputfile)

out = File.new(outputfile, "w+")

info = []

wline = ['rxname', 'qthx', 'qthy', 'qthz', 'sat']

IO.foreach(file){|line|
if line =~ /_Net/
wline[0] = line.split(" ")[1]

elsif line =~/\A\d{6}.\d{4}/
wline[1] = line.scan(/\A\d{6}.\d{4}/)

elsif line =~/\W\d{7}.\d{4}/
wline[2] = line.scan(/\W\d{7}.\d{4}/)

elsif line =~/\A\d{7}.\d{4}/
wline[3] = line.scan(/\A\d{7}.\d{4}/)

elsif line =~/sat/
wline[4] = line.split(" ")[1]

writefile(out, wline)

end

}

out.close

end

readfile('location.txt', 'output.txt')

File.open(INPUT_DATAFILE) do |f|

while line = f.gets
values = line.split(",") # our data is comma delimited
time = Time.new

sql = ("INSERT INTO satpost ([sdate],[stime],[netname],[qthx],[qthy],[qahz],[sat]) VALUES ('#{time.strftime("%d-%b-%y")}', '#{time.strftime("%H:%M")}', '#{values[0]}', '#{values[1]}', '#{values[2]}', '#{values[3]}', '#{values[4]}');")
puts sql
connection.Execute(sql)

end

end

Any help or guidance is appreciated.
Thanks,
AJ

2. This may or may not be what you are looking for but...

I'm not sure if you are just creating a stand-alone ruby script or if this is something that you intend to include in a Rails application.

If you plan on using it with a Rails app, then there is a plugin called Geokit that may help solve some issues for you:

http://geokit.rubyforge.org/

3. Thanks, I was not aware of that.
I was looking at a stand alone application. But will take a look at this.
Thanks,
AJ

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•