DBIx::Class Perimeter Search

Question

I am trying to get a perimeter search working with DBIx::Class but have not succeeded so far.

The SQL I would like to generate looks like this:

SELECT 
    zip,
    6371 * ACos( Cos(RADIANS(Lat)) * Cos(RADIANS(USERLAT)) * Cos(RADIANS(USERLNG) - RADIANS(Lng)) + Sin(RADIANS(Lat)) * Sin(RADIANS(USERLAT)) ) AS Distance
FROM 
    geopc
WHERE 
    6371 * ACos( Cos(RADIANS(Lat)) * Cos(RADIANS(USERLAT)) * Cos(RADIANS(USERLNG) - RADIANS(Lng)) + Sin(RADIANS(Lat)) * Sin(RADIANS(USERLAT)) ) <= DISTANCE
ORDER BY 
    Distance

Where USERLAT, USERLNG, and DISTANCE should be variables, which will come in thru a Webrequest.

My DBIx::Class Result:

use utf8;
package MyApp::Models::Schema::Result::Geopc;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("geopc");

__PACKAGE__->add_columns(
  "id",
  { data_type => "bigint", is_nullable => 0, is_auto_increment => 1 },
  "country",
  { data_type => "varchar", is_nullable => 0, size => 2 },
  "language",
  { data_type => "varchar", is_nullable => 0, size => 2 },
  "iso2",
  { data_type => "varchar", is_nullable => 0, size => 6 },
  "region1",
  { data_type => "varchar", is_nullable => 0, size => 60 },
  "region2",
  { data_type => "varchar", is_nullable => 0, size => 60 },
  "region3",
  { data_type => "varchar", is_nullable => 0, size => 60 },
  "region4",
  { data_type => "varchar", is_nullable => 0, size => 60 },
  "zip",
  { data_type => "varchar", is_nullable => 0, size => 10 },
  "city",
  { data_type => "varchar", is_nullable => 0, size => 60 },
  "area1",
  { data_type => "varchar", is_nullable => 0, size => 80 },
  "area2",
  { data_type => "varchar", is_nullable => 0, size => 80 },
  "lat",
  { data_type => "double precision", is_nullable => 0 },
  "lng",
  { data_type => "double precision", is_nullable => 0 },
  "tz",
  { data_type => "varchar", is_nullable => 0, size => 30 },
  "utc",
  { data_type => "varchar", is_nullable => 0, size => 10 },
  "dst",
  { data_type => "varchar", is_nullable => 0, size => 1 },
);
__PACKAGE__->set_primary_key('id');

I have googled around but have not found a good way to handle this. Any help would be very much appreciated.

I am using MySQL ...

Answer 1

I had the same problem: I have companies which belongs_to address, so an address has_many companies - I needed to find the neighbor companies, so, using the Adress model:

__PACKAGE__->add_columns(
  "id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
  "country",
  { data_type => "varchar", is_nullable => 0, size => 64 },
  "county",
  { data_type => "varchar", is_nullable => 1, size => 45 },
  "city",
  { data_type => "varchar", is_nullable => 0, size => 64 },
  "street",
  { data_type => "varchar", is_nullable => 0, size => 128 },
  "street_no",
  { data_type => "varchar", is_nullable => 1, size => 24 },
  "apartment_no",
  { data_type => "varchar", is_nullable => 1, size => 24 },
  "extra",
  { data_type => "varchar", is_nullable => 1, size => 128 },
  "lat",
  { data_type => "decimal", is_nullable => 1, size => [10, 7] },
  "long",
  { data_type => "decimal", is_nullable => 1, size => [10, 7] },
);

I've implemented the method get_neighbour_companies in that model:

sub get_neighbour_companies{
  my ( $self, $args ) = @_;

  my $distance = $args->{distance} // 15;

  my $geo_clause = sprintf('( 6371 * acos( cos( radians(%s) ) * cos( radians( me.lat ) ) * cos( radians( me.`long` ) - radians(%s) ) + sin( radians(%s) ) * sin( radians( me.lat ) ) ) ) AS distance', $self->lat, $self->long, $self->lat );

  my $rs = $self->result_source->schema->resultset('Address')
    ->search_rs(
      {
        'companies.company_type_id' => ( $args->{company_type_id} // 1 ), #defaults to 'orderer' type
      },
      {
        prefetch => { 'companies' => 'address' },
        select => [ 'id', \$geo_clause ],
        as     => [qw/ id distance /],
        having => { distance => { '<=' => $distance } },
        order_by => 'distance',
      }
    );

  my @companies;
  while ( my $address = $rs->next ){
    my @comps = $address->companies()->all;
    next unless @comps;

    foreach my $company ( @comps ) {
        push @companies, {
            company => $company,            
            distance => $address->get_column('distance'),
          };
    }    
  };
  return [ @companies ]; 
}

I am using it like this:

my $customers = $comp->address->get_neighbour_companies({
          distance        => 12,
          company_type_id => 1,
        });

where $customers will be an array ref to a list of companies within 12 kms of $comp, which is also a company

Answer 2

One solution for such complex queries is to define them as a view. That has the advantage of being join- and prefetch-able if you define relationships to it.

Another one to use columns for the calculated 'distance' column. 'columns' is just a combination of the 'select' and 'as' parameters which has been proven to be a more robust api that leads to less user errors. Note that the search syntax comes from SQL::Abstract and it provides some means to use literal sql.

The best solution without a subquery is:

my $param = \[
        '6371 * ACos( Cos(RADIANS(Lat)) * Cos(RADIANS(?)) * Cos(RADIANS(?)' .
        ' - RADIANS(Lng)) + Sin(RADIANS(Lat)) * Sin(RADIANS(?)) )'
        [ USERLAT  => $USERLAT ],
        [ USERLNG  => $USERLNG ],
        [ USERLAT  => $USERLAT ],
    ];

my $geopc = $schema->resultset('Result::Geopc')->search({
        $param => { '<=', $distance },
    }, {
        columns => [
           'zip',
           { distance => $param }
        ],
        order_by => $param,
    });

Answer 3

You can rewrite your query to use a subquery like this:

SELECT zip, Distance
FROM (SELECT zip,
        6371 * ACos( Cos(RADIANS(Lat)) * Cos(RADIANS(USERLAT)) * Cos(RADIANS(USERLNG) - RADIANS(Lng)) + Sin(RADIANS(Lat)) * Sin(RADIANS(USERLAT)) )
        AS Distance
     FROM geopc) AS tmp
WHERE Distance <= DISTANCE
ORDER BY Distance

Then something like the following should work:

my $geopc = $schema->resultset('Result::Geopc');

my $subquery = $geopc->search({}, {
    select => [
        'zip',
        \[
            '6371 * ACos( Cos(RADIANS(Lat)) * Cos(RADIANS(?)) * Cos(RADIANS(?)' .
            ' - RADIANS(Lng)) + Sin(RADIANS(Lat)) * Sin(RADIANS(?)) )' .
            ' AS Distance',
            [ USERLAT  => $USERLAT ],
            [ USERLNG  => $USERLNG ],
            [ USERLAT  => $USERLAT ],
        ],
    ],
})->as_query;

my $rs = $geopc->search({
    Distance => { '<=' => $DISTANCE },
}, {
    alias => 'geopc2',
    from => [
        { geopc2 => $subquery },
    ],
    select => [ qw(zip Distance) ],
    order_by => 'Distance',
});

This approach uses literal SQL with placeholders and the undocumented ResultSet attribute from. Some usage examples of the from attribute can be found in the DBIx::Class test suite. Note that since this attribute is undocumented, it might not be supported in future versions.