[RESOLVED] Location Query

[Shaggy Hiker]

I have a complicated query that I'd like some advice on. The ultimate goal is to find the waypoint in a table that is closest to some lat/lon that are supplied.

The closest would be minimizing the distance using the Haversine formula, which is straightforward. But that distance isn't the ultimate goal. The waypoints may or may not be used in a survey, and I want only the ones that ARE used in the survey, and the return is a field from that survey. From there, it gets a bit worse.

The survey is table S, which has two child tables A and B. The waypoints, if they tie to anything, tie to either A or B, or one of two fields in the Survey S.1 or S.2.

In code I do (something like) this by getting all the waypoints that join to A, then getting all the waypoints that join to B, then adding in any waypoints that join to S.1 or S.2. That would get me the set of all waypoints that are used by that survey.

In SQL, I could do the same thing with a UNION query to get all the waypoints that join to any A, and all the waypoints that join to any B, and all the waypoints that join to any S.1 and all the waypoints that join to any S.2. That UNION could have the lat/lon of the waypoint along with the one field from S that needs to be returned, which I'll call S.X.

From that UNION, I would then be getting the S.X with the minimum distance from the target lat/lon.

That all seems viable, but it doesn't seem particularly efficient, so I figured I'd ask for a solution that would potentially avoid the UNION, which would really be the UNION of three sub queries. Is there a better way?

[Zvoni]

that's the crucial part:

if they tie to anything, tie to either A or B, or one of two fields in the Survey S.1 or S.2.

So, it's EITHER A or B, or ...... what exactly? EITHER S1. or S2? or is it EITHER A or B or S1 or S2?

My question targets "Is it possible that a waypoint has a "duplicate" in any of those 4 fields"!

A UNION is not worse than any JOIN
It becomes WORSE if there are duplicates that have to be DE-duplicated.
Then a UNION gets expensive

and btw: Didn't you have something like this a few Months (Years?) ago?
I distinctly remember, you had a question like this before

[Shaggy Hiker]

That first question is one that I've been pondering for months. A single waypoint could be tied to multiple A or multiple B, but I believe that it should never be tied to an A and a B...except that I can't be certain that nobody would use one waypoint for both. I'm also not sure about the S1 or S2 fields. One of those could EASILY be associated with an A, and possibly with a B. In all cases, it would mean that somebody got lazy, but that could happen.

One thing I have determined since I posted the question is that none of this matters and I'm not quite sure why (though it's certain that I'm the reason, and that I made the change, I just don't remember when this change was made or why). Perhaps I did ask this very question back then, I'll have to go look.

So, now it doesn't matter so much, though I'm still interested in the question. Would it really matter to de-duplicate anything if all I will ever be doing is getting the Top (1) of the Min distance? If there are duplicates, they'd sort in whatever order, since they'd have the same minimum distance, and it wouldn't matter which was first, as either way I'd get the same answer.

EDIT: After looking at threads I have started, I found a couple that were asking about different aspects of this data, but were never this question. It's something that I have played around with over the last couple decades, so I'm not surprised I've had a couple questions about these queries. In fact, at one point, every waypoint was associated with a survey, which makes some searches easier. Once that connection was severed, that may be when I moved the key field into the waypoint table, though I don't remember.

[Zvoni]

Well, let's say:
If we talk total results are in the 100-200 Result-Records (incl. duplicates), you won't notice a difference in a UNION (De-Deduplicates) vs. UNION ALL (doesn't remove duplicates)

If we're talking Results above 1,000 then there might be noticable differences in performance, tough it depends on how many fields the engine actually has to compare to each other

[Shaggy Hiker]

It would have to compare two Decimal fields, which might be fairly quick comparisons. As to the numbers...you hit it about right. It's not a truly massive number of records, but it is certainly over 1000, though probably not 10000.

I would expect UNION ALL to be faster than UNION. If there were 5 identical "best" records, I wouldn't care which of the five I looked at, so the first would be as good as any other, so I could use the faster. Of course, the Sort would be slower with more items, but there would be few duplicates....or maybe not. The more I think about it, there should be NO duplicates for the VAST majority of the surveys, but then there are a small number of peculiar surveys that will have both a large number of child records AND they will ALL be duplicates, so the actual number of duplicate records in a UNION ALL could be sufficient that the Sort would be noticeably slower.

I guess that's one I'd just have to test. I can't decide whether the cost of de-duplicating would exceed the cost of the sort with duplicates remaining.

[Zvoni]

IIRC, de-duplicating is way more expensive than sorting

[Zvoni]

Just had an idea for your performance-Tests:
Since you are in a UNION ALL, and you mentioned a "TOP 1":
Write your 4 (Sub-) Queries already including the "TOP 1" including sorting WITHIN the Subquery.
That way the UNION ALL itself ships only 4 Result-Records

Reasoning: You said you are only interested in the smallest/lowest (?) value of the complete UNION
That means: the smallest/lowest value you are looking for MUST be one of the smallest/lowest value of either of the 4 subqueries

btw: Instead of TOP 1 with Sorting, what about MIN-Function? Not withstanding other Fields you have to ship (like: Do you need the ID of the smallest Value?)

i don't think i have to explain how to proceed from there

Try different approaches and look at the execution-plan.

I wouldn't be surprised, if the 4 separate "TOP 1" are quite fast for themselves, and a UNION ALL combining 4 Resultrecords.....

[Shaggy Hiker]

I always assumed that Min and Sort were virtually the same, but they really aren't. Sort does a lot more work, while Min is a single pass, at most. I was only saying Sort because I figured that's what the query would do to get the Min, which is not the case.

Anyways, you are right in your reasoning. Getting the Min for each of the UNION ALL would work well because the true min would be one of those.

I'd say this is resolved, now. Thanks for your input.

[Zvoni]

As a last option:
read this thread: https://microsoft.public.sqlserver.s...ndexing-floats
and think about maybe putting an index on your value columns

[RedHeadedLefty]

This is a GIS problem. SQL Server actually supports Geography spatial types, which allows for geographical spatial math (like distance) on those coordinates.