#!/usr/bin/perl -w

use strict;

my $STATES = 
{
	AL => "Alabama",
	AK => "Alaska",
	AZ => "Arizona",
	AR => "Arkansas",
	CA => "California",
	CO => "Colorado",
	CT => "Connecticut",
	DE => "Delaware",
	DC => "District of Columbia",
	FL => "Florida",
	GA => "Georgia",
	HI => "Hawaii",
	ID => "Idaho",
	IL => "Illinois",
	IN => "Indiana",
	IA => "Iowa",
	KS => "Kansas",
	KY => "Kentucky",
	LA => "Louisiana",
	ME => "Maine",
	MD => "Maryland",
	MA => "Massachusetts",
	MI => "Michigan",
	MN => "Minnesota",
	MS => "Mississippi",
	MO => "Missouri",
	MT => "Montana",
	NE => "Nebraska",
	NV => "Nevada",
	NH => "New Hampshire",
	NJ => "New Jersey",
	NM => "New Mexico",
	NY => "New York",
	NC => "North Carolina",
	ND => "North Dakota",
	OH => "Ohio",
	OK => "Oklahoma",
	OR => "Oregon",
	PA => "Pennsylvania",
	RI => "Rhode Island",
	SC => "South Carolina",
	SD => "South Dakota",
	TN => "Tennessee",
	TX => "Texas",
	UT => "Utah",
	VT => "Vermont",
	VA => "Virginia",
	WA => "Washington",
	WV => "West Virginia",
	WI => "Wisconsin",
	WY => "Wyoming"
};

&main();

sub main
{
	my $entries = {};
	
	if (0 == scalar(@ARGV)) {
		print STDERR "main().. [1] must supply a file name\n";
		return;
	}
	
	my $file = $ARGV[0];
	
	if (!defined $file || $file eq "") {
		print STDERR "main().. [2] must supply a file name\n";
		return;
	}
	
	open(FILE,"<$file") or die "main().. failed to open '$file', $!\n";
	
	while (<FILE>) {
		my $line = $_;
		
		if ($line !~ /^\[([\S]{3})\]\s+(\S+)\s+(\S+)\s+(.+)$/) {
			next;
		}
		
		my $sym = $1;
		my $lat = $2;
		my $lon = $3;
		my $des = $4;
		
		if ($des !~ /^(.+),([A-Z]{2})$/) {
			print "main()..   couldn't parse city/state for '$des'\n";
			next;
		}
		
		my $cit = $1;
		my $sta = $2;
		my $nam = $STATES->{$sta};
		
		if (!defined $nam || $nam eq "") {
			print "main().. couldn't find state name for '$sta'\n";
			next;
		}
		
		if (defined $entries->{"$cit, $nam"}) {
#			print "main()..   skipping duplicate [$cit, $nam]\n";
			next;
		}
		
		$entries->{"$cit, $nam"} = 1;
		
		$cit =~ s/\'/\'\'/g;
		
#		print "main().. sym=$sym, lat=$lat, lon=$lon, des=$des, cit=$cit, sta=$sta, nam=$nam\n";
		print "INSERT INTO location (location, description, altitude) VALUES (ST_GeomFromText('POINT($lat -$lon)', 4326), '$cit, $nam, USA', 0);\n";
	}
	
	close(FILE);
}

1;