Even More Traveling, Even Less Sales

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 ) ;
    }