?
Solved

NMEA to POSTGRES using PERL

Posted on 2006-06-04
7
Medium Priority
?
833 Views
Last Modified: 2008-03-10
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
Comment
Question by:geomvigla
  • 4
  • 3
7 Comments
 
LVL 3

Expert Comment

by:DVB
ID: 16832094
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
 

Author Comment

by:geomvigla
ID: 16855533
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
 

Author Comment

by:geomvigla
ID: 16901789
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 3

Expert Comment

by:DVB
ID: 16901984
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
 

Author Comment

by:geomvigla
ID: 16904881
Thank you!
Can you please comment the meaning of eval{ and if($@)
George
0
 
LVL 3

Accepted Solution

by:
DVB earned 2000 total points
ID: 16905907
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
 

Author Comment

by:geomvigla
ID: 16905961
Thank you very much!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question