XF 2.0 How do I add a more complex "where" clause to the finder?

[Jaxel]

When I need to locate something by GPS latitude and longitude... I need to put the following in my select clause:

SELECT event.* AS event,
    (6371 * acos(
        cos( radians( $latitude ) )
        * cos( radians( event.event_latitude ) )
        * cos( radians( event.event_longitude ) - radians( $longitude ) )
        + sin( radians( $latitude ) )
        * sin( radians( event.event_latitude ) )
    )) AS distance
...
...
...
HAVING distance < $distance

How would I accomplish this with the finder system?

 

[Chris D]

I’m not sure I’d bother.

One tactic is to simply get an array of IDs using a raw query and then use the finder system to get records matching those IDs.

 

[Jaxel]

All my events have latitude and longitude coordinates... Without that complex query, I can't search by distance.

 

[RobParker]

I assume it's irrelevant but you're aware that technically that equation is incorrect (or at least a poor approximation)?

 

[Jaxel]

I assume it's irrelevant but you're aware that technically that equation is incorrect (or at least a poor approximation)?

That is the equation that google itself uses...

 

[RobParker]

That is the equation that google itself uses...

For what purpose? I've not worked out if that's an equivalent form but it certainly doesn't look like the Haversine equation. Basic trig will approximate it well enough for small distances but if you have big distances you need to do the great circle calculation.

 

[Jaxel]

https://developers.google.com/maps/...ng-store-locator#finding-locations-with-mysql

 

[Jaxel]

I figured it out btw:

            $dis = $this->app->db->quote($filters['geoloc']['distance']);
            $uni = $this->app->db->quote($filters['geoloc']['units'] == 'km' ? 6371 : 3959);
            $lat = $this->app->db->quote($filters['geoloc']['latitude']);
            $lon = $this->app->db->quote($filters['geoloc']['longitude']);
            
            $entries->whereSql("( $uni * acos(
                cos( radians( $lat ) )
                * cos( radians( `ewr_atendo_events_Event_1`.`event_latitude` ) )
                * cos( radians( `ewr_atendo_events_Event_1`.`event_longitude` ) - radians( $lon ) )
                + sin( radians( $lat ) )
                * sin( radians( `ewr_atendo_events_Event_1`.`event_latitude` ) )
            )) < $dis ");

 

[RobParker]

Yeah if you're within 10s of miles then it won't make a difference. If it's hundreds of miles it will.