Order by distance. How?

There is a big table of x, y, z coordinates.
Is it possible with one query to sort by distance at a certain point (say [50, 50, 50]).
Or maybe another way to design the table?
March 23rd 20 at 18:45
3 answers
March 23rd 20 at 18:47
Make a calculated column and sort by it. A formula you can Google for short distances. For large - not just
I mean a computed column in the select e type select * func(...) as distance order by distance...?
But then I get a FullScan with any query the same. - crystal35 commented on March 23rd 20 at 18:50
@crystal35, the function is not required. Just a mathematical calculation - kaden_Franecki commented on March 23rd 20 at 18:53
@kaden_Franecki, Yes. Would make such a request
select *, sqrt((x - x, 2) + pow(y - y, 2) + pow(z :z, 2)) as distance from points order by distance limit 10.

It works. But there's no way to optimize, right? There is no one index will not work, and always fullscan. - crystal35 commented on March 23rd 20 at 18:56
@crystal35should not. And anything here you will not want much. Except that you can do clusters in the squares on the map and using them, but otherwise not even imagine - kaden_Franecki commented on March 23rd 20 at 18:59
@kaden_Franecki, I also thought about the clusters. Divide the coordinates into zones. But if the point is closer to the edge of the cluster, the nearest may be in another. And in the end, it is necessary to view the next and so on. How all this process I'll never know and scored. - crystal35 commented on March 23rd 20 at 19:02
@crystal35, and is not necessary. how many of you there points? - kaden_Franecki commented on March 23rd 20 at 19:05
@crystal35, I saw one document on the Internet on the topic ) - kaden_Franecki commented on March 23rd 20 at 19:08
@kaden_Franecki, thank you! just found out that you don't care that the points table will change from outside or classes will be run in parallel. And all searches will be within the same script. Roughly speaking, it is possible to save the cache in php for a quick search. It's so much easier. 2 days down the drain) - crystal35 commented on March 23rd 20 at 19:11
@crystal35, you have not practiced such a thing as architecture?) or it is, but its not showing anyone?) - kaden_Franecki commented on March 23rd 20 at 19:14
@kaden_Franecki, this is again a test task! purely academic. of course, in a real task to do so is unacceptable. And what kind of architecture they have there I don't know. It is not taken yet. - crystal35 commented on March 23rd 20 at 19:17
@crystal35, are you seriously decide to test the job using the toaster? write, please, the name of the organization to write to you did not take - kaden_Franecki commented on March 23rd 20 at 19:20
@kaden_Franecki,
First, I decided it myself. It's not all a test, but only one thing I additionally simplified to understand, and I also decided (wrote above about it), but the method I used seemed suboptimal, so I decided to ask for advice because nothing googled such and not contrived.
Second, what actual help I received? About a computed column, and so I did. Nothing more. - crystal35 commented on March 23rd 20 at 19:23
@crystal35, the fact that you will in any case try to get help instead of trying to draw conclusions from failures. Well wanting to avoid mistakes, but better to do it at their own expense - kaden_Franecki commented on March 23rd 20 at 19:26
March 23rd 20 at 18:49
If you don't want to do quite complicated mathematical calculations, you'll find it easier to have a separate table with distances: from, to, and distance and have it do the conversion. Well, respectively, every time to update.

On updates of course will lose, but the select is very fast.
that is, if the points table is 1000 entries, then ties will be 1000*1000? Will it be faster? - crystal35 commented on March 23rd 20 at 18:52
@crystal35, Yes, it will.
Once you have 10 million points, then they will have questions, but until such an option. - Jarrett_Remp commented on March 23rd 20 at 18:55
March 23rd 20 at 18:51
You can count, it will have to go through all the table rows and for each point calculate the distance to your point and then sort. But if you want to select only a few nearby points, or just points not too remote from the target, you can use the technique of bounding-box: imagine that the point of a cube or square of a given size and to spend the first calculation within the boundaries of the cube. This will allow somehow to use indices.

Find more questions by tags Database designSQL