i have table big, containing 160k rows of data. columns id, x, y, z, pl, bs , building.
for same (x,y,z), there multiple rows (different value of bs , pl).
i given value p, want 3 distinct (x,y,z) have shortest sum distances (euclidean distance).
for example, (x',y',z') has 3 rows, different pl1, pl2, pl3. sum of distances = sqrt{(pl1 - p)^2 + (pl2 - p)^2 + (pl3 - p)^2}
so, possible purely in mysql?
edited p scalar value column pl. (x,y,z) 3-dimension points, distance computed based on column pl. want know 3 distinct points (x,y,z) have sum of shortest distance, computed via sum(pl-p)
what want achieve k nearest neighbor (knn) algorithm, described in link.
to sum up, given scalar value p, task find 3 rows (with different x,y,z) table have shortest sum of distances. distances computed sqrt{(p-pl1)^2 + (p-pl2)^2 + ... (p-pln)^2}, there may more 1 rows (x, y, z).
create table script (added)
create table #temp ( id int, x float, y float, z float, pl float, bs int, building int ) insert #temp values (2,1582.1,-863.5,1.5,154.9,1,-1) ,(3,1602.1,-883.5,1.5,154,1,-1) ,(4,1602.1,-863.5,1.5,154.4,1,-1) ,(5,1622.1,-883.5,1.5,153.4,1,-1) ,(6,1622.1,-863.5,1.5,153.8,1,-1) ,(7,1622.1,-743.5,1.5,154.9,1,-1) ,(8,1642.1,-883.5,1.5,153.1,1,-1) ,(9,1642.1,-863.5,1.5,153.2,1,-1) ,(10,1642.1,-763.5,1.5,154.5,1,-1) ,(11,1642.1,-743.5,1.5,154,1,-1)
i not 100% if understand correctly answer. let me know if close or if there obvious mistake.
drop table #temp create table #temp ( id int, x float, y float, z float, pl float, bs int, building int ) insert #temp values (2,1582.1,-863.5,1.5,154.9,1,-1) ,(3,1602.1,-883.5,1.5,154,1,-1) ,(4,1602.1,-863.5,1.5,154.4,1,-1) ,(5,1622.1,-883.5,1.5,153.4,1,-1) ,(6,1622.1,-863.5,1.5,153.8,1,-1) ,(7,1622.1,-743.5,1.5,154.9,1,-1) ,(8,1642.1,-883.5,1.5,153.1,1,-1) ,(9,1642.1,-863.5,1.5,153.2,1,-1) ,(10,1642.1,-763.5,1.5,154.5,1,-1) ,(11,1642.1,-743.5,1.5,154,1,-1) declare @p float = 150 select x,y,z,sqrt(sum(power(pl-@p,2.0))) 'distance' #temp group x,y,z order 4 
Comments
Post a Comment