Create a POI SQLite database from a Garmin CSV

poi_sqlite.pl

#!/usr/bin/env perl

use strict;
use warnings;

use DBI;
use Data::Dumper;
use Geohash;
use Text::CSV;

my $dbh = DBI->connect("dbi:SQLite:dbname=test.db","","");
my $query = qq~CREATE TABLE IF NOT EXISTS poi (
  id INTEGER PRIMARY KEY,
  gh TEXT,
  lat REAL,
  lon REAL,
  desc TEXT,
  UNIQUE(gh) ON CONFLICT REPLACE 
)~;
$dbh->do($query) or die $dbh->errstr;

my $csv = Text::CSV->new({
	binary => 1,
	allow_whitespace => 1,
});
open(my $fh, "<:encoding(utf8)", "poi.csv")
	or die "Cannot open file: $!";
my $gh = Geohash->new();
$dbh->begin_work or die $dbh->errstr;
while (my $row = $csv->getline($fh)) {
	my %poi = (
		lon => shift(@$row),
		lat => shift(@$row),
		desc => join(", ", @$row),
	);
	$poi{gh} = $gh->encode($poi{lat}, $poi{lon});
	$query = qq~INSERT INTO poi (gh, lat, lon, desc) VALUES (?,?,?,?)~;
	my $sth = $dbh->prepare($query) or die $dbh->errstr;
	my @binds = ($poi{gh}, $poi{lat}, $poi{lon}, $poi{desc});
	$sth->execute(@binds) or die $dbh->errstr;
}
$dbh->commit or die $dbh->errstr;
close $fh;