0

My Company currently runs a listing service of family activities. In our CMS we have two types of entities Branches (The shops we list) and Events (Special Offers, Occasions etc).

Typically when listing an event we would say which Branches it is for and create a relationship, we would search the near by shops for events. Grab them and sort them by distance.

Now our clients want to be able to list a one off event that hasn't got a branch associated with it (For example they host a Festival at a near by garden center rather than one of their shops), I can easily make it I can sort these by distance as well.

But what I was wondering is how could combine the both, so one of our apps could go to our API, "Dude, where are 10 events near to whee I am right now ? " and the api would pull up a list of the 10 closest events.

It should be able to handle Events that are using the location of Branches as well as having its own unique location.

Or do you think I should just store location as its own entity or have hidden branches, places we can set up as being where the event is happening but don't actually show up as being a branch in the app :)

James Campbell
  • 3,511
  • 4
  • 33
  • 50

2 Answers2

1

If you have lat / long positions for your events and your branches you can apply the Haversine Formula to compute approximate distances, then order by ascending distance.

MySQL can do this, if you're willing to use a hairy query. This note from the Google Maps team gives the query. You don't have to use Google Maps to do this; you just need lat/long information for each place involved.

https://developers.google.com/maps/articles/phpsqlsearch_v3

Edit It's true that this is very slow if you compute the distance between many pairs of places. The trick to making this kind of operation fast is using a bounding box (spherectangular) distance limit, and putting indexes on your latitude and longitude.

Look at this: Geolocation distance SQL from a cities table

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • :) This is what I use an it works very well, would this work with a join so if I had two tables with lattitude and longitudes. I could join them and sort ? I know its a very expensive operation. – James Campbell Jun 26 '13 at 12:11
1

MYSQL has support for "spacial databases" as the spacial extension This will allow you to use "spacial" datatypes in your columns, as well as build index on them, and perform various "spacial analysis" such as polygon intersection.

Not sure this is what you need, but that may worth investigations.

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125