0

I need to search and order by distance asc using two tables.

Explained:

Table 1: companies Id, CompanyName, address, location (point datatype)

Table 2 : branches Id, companyID, address, location (point datatype)

Table 1 is companies main data, main address, location and details etc, Table 2 is purely for a companies branches which also contains a locatiom point datatype.

I already have a query working with a haversine formula to query the nearest companies around a specific location (gotten through html5 location features). And it's working.

The problem is: it's only taking in account table 1 which has the main location. So, if the user is also near a branch of any other company, it won't be shown..

I need to make the query evaluate table 1 and table 2 locations and sort by distance taking in account both..

Is there a way this can be done ?

matthias_h
  • 11,356
  • 9
  • 22
  • 40
lorenzo gonzalez
  • 1,894
  • 4
  • 14
  • 18
  • Yes. You could use a join - but I think I would store all addresses in one table, with either a flag or 'branch_id NULL' to signify head offices – Strawberry Aug 31 '14 at 09:01
  • Thnx a lot for answering. Can you explain a little bit more on how i could make the join. The thing is in table two will also be needed to be linked to table1 by id to get the company name also. And sbout sorting.. The way i have is.. I have rhe haversine formula as distance and then sort by distance asc. I dont know how to make the haversine formula. Evualuate the location data from table one and from table two and then sort by distance asc... – lorenzo gonzalez Aug 31 '14 at 21:00
  • Actual query : select e.id,e.nombre,p.nombre,t.nombre,Y(e.location),X(e.location),e.direccion,e.telefono, ROUND( SQRT( POW((69.1 * ($lat - X(e.location))), 2) + POW((53 * ($lng - Y(e.location))), 2)), 1) AS distance FROM empresas as e,provincias as p, tipo_comida as t where (e.status like 'A' and Y(e.location) is not NULL and X(e.location) is not NULL) and p.id=e.provincia and t.id=e.tipo_comida HAVING distance < 5 order by distance asc limit 10 – lorenzo gonzalez Aug 31 '14 at 21:07
  • That was the main table 1: is a restaurants table, also getting the type of food, city, address,telephone. the Table 2 for branches has fields as follows : id, empresa_id,provincia,direction,telefono,horario,location. – lorenzo gonzalez Aug 31 '14 at 21:10
  • empresa_id stands for company_id, provincia=city,direccion=address,telefono=telephone,horario=schedule,location ( point type field with location also) – lorenzo gonzalez Aug 31 '14 at 21:11
  • Thanks all for the responses. What i did was i modified the branches table, added a mainbranch field. Then just look in the branches table and join to the company table for other data needed... Thanks all – lorenzo gonzalez May 06 '15 at 04:52

0 Answers0