• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 841
  • Last Modified:

NMEA to POSTGRES using PERL

Hello,
I want a program in perl which would read NMEA data from a text file and would write longtitude,latitude and elevation in a database which is set up with Postgresql. I lack in programming so I would also like the source code.
Thank You
George
0
geomvigla
Asked:
geomvigla
  • 4
  • 3
1 Solution
 
DVBCommented:
Hmmm, perhaps you need a programmer for this? If you can post sample data and table structures, I might be able to help you.
0
 
geomviglaAuthor Commented:
I have a file with gps data in nmea format the format is like this
$GPRMC,081836,A,3751.65,S,14507.36,E,000.0,360.0,130998,011.3,E*62
I have a parser in perl and I want to read this file and write it in a table in Postgresql. I don't have a ready database where i will store it. Right now I need a simple code to read the file parse the data and store it to a database. I know it is not difficult but I don't have the programming experience and enoygh time to study more on it.
The parser source code is:

my @f = split ',', $t;
if ($f[0] eq 'GPRMC') {
      shift @f;

      my ($latdir,$longdir, $magvardir);
      ($gps{rmc}{time}, $gps{rmc}{warning},
       $gps{rmc}{latitude}, $latdir, $gps{rmc}{longtitude}, $longdir,
       $gps{rmc}{sog}, $gps{rmc}{cmg}, $gps{rmc}{date},
       $gps{rmc}{magvar}, $magvardir ) = @f;

      $magvardir ||= '';
      $gps{rmc}{latitude}   *= -1 if $latdir eq 'S';
      $gps{rmc}{longtitude} *= -1 if $longdir eq 'W';
      $gps{rmc}{magvar}     *= -1 if $magvardir eq 'W';
      
      $gps{rmc}{latitude} /= 100;
      $gps{rmc}{longtitude} /= 100;

I just need to store longtitude and latitude
I hope I can find some help and some source code
Thank you
George

0
 
geomviglaAuthor Commented:
Ok I solve the problem of reading the file and parsing the data.
Now I want to add $x,$y,$z to a tabl of postgresql.
Can you help me on how I make the connection with my database?
George
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
DVBCommented:
Argh! Sorry, I had typed a script, and then my browser blew up.

#!/usr/bin/perl

use strict;
use DBI;

my $dsn = "dbi:Pg:dbname=test";
# or "dbi:Pg:dbname=test;host=192.168.1.1;port=5432" to connect to a remote box.

my $dbh = DBI->connect($dsn, "user", "password" {
                       AutoCommit => 0,
                       RaiseError => 1,
                       PrintError => 1,
             });

# Assumes a table mytables with columns latitude, longitude and elevation.

my $sql = "INSERT INTO mytable (latitude, longitude, elevation) VALUES (?, ?, ?)";

my $sth = $dbh->prepare($sql);
eval {
         $sth->execute($x, $y, $z);
}

if ($@) {
         print "Transaction failed: $@\n";
         $dbh->rollback();
}
else {
         $dbh->commit();
}

$dbh->disconnect();

# end script

If you are using PostgreSQL 8.x, just use the COPY option in psql and import the original CSV.
http://www.postgresql.org/docs/current/static/sql-copy.html
0
 
geomviglaAuthor Commented:
Thank you!
Can you please comment the meaning of eval{ and if($@)
George
0
 
DVBCommented:
eval {}; is the Perl way of doing a try {} catch{} block.

If the statement fails/errors out for any reason, putting it in an eval block will allow you to handle it gracefuilly.

If there is an error, eval{}; puts the error in the variable $@. Otherwise this variable is not defined.
If you are sure your data is clean, I would implement something like the following pseudocode:

open database connection
prepare statement
while <valid data>
    eval {
        execute statement
    }
    if (error) {
        rollback
    }
}

# all the data has been successfully inserted
commit
disconnect from database
0
 
geomviglaAuthor Commented:
Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now