SELECT A.Id, B.Id, lat_long_km(A.longitude,A.latitude,B.longitude,B.latitude) AS instance_distance_km
FROM local_db.entity A
INNER JOIN local_db.entity B
ON A.Id < B.Id
INNER JOIN local_db.type_link tcl
ON
(
# check types are in link table
(A.type_id <= B.type_id AND A.type_id = tcl.type_id_A AND B.type_id = tcl.type_id_B)
OR
(A.type_id > B.type_id AND B.type_id = tcl.type_id_A AND A.type_id = tcl.type_id_B)
)
INNER JOIN local_db.type_range_info trange
ON
(
# get the range for the type
(A.type_id <= B.type_id AND A.type_id = trange.type_id)
OR
(A.type_id > B.type_id AND B.type_id = trange.type_id)
)
WHERE
lat_long_km(A.longitude,A.latitude,B.longitude,B.latitude) < trange.range_km;
Tables and their important fields:
entity - Id, longtitude, latitude, type_id
type_link - type_id_A, type_id_B
type_range_info - type_id, range_km
It runs very slow because of the repeated call to lat_long_km() but if I replace the final WHERE clause with instance_distance_km < trange.range_km; it fails with the error "SQL Error (1054): Unknown column 'instance_distance_km' in 'where clause'.
I can't fund any way to eliminate the double stored function call eg by storing the result. It's part of an INSERT so has to be in one query. Any ideas?
Backstory:
I want to find entities of certain types within certain distances of each other. I use a table type_link to store which types should be compared (by storing pairs of type_ids) and type_range_info to store what the range between certain types should be in km.
I have written a stored function lat_long_km(A.longitude,A.latitude,B.longitude,B.latitude) that returns the km distance between two points and use the statement below to find all entities that are linked and are within the correct range.
