I’m keen to make some solid progress on HadCoffee over the Christmas period to hopefully get to an early 2019 v1 launch. This week I was working on the main coffee rating interface which depends on users being able to select or enter the café they visited.
I’ll add the autocomplete lookups for typed cafe names, but I also wanted some basic quick suggestions based on where the user has been before and their current location.
This would save typing for common places and maybe also hint at other nearby cafés the user mightn’t know about.
The frontend part of these feature went pretty smoothly. The Vue component watches for the users location (HTML5 Geolocation) and calls the backend to load these quick suggestions as it’s known. There’s a little debounce action to slow things down as the geolocation API can return updated positions in quick succession as it locks on a more accurate location.
Geographic Search by Proximity
The smooth progress I was making through this feature hit a wall when it came to actually querying cafés by distance though. I was hoping to use MySQL’s ST_Distance_sphere function to let the DB do that work. I’m running MariaDB though, which although it’s advertised as a ‘drop in’ replacement for MySQL does not support this feature ????
I prefer a simpler dev environment (I’m not using Laravel Valet or Docker images) so I didn’t feel like swapping to MySQL for this project. Changing my workflow to use Valet also wasn’t very appealing when I’m otherwise happy with the setup. so I briefly tried migrating to Postgres. I know it’s a great DB, but I haven’t used it before and that’s a big change to have to make to run one type of query.
In the end I’m going with a raw SQL query to help with this. I’ll add a simple bounding box to its parameters first to avoid having to do a table scan of every cafe in the world (once my DB gets to that point ????)
Although it took a windy path this geo search will also provide the basis for the other cafe search features on the site such as the autocomplete (to improve relevance) and the location based search.
$query = "SELECT id, cafe_id, lat, lng, address, locality, city,
( 6371 * acos( cos( radians(:lat) ) * cos( radians( lat ) )
* cos( radians( lng ) - radians(:lng) ) + sin( radians(:lat2) ) * sin(radians(lat)) ) ) AS distance
FROM cafe_locations ";This is a good step towards being able to add café & coffee reviews, however the next big sticking point will be letting users add new cafés as they go.
Ideally I’d like to collect a bit of meta data such as roasters, menu and seating options to help users finding cafés, but I’ll have to see how much data entry users will tolerate. I also need to be aware of how or if I can verify this community sourced data.