This question is self-answered to help people that need to create distance matrix in MySQL, which supports queries such as "select id2 from Distances where id1=10 and type1=0 and type2=1 and distance<10" which would find objects of type 1 in vicinity of 10 from object 10 of type 0.
The code assumes table structure similar to one described in Mysql Haversine distance calculation
If you ever find yourself in need of a distance matrix in mysql, here's a View definition for it:
select o1.object_id AS id1,o1.object_type AS type1,o2.object_id AS id2,o2.object_type AS type2,geodistance_km_by_obj(o1.object_id,o1.object_type,o2.object_id,o2.object_type) AS distance from (Coordinates o1 join Coordinates o2) where ((o1.object_id,o1.object_type) <> (o2.object_id,o2.object_type))
Where the geodistance_km_by_obj function is some distance calculation function, for example based on haversine calculation linked in the header.
CREATE FUNCTION geodistance_km_by_obj(object_id1 INT, object_type1 TINYINT, object_id2 INT, object_type2 TINYINT)
RETURNS float
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
COMMENT 'returns distance in km'
BEGIN
declare sl1 float;
declare cc1 float;
declare cs1 float;
declare sl2 float;
declare cc2 float;
declare cs2 float;
select sin_lat,cos_cos,cos_sin into sl1, cc1, cs1 from Coordinates where object_id=object_id1 and object_type=object_type1;
select sin_lat,cos_cos,cos_sin into sl2, cc2, cs2 from Coordinates where object_id=object_id2 and object_type=object_type2;
return cast(round(acos(sl1*sl2 + cc1*cc2 + cs1*cs2)*6371,0) as decimal);
END
