macrostrat

package macrostrat;
use Dancer ':syntax';
use Dancer::Plugin::REST;
use DBI;

prepare_serializer_for_format;

our $VERSION = '0.1';

my $db = config->{'database'};
my $hn = config->{'hostname'} . ':mysql_socket=/var/mysql/mysql.sock';
my $un = config->{'username'};
my $pw = config->{'password'};
my $pn = 3306;

my $dbh = DBI->connect("DBI:mysql:database=$db;host=$hn;port=$pn","$un","$pw");

get '/' => sub {
    template 'index';
};

#curl http://localhost:3000/poly.json?BBOX=-87%2033,%20-85%2033,%20-85%2035,%20-87%2035,%20-87%2033
get '/points.:format' => sub {
    my $bbox = params->{'BBOX'};
    my $callback = params->{'callback'};
    my $res = $callback . '(' . to_json(points_within_bbox($bbox)) . ')';
    return $res;
};

get '/polys.:format' => sub {
    my $bbox = params->{'BBOX'};
    my $callback = params->{'callback'};
    my $res = $callback . '(' . to_json(polys_within_bbox($bbox)) . ')';
    return $res;
};

true;

sub points_within_bbox {
    my ($bbox) = @_;

    my $sql = qq{
        SELECT id, col_group_id, lat, lng, col_name
        FROM cols
        WHERE status_code='active' AND MBRContains(GeomFromText('POLYGON(($bbox))'), coordinate)
    };

    my $sth = $dbh->prepare($sql, { async => 1 });
    $sth->execute();
    my $res = $sth->fetchall_arrayref({});
    return $res;
}

sub polys_within_bbox {
    my ($bbox) = @_;

    my $sql = qq{
        SELECT col_group_id, col_name, gmap
        FROM cols JOIN col_areas ON cols.id = col_areas.col_id
        WHERE status_code='active' AND MBRContains(GeomFromText('POLYGON(($bbox))'), coordinate)
    };

    my $sth = $dbh->prepare($sql, { async => 1 });
    $sth->execute();
    my $res = $sth->fetchall_arrayref({});
    return $res;
}