1

I have a table(table1) consists of 5 columns in this order

date  |   x   |   y    |    z    |     t
...   | 10.24 | 12.01  |    6    |     7
...   |  42   |   18   |    12   |     1

and .... This table has 5 million records

I have another table(table2) like table1 structure but it has only 10 rows I want to find the closest path in table1. I use this method to calculate distance between two points

sqrt(pow(table2.x-table1.x,2) + pow(table2.y-table1.y,2) + pow(table2.z-table1.z,2) + pow(table2.t-table1.t,2))

what is the fastest way to find the nearest ones in table1. data of the table2 changes every 10 minutes

jarlh
  • 42,561
  • 8
  • 45
  • 63
Hadi Developer
  • 37
  • 5
  • 1
    `I want to find the closest path in table1` ... can you explain what this means? Maybe show more sample input data _and_ the expected output. – Tim Biegeleisen Oct 15 '18 at 07:22
  • Unless you can heuristically limit how many in table 1 you have to check, you are forced to read the entire table and run it through the formulae. No mention of which database is being used is made - ideally you are on one with geo-spatial support which could alter the problem at least. – Andrew Oct 15 '18 at 08:07
  • How many records do change in table2? Only one or most of them? I'm thinking about indexed view, but it wouldn't make sense if all of rows change... – Limonka Oct 15 '18 at 08:18
  • I think you could try to ask this on dba.stackexchange.com. – Limonka Oct 15 '18 at 08:46
  • You could look into GIS extensions. These implement alternative indexing structures (R-trees) that speed up such searches. – Gordon Linoff Oct 15 '18 at 12:18
  • Tnx a lot, actually the table1 has 5 million records each record represents a point which is defined in 4 dimensions. table2 has 10 points in 4 dimensions I want to find the most similar one to the current line. – Hadi Developer Oct 16 '18 at 08:21

0 Answers0