Trying to query geolocation using selectRaw with my ShippingAddress model

I'm trying to build a search so that a user can put in a zip code and we search a 20 mile radius in order to get locations close to them.

I've followed this example online and modified it to fit my needs.

I'm using this articles Eloquent model example https://www.techalyst.com/posts/laravel-find-nearest-restaurants-from-certain-gps-latitude-and-longitude

My code looks like this.

$zipcode = $request->zipcode;

        $zip = ZipCode::where('zip', $zipcode)->first();

        if($zip)
        {
            $radius = 20;
            $latitude = $zip->lat;
            $longitude = $zip->lng;

            /*
             * replace 6371000 with 6371 for kilometer and 3956 for miles
             */
            $addresses = ShippingAddress::selectRaw("id, business_name, address, address2, city, state, zip, lat, lng, distance,
                ( 3956 * acos( cos( radians(?) )
                * cos( radians( lat ) )
                * cos( radians( lng ) - radians(?)) + sin( radians(?) )
                * sin( radians( lat ) ) )) AS distance", [$latitude, $longitude, $latitude])
                ->having("distance", "<", $radius)
                ->groupBy('shipping_addresses.id')
                ->offset(0)->limit(20)->get();

I do not get any error however I don't get any results.

My zip_codes table has a lat / lng for each zip code and the addresses in my shipping_addresses table have lat / lng locations that are either identical as the location being used to do the radius search or they should be within a 20 mile radius for testing purposes.

It's worth mentioning that my query was complaining about not having a "distance" column in the shipping_addresses table, so I added that. I also set the column to nullable so I'm not sure if that's causing the issue or not. the article reference above doesn't say anything about the distance column being required or what value needs to be in the column or even what the column type needs to be.

m615

5 years ago

Best Answer

Level 2

I actually found a solution in this post. https://laracasts.com/discuss/channels/eloquent/how-to-store-locations-with-laravel-and-eloquent?page=1#reply=700933

here is what My updated code looks like.

$addresses = DB::select('SELECT * FROM shipping_addresses WHERE
                                lat BETWEEN ('. $lat .' - ( '. $circle_radius .' *0.018)) AND ('. $lat .'  + ('. $circle_radius .' *0.018)) AND
                                lng BETWEEN ('. $lng .'  - ('. $circle_radius .' *0.018)) AND ('. $lng .'  + ('. $circle_radius .' *0.018));');

Please or to participate in this conversation.