Here's some table descriptions from MySQL, from which you should be able to reverse engineer the table creation.
State Capitals
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| state | varchar(25) | YES | | NULL | |
| st | varchar(2) | YES | | NULL | |
| city | varchar(25) | YES | | NULL | |
| latitude | float(12,6) | YES | | NULL | |
| longitude | float(12,6) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
Combinations - Connecting each one to each other
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| state_id_1 | int(10) | YES | | NULL | |
| state_id_2 | int(10) | YES | | NULL | |
+------------+---------+------+-----+---------+----------------+
Distances
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| distance | float(12,6) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
I'll say again, I think I made a mistake by not including distance in the combination table.
I didn't write perl code to put the state capital information into the database. I copied it from a source and recrafted it into SQL by hand.
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 01 , "Delaware" , "DE" , "Dover" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 02 , "Pennsylvania" , "PA" , "Harrisburg" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 03 , "New Jersey, NJ" , "Trenton" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 04 , "Georgia" , "GA" , "Atlanta" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 05 , "Connecticut" , "CT" , "Hartford" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 06 , "Massachusetts" , "MA" , "Boston" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 07 , "Maryland" , "MD" , "Annapolis" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 08 , "South Carolina" , "SC" , "Columbia" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 09 , "New Hampshire" , "NH" , "Concord" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 10 , "Virginia" , "VA" , "Richmond" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 11 , "New York" , "NY" , "Albany" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 12 , "North Carolina" , "NC" , "Raleigh" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 13 , "Rhode Island" , "RI" , "Providence" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 14 , "Vermont" , "VT" , "Montpelier" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 15 , "Kentucky" , "KY" , "Frankfort" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 16 , "Tennessee" , "TN" , "Nashville" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 17 , "Ohio" , "OH" , "Columbus" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 18 , "Louisiana" , "LA" , "Baton Rouge" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 19 , "Indiana" , "IN" , "Indianapolis" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 20 , "Mississippi" , "MS" , "Jackson" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 21 , "Illinois" , "IL" , "Springfield" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 22 , "Alabama" , "AL" , "Montgomery" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 23 , "Maine" , "ME" , "Augusta" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 24 , "Missouri" , "MO" , "Jefferson City" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 25 , "Arkansas" , "AR" , "Little Rock" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 26 , "Michigan" , "MI" , "Lansing" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 27 , "Florida" , "FL" , "Tallahassee" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 28 , "Texas" , "TX" , "Austin" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 29 , "Iowa" , "IA" , "Des Moines" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 30 , "Wisconsin" , "WI" , "Madison" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 31 , "California" , "CA" , "Sacramento" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 32 , "Minnesota" , "MN" , "Saint Paul" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 33 , "Oregon" , "OR" , "Salem" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 34 , "Kansas" , "KS" , "Topeka" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 35 , "West Virginia" , "WV" , "Charleston" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 36 , "Nevada" , "NV" , "Carson City" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 37 , "Nebraska" , "NE" , "Lincoln" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 38 , "Colorado" , "CO" , "Denver" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 39 , "North Dakota" , "ND" , "Bismarck" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 40 , "South Dakota" , "SD" , "Pierre" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 41 , "Montana" , "MT" , "Helena" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 42 , "Washington" , "WA" , "Olympia" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 43 , "Idaho" , "ID" , "Boise" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 44 , "Wyoming" , "WY" , "Cheyenne" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 45 , "Utah" , "UT" , "Salt Lake City" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 46 , "Oklahoma" , "OK" , "Oklahoma City" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 47 , "New Mexico" , "NM" , "Santa Fe" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 48 , "Arizona" , "AZ" , "Phoenix" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 49 , "Alaska" , "AK" , "Juneau" ) ;
INSERT INTO state_capitals ( id , state , st , city ) VALUES ( 50 , "Hawaii" , "HI" , "Honolulu" ) ;
The latitudes and longitudes were also hand-crafted.
UPDATE state_capitals SET latitude="32.361538", longitude="-86.279118" where state = "Alabama" ;
UPDATE state_capitals SET latitude="58.301935", longitude="-134.419740" where state = "Alaska" ;
UPDATE state_capitals SET latitude="33.448457", longitude="-112.073844" where state = "Arizona" ;
UPDATE state_capitals SET latitude="34.736009", longitude="-92.331122" where state = "Arkansas" ;
UPDATE state_capitals SET latitude="38.555605", longitude="-121.468926" where state = "California" ;
UPDATE state_capitals SET latitude="39.7391667", longitude="-104.984167" where state = "Colorado" ;
UPDATE state_capitals SET latitude="41.767", longitude="-72.677" where state = "Connecticut" ;
UPDATE state_capitals SET latitude="39.161921", longitude="-75.526755" where state = "Delaware" ;
UPDATE state_capitals SET latitude="30.4518", longitude="-84.27277" where state = "Florida" ;
UPDATE state_capitals SET latitude="33.76", longitude="-84.39" where state = "Georgia" ;
UPDATE state_capitals SET latitude="21.30895", longitude="-157.826182" where state = "Hawaii" ;
UPDATE state_capitals SET latitude="43.613739", longitude="-116.237651" where state = "Idaho" ;
UPDATE state_capitals SET latitude="39.783250", longitude="-89.650373" where state = "Illinois" ;
UPDATE state_capitals SET latitude="39.790942", longitude="-86.147685" where state = "Indiana" ;
UPDATE state_capitals SET latitude="41.590939", longitude="-93.620866" where state = "Iowa" ;
UPDATE state_capitals SET latitude="39.04", longitude="-95.69" where state = "Kansas" ;
UPDATE state_capitals SET latitude="38.197274", longitude="-84.86311" where state = "Kentucky" ;
UPDATE state_capitals SET latitude="30.45809", longitude="-91.140229" where state = "Louisiana" ;
UPDATE state_capitals SET latitude="44.323535", longitude="-69.765261" where state = "Maine" ;
UPDATE state_capitals SET latitude="38.972945", longitude="-76.501157" where state = "Maryland" ;
UPDATE state_capitals SET latitude="42.2352", longitude="-71.0275" where state = "Massachusetts" ;
UPDATE state_capitals SET latitude="42.7335", longitude="-84.5467" where state = "Michigan" ;
UPDATE state_capitals SET latitude="44.95", longitude="-93.094" where state = "Minnesota" ;
UPDATE state_capitals SET latitude="32.320", longitude="-90.207" where state = "Mississippi" ;
UPDATE state_capitals SET latitude="38.572954", longitude="-92.189283" where state = "Missouri" ;
UPDATE state_capitals SET latitude="46.595805", longitude="-112.027031" where state = "Montana" ;
UPDATE state_capitals SET latitude="40.809868", longitude="-96.675345" where state = "Nebraska" ;
UPDATE state_capitals SET latitude="39.160949", longitude="-119.753877" where state = "Nevada" ;
UPDATE state_capitals SET latitude="43.220093", longitude="-71.549127" where state = "New Hampshire" ;
UPDATE state_capitals SET latitude="40.221741", longitude="-74.756138" where state = "New Jersey" ;
UPDATE state_capitals SET latitude="35.667231", longitude="-105.964575" where state = "New Mexico" ;
UPDATE state_capitals SET latitude="42.659829", longitude="-73.781339" where state = "New York" ;
UPDATE state_capitals SET latitude="35.771", longitude="-78.638" where state = "North Carolina" ;
UPDATE state_capitals SET latitude="48.813343", longitude="-100.779004" where state = "North Dakota" ;
UPDATE state_capitals SET latitude="39.962245", longitude="-83.000647" where state = "Ohio" ;
UPDATE state_capitals SET latitude="35.482309", longitude="-97.534994" where state = "Oklahoma" ;
UPDATE state_capitals SET latitude="44.931109", longitude="-123.029159" where state = "Oregon" ;
UPDATE state_capitals SET latitude="40.269789", longitude="-76.875613" where state = "Pennsylvania" ;
UPDATE state_capitals SET latitude="41.82355", longitude="-71.422132" where state = "Rhode Island" ;
UPDATE state_capitals SET latitude="34.000", longitude="-81.035" where state = "South Carolina" ;
UPDATE state_capitals SET latitude="44.367966", longitude="-100.336378" where state = "South Dakota" ;
UPDATE state_capitals SET latitude="36.165", longitude="-86.784" where state = "Tennessee" ;
UPDATE state_capitals SET latitude="30.266667", longitude="-97.75" where state = "Texas" ;
UPDATE state_capitals SET latitude="40.7547", longitude="-111.892622" where state = "Utah" ;
UPDATE state_capitals SET latitude="44.26639", longitude="-72.57194" where state = "Vermont" ;
UPDATE state_capitals SET latitude="37.54", longitude="-77.46" where state = "Virginia" ;
UPDATE state_capitals SET latitude="47.042418", longitude="-122.893077" where state = "Washington" ;
UPDATE state_capitals SET latitude="38.349497", longitude="-81.633294" where state = "West Virginia" ;
UPDATE state_capitals SET latitude="43.074722", longitude="-89.384444" where state = "Wisconsin" ;
UPDATE state_capitals SET latitude="41.145548", longitude="-104.802042" where state = "Wyoming" ;
The distances themselves were generated mathematically, with the help of Google and Wikipedia to find the how-to.
#!/usr/bin/perl
use 5.010 ;
use strict ;
use warnings ;
use Data::Dumper ;
use DBI ;
use lib '/home/jacoby/lib' ;
use MyDB 'db_connect' ;
use subs qw{ get_combos get_states set_distance } ;
my $pi = atan2( 1, 1 ) * 4 ;
my $states = get_states() ;
my $combos = get_combos() ;
for my $combo ( (sort { $a<=>$b } keys %$combos ) ) {
my $c_obj = $combos->{$combo} ;
my ( $state_1 , $state_2 ) = sort { $a <=> $b } $c_obj->{ state_id_1 } , $c_obj->{ state_id_2 } ;
my $obj_s1 = $states->{ $state_1 } ;
my $obj_s2 = $states->{ $state_2 } ;
my $dist = haversine(
$obj_s1->{ latitude } , $obj_s1->{ longitude } ,
$obj_s2->{ latitude } , $obj_s2->{ longitude } ) ;
say $combo ;
say join ' - ' ,
( join ', ' , $obj_s1->{ city } , $obj_s1->{ state } ) ,
( join ', ' , $obj_s2->{ city } , $obj_s2->{ state } ) ;
say join "\t" , '' , $dist . ' miles';
set_distance( $combo , $dist ) ;
}
sub get_states {
my $dbh = db_connect() ;
my $sql = 'SELECT * from state_capitals ORDER BY id' ;
my $states = $dbh->selectall_hashref( $sql , 'id' ) or croak $dbh->errstr;
return $states ;
}
sub get_combos {
my $dbh = db_connect() ;
my $sql = 'SELECT * from combinations ORDER BY id' ;
my $combos = $dbh->selectall_hashref( $sql , 'id' ) or croak $dbh->errstr;
return $combos ;
}
sub set_distance {
my ( $combo , $dist ) = @_ ;
my $dbh = db_connect() ;
my $sql = "INSERT INTO distances ( id , distance ) VALUES ( $combo , $dist ) " ;
say $sql ;
$dbh->do( $sql ) or croak $dbh->errstr;
}
sub haversine {
my ( $lat1, $lon1, $lat2, $lon2 ) = @_ ;
my $theta = $lon1 - $lon2 ;
my $dist =
sin( deg2rad( $lat1 ) ) *
sin( deg2rad( $lat2 ) ) +
cos( deg2rad( $lat1 ) ) *
cos( deg2rad( $lat2 ) ) *
cos( deg2rad( $theta ) ) ;
$dist = acos( $dist ) ;
$dist = rad2deg( $dist ) ;
$dist = $dist * 60 * 1.1515 ;
return sprintf '%5.2f' , $dist ;
}
sub acos {
my ( $rad ) = @_ ;
my $ret = atan2( sqrt( 1 - $rad**2 ), $rad ) ;
return $ret ;
}
sub deg2rad {
my ( $deg ) = @_ ;
return ( $deg * $pi / 180 ) ;
}
sub rad2deg {
my ( $rad ) = @_ ;
return ( $rad * 180 / $pi ) ;
}
