perl - DBIx::Class Perimeter Search -

i trying perimeter search working dbix::class have not succeeded far.

the sql generate looks this:

select      zip,     6371 * acos( cos(radians(lat)) * cos(radians(userlat)) * cos(radians(userlng) - radians(lng)) + sin(radians(lat)) * sin(radians(userlat)) ) distance      geopc      6371 * acos( cos(radians(lat)) * cos(radians(userlat)) * cos(radians(userlng) - radians(lng)) + sin(radians(lat)) * sin(radians(userlat)) ) <= distance order      distance 

where userlat, userlng, , distance should variables, come in thru 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 have not found way handle this. appreciated.

i using mysql ...

i had same problem: have companies belongs_to address, address has_many companies - needed find neighbor companies, so, using 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 method get_neighbour_companies in model:

sub get_neighbour_companies{   ( $self, $args ) = @_;    $distance = $args->{distance} // 15;    $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 ) ) ) ) distance', $self->lat, $self->long, $self->lat );    $rs = $self->result_source->schema->resultset('address')     ->search_rs(       {         'companies.company_type_id' => ( $args->{company_type_id} // 1 ), #defaults 'orderer' type       },       {         prefetch => { 'companies' => 'address' },         select => [ 'id', \$geo_clause ],             => [qw/ id distance /],         having => { distance => { '<=' => $distance } },         order_by => 'distance',       }     );    @companies;   while ( $address = $rs->next ){     @comps = $address->companies()->all;     next unless @comps;      foreach $company ( @comps ) {         push @companies, {             company => $company,                         distance => $address->get_column('distance'),           };     }       };   return [ @companies ];  } 

i using this:

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

where $customers array ref list of companies within 12 kms of $comp, company