sql select join view

Given a table of id, 'points' and coordinates, would like to create a view (i think it'd need to be a view) such that i can get a list of distances between all 'pointids' for two given (foreign key) ids

input table

pointid (key), foreign_key, x, y
01,            id_x,        6, 2,
71,            id_x,        6, 1,
02,            id_y,        5, 9,
34,            id_z,        3, 8
42,            id_z,        2, 3,

desir开发者_运维技巧ed view, get the distances between all points belonging to id_x and id_y:

pointid_1,     pointid_2, distance
01,            02,        12 (or whatever distance is between 6,2 and 5,9)
71,            02,        12 (or whatever distance is between 6,1 and 5,9)

another query would be, the distances between all points belonging to id_x and id_z:

01,            34,        33 (distance between 6,2 and 3,8)
01,            42,        nn
71,            34,        nn
71,            42,        nn

etc

I can do the distance computation sqrt(....) , but do not know how to do the join and combine that with the distance computation, or what the appropriate query of such a view would be to produce the above results.

this would be using sqlite3 with loaded math lib extensions for the distance calc

---

create view PAIRWISE_DISTANCE_SQUARES as
select 
  a.id as a_id,
  b.id as b_id,
  (a.x-b.x)*(a.x-b.x) + (a.y-b.y)*(a.y-b.y) as square
from point_table as a, point_table as b
-- where a.id != b.id if you wish

This view will give you squares of distances between points. SQLite does not have a sqrt, but squares are as fine for sorting as actual distances.