Forum

Thread tagged as: Question, Discussion, Runway

Distributor Locator at Scale

Hi all,

I'm working on a site in Runway and all is going well (of course) but I've come to building a locator for where to find the client's product and I've discovered that the amount of data is by far the biggest thing I've ever considered throwing at Perch. I'm looking at thousands of geo locations and addresses, which, of course have to be searchable and sorted by distance, etc.

So, I'm wondering if anyone has experience with something this large and what (if any) are the implications for both the site users and the admin or should I possible use something other than Perch for this section of the site (I'd rather not!)?

I've seen this great thread on the topic: https://forum.grabaperch.com/forum/07-17-2014-building-a-store-locator for how to potentially do it but I'm wondering how to do it efficiently (if it isn't already)?

I'd appreciate any comments on this topic, I'm looking forward to getting stuck into this challenge.

Thank you!
Mark Greenwood

Mark Greenwood 0 points

  • 6 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

What sort of scale are you looking at? Millions?
Mark Greenwood

Mark Greenwood 0 points

6 years ago

No, I think thousands, probably not quite 10k. Which, thinking about it, isn't really that many in terms of database size but I've never worked with something this large in Perch.

My main concern is returning results by working out the distance from a given geo code quickly. I'm thinking I have to take the raw data from each location, work out the distance to the input and return it if it's within X miles. It seems like I have to ask Perch to do a lot of work to do a calculation and reject 99% of results (though, I could subset by the country data Google returns).

I'm looking at the UK data I have and that's only ~250 locations but this will be global.

Am I making a mountain out of a mole hill or just doing it wrong?

This is what I'm trying to achieve but via Perch (I have a simple php example of it already working):

SELECT
    id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance
FROM
    markers HAVING distance < 25 ORDER BY distance
LIMIT
    0 , 20;

/* 3959 for results in miles, not km */

HT: https://developers.google.com/maps/articles/phpsqlsearch_v3
Drew McLellan

Drew McLellan 2638 points
Perch Support

6 years ago

Limiting by country would be a good optimisation if that's a possibility. It's always better to do less work than more.

If the end result is to get locations with X miles, I think you should be able to ultimately do that with a filter. If you can calculate the geo box you're searching within, you can then filter on the lat and lng values. In theory.
Mark Greenwood

Mark Greenwood 0 points

6 years ago

Thanks Drew!

That is actually a brilliant idea, I was trying to do a circle and dismissed a box but it makes way more sense filtering lat and lng using between and the country of the search. In theory. :)

I do like to over complicate matters. Thanks very much for the help. If I get anything useful, I'll have to get a post up and share.

Cheers.