Find the closest X, Y, Z coordinate in query 1

[tubbers]

I'm hoping that someone can help me figure out how to do this. I've done some searches but I haven't been able to decipher what exactly is being done in the other queries.

I have a table, PositionSets, which lists the X, Y, and Z coordinates for 15 different turbine units (fields: UnitName, PointX, PointY, PointZ).

I have a query, ExitAssignment, which lists the last X, Y, and Z of each Tag (fields TagCode, PointX, PointY, PointZ).

What I need to do is create a query where I can get the UnitName from PositionSets where that unit's X, Y and Z is closest to the Tag's X, Y and Z.

Does that make sense? I don't know if this is something terribly easy or complicated. I didn't find a lot of information.

Sample data:

table PositionSets
UnitName PointX PointY PointZ
TU1 2132201 194401 601
TU2 2132259 194458 698
TU3 2132515 194613 704

query ExitAssignment
TagCode PointX PointY PointZ
3996 2132487 194626 590
4030 2132231 194506 686
4056 2132310 194434 682
5201 2134579 195013 704

I don't know the math, so I'm not sure if you need to evaluate all three points (X,Y,Z) to find the nearest or just X and Y.

Any help is greatly appreciated. Thanks.

 

[PHV]

I don't know the math
You have to know how to calculate the distance between 2 points and take the min.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

Insufficient details to resolve. Do not know the relationship between enities referenced e.g. TagCode not seen in other structure and UnitName not rrelated to query results.





MichaelRed

 

[PHV]

Michael, I think the relationship is the minimal distance ...

 

[MichaelRed]

So we would be looking at the 3-D matrix of points. Comparing the resolved distance of each point to every other point, searching for the MIN, but eliminating those where the "ID" (TU? vs numeric text) are in the two domains?

I suppose it MIGHT be assumed (from the nomenclature) that the points represent the Cartesian (as opposed to spherical) coordinate system?

So these would reduce to DeltaX + DeltaY + DeltaZ between each pair of points?

This is not "SO BAD":

MyTables:
tblPosSets

UnitName	PosX	PosY	PosZ
TU1	        2132201	194401	601
TU2	        2132259	194458	698
TU3	        2132515	194613	704
[code]

[b]tblExit[/b]
[code]
TagCode	EPosX	EPosY	EPosZ
3996	2132487	194626	590
4030	2132231	194506	686
4056	2132310	194434	682
5021	2134579	195013	704

the function

Public Function bas3dDelta(ParamArray XYZPos() As Variant) As Double


    Dim PartDelta As Double
    Dim Idx As Integer

    While Idx <= 2

        PartDelta = PartDelta + Abs(XYZPos(Idx) - XYZPos(Idx + 3))
        Idx = Idx + 1
    Wend

    bas3dDelta = PartDelta
End Function

the SQL Query

SELECT tblPosSets.UnitName, tblPosSets.PosX, tblPosSets.PosY, tblPosSets.PosZ, tblExit.TagCode, tblExit.EPosX, tblExit.EPosY, tblExit.EPosZ, bas3dDelta([PosX],[PosY],[PosZ],[EPosX],[EPosY],[EPosZ]) AS Pos2Exit
FROM tblPosSets, tblExit
ORDER BY tblPosSets.PosX
WITH OWNERACCESS OPTION;

the Results(set)

UnitName	PosX	PosY	PosZ	TagCode	EPosX	EPosY	EPosZ	Pos2Exit
TU1	2132201	194401	601	5021	2134579	195013	704	3093
TU1	2132201	194401	601	4056	2132310	194434	682	223
TU1	2132201	194401	601	4030	2132231	194506	686	220
TU1	2132201	194401	601	3996	2132487	194626	590	522
TU2	2132259	194458	698	5021	2134579	195013	704	2881
TU2	2132259	194458	698	4056	2132310	194434	682	91
TU2	2132259	194458	698	4030	2132231	194506	686	88
TU2	2132259	194458	698	3996	2132487	194626	590	504
TU3	2132515	194613	704	5021	2134579	195013	704	2464
TU3	2132515	194613	704	4056	2132310	194434	682	406
TU3	2132515	194613	704	4030	2132231	194506	686	409
TU3	2132515	194613	704	3996	2132487	194626	590	155

Of course, the above assumes a SIMPLISTIC view of the 'what is wanted here'




MichaelRed

 

[MichaelRed]

OOPs. Forgot to mention / note that another step is necessary. Select Min(Pos2Exit) for each UnitName where UnbitName like TU?





MichaelRed

 

[Golom]

Assuming that X, Y, Z are 3-D coordinates

Build A Query called "Dist"

SELECT P.UnitName ,  T.TagCode, Abs(Sqr([P].[posx]*[P].[posX]+[P].[Posy]*[P].[Posy]+[P].[PosZ]*[P].[PosZ])-Sqr([T].[posx]*[T].[posX]+[T].[Posy]*[T].[Posy]+[T].[PosZ]*[T].[PosZ])) AS D
FROM PositionSets AS P, ExitAssignment AS T;

and then another query to find the minimums

SELECT P1.UnitName, D.TagCode, D.D AS Distance

FROM PositionSets AS P1, Dist As D

WHERE D.D = (Select MIN(D) From Dist Where Dist.UnitName = P1.UnitName)

Gives you something like
[tt]
UnitName TagCode Distance
TU3 3996 26.737
TU1 4030 39.437
TU2 4030 23.528
[/tt]

 

[MichaelRed]

bah ... humbug ... elitist ... show-off ...

he didn't ask for actual VALUES, just which pair(s) are closest.

MichaelRed

 

[tubbers]

I'de like to thank you both for your suggestions and help.

BTW - X,Y,Z are 3D coordinates.

MichaelRed, when I tried to use your example, there was a problem with using "With OwnerAccess Option". I removed that line from the query and it worked.

Golom, I like the idea of having the distance value because I'm sure at some point, someone is going to want to know just how close the tag goes to the unit.

However, when I run the second query to find the minimums, it takes forever to calculate (I gave up after 1/2 hour). Is there a way to speed this up?

 

[PHV]

Transform the 1st query to a make table query and use the newly created table in the 2nd query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

PHV's absolutely right. The second query is a correlated sub query and is run for every record when it exists as a query. If you do a make table and then use the table in place of the query then the operation is just filtering on the table.

Shoulda thought of that.

 

[tubbers]

That worked great. Thanks.

 

[MichaelRed]

hmmmmmmmmmmmmmmmmm ... mokey wrench time ... mmmmmmmmmmmmm


The "soloution" [sqr(A^2 + b^2)] applies to RIGHT triangles in TWO (2) dimensional (read Retangular) coordinate systems. There is no "proof" that the given coordinates of any object (or the identified points thereof) form such a figuure (right Triangl). Furthermore we know the coordinate system is a three-D system. Plane geometry (see soloution / formula above) does NOT apply. The soloution needs to be 'translarted to "Solid Geometry", and use a bit more calculation to resolve.

Solid Geom. is one of the many math skills I would need to 'review' before getting into a soloution which I might want to 'show to the boss' (after all they MAY have just recently read up on the topic).





MichaelRed

 

[Golom]

MichaelRed
Indeed so. I made the assumption that X, Y and Z were measurements made on the axes of a rectangular 3D coordinate system. (i.e. one in which X, Y and Z are orthogonal.) If that's the case then any pair of coordinates defines a right triangle and any point in a plane (the XY plane for example) and a point on the axis orthogonal to that plane (Z) defines another right triangle. The expression is then just shorthand for

Q = Sqr (X^2 + Y^2) - The XY Plane Position.

D = Sqr (Q^2 + Z^2) - The 3D Position.

At least that's what I vaguely remember from Vector Analysis in another life.

 

[tubbers]

I guess I will have to try and find out more information if the original solutions aren't correct.

I asked one of my co-workers about the 3D coordinates and he's as inexperienced as I am. He did provide me with some images from a program called TechPlot that we use to generate 3D renderings of a tag.

It looks like it is a rectangular 3D coordinate system to me. The images are here, if you want to take a look:

http://roena.net/side.jpg

http://roena.net/angle.jpg

http://roena.net/plan.jpg

(The blue dots represent our hydrophones)

 

[Golom]

Try this mental experiment. Suppose that

X = 3
Y = 4
Z = 12

then

On the XY Plane the distance from the origin (0,0,0) is

Sqr (3[sup]2[/sup] + 4[sup]2[/sup]) = 5

Now rotate the XY-plane around the Z-Axis until the X axis is running along the hypotenuse of the triangle on the XY plane. The hypotenuse ends at an X-coordinate of 5. (Rotation doesn't change the geometry of the system since we are not changing the scale or angles of the system.)

Now, on the new XZ axis we have another right triangle with sides 5 (the hypotenuse from XY) and 12 (the Z-coordinate). The hypotenuse from that triangle is

Sqr (5[sup]2[/sup] + 12[sup]2[/sup]) = 13

That distance is of course the distance from the origin to the point (3,4,12).

But that's just

Sqr (3[sup]2[/sup] + 4[sup]2[/sup] + 12[sup]2[/sup]) or

Sqr (X[sup]2[/sup] + Y[sup]2[/sup] + Z[sup]2[/sup])

This would be easier to illustrate in pictures but this running off at the keyboard will have to suffice.

 

[tubbers]

Thanks Golom, that makes a bit more sense to me.

 

[MichaelRed]

While I bow to the expert memory, my ref for solid geo, suggests a difference, in that the final equation needs to have the first term completly embeded in the final equation, and that it does make a difference. I adapted the Golem's "Example" to a demo function:

Public Function bas3dDistExample(X As Double, Y As Double, Z As Double) As Double

    Dim MyDist(5) As Double

    'Try this mental experiment. Suppose that _
    X = 3 _
    Y = 4 _
    Z = 12

    'then On the XY Plane the distance from the origin (0,0,0) is
    MyDist(0) = Sqr((3 ^ 2) + (4 ^ 2))      '= 5

    'Now rotate the XY-plane around the Z-Axis _
     until the X axis is running along the hypotenuse _
     of the triangle on the XY plane.

    'The hypotenuse ends at an X-coordinate of 5. _
     (Rotation doesn't change the geometry of the system _
     since we are not changing the scale or angles of the system.)

    'Now, on the new XZ axis we have another right triangle _
     with sides 5 (the hypotenuse from XY) and 12 _
     (the Z-coordinate). The hypotenuse from that triangle is

    MyDist(1) = Sqr((5 ^ 2) + (12 ^ 2))     '=13

    'That distance is of course the distance from the origin to the point (3,4,12). _
     But that 's just

    MyDist(2) = Sqr((3 ^ 2) + (4 ^ 2) + (12 ^ 2))    'or

    MyDist(3) = Sqr((X ^ 2) + (Y ^ 2) + (Z ^ 2))

    MyDist(4) = Sqr(Sqr((X ^ 2) + (Y ^ 2)) + Z ^ 2)

    Debug.Print MyDist(0), MyDist(1), MyDist(2), MyDist(3), MyDist(4)

    bas3dDistExample = MyDist(4)

End Function

Just ttrying to see the error of my ways, so could you explain?




MichaelRed

 

[GoDawgs]

They don't need to be broken out...I think you missed a step (and "2" equals squared in my example):

The hypotenuse of the 1st triangle=
Sqr(x2+y2)

The hypotenuse of the 2nd triangle (and therefore the total distance)=
sqr((the hypotenuse of the first)2 + z2)
or
sqr(sqr(x2+y2)2 + z2)
or
sqr(x2+y2+z2)

In MyDist(4) you forgot to square the Sqr((X ^ 2) + (Y ^ 2)) portion

 

[Golom]

MichaelRed

If you're going to portray it that way then you need

MyDist(4) = Sqr((Sqr((X ^ 2) + (Y ^ 2))^2 + Z ^ 2)

since the corresponding term for which you are substituting is squared.

Of course Sqr ( X[sup]2[/sup]) = X

so the whole thing just reduces to X[sup]2[/sup] + Y[sup]2[/sup]