Find Nearest Sides (Polygon)

[AccessIdiot]

I have a puzzle for all you sql fans.

Imagine a polygon with 12 sides (SideA, SideB, SideC, etc)

Within this polygon are 96 points (Pt1, Pt2, Pt3, etc).

Now let's say I have a table that has the distances from each point to each side
Point Side Distance
Pt1 SideA 25.6683
Pt1 SideB 37.6778
Pt1 SideC 576.8383
Pt2 SideA 352.2652
Pt2 SideB 3.8953
Pt2 SideC 46.9563


etc.

What I need to do is, for each point, find the two closest sides. So I need to sort on point name, find the two minimum values in Distance for that point, and report the side.

How do I do this? We are having a hard time wrapping our brains around the correct syntax.

Thanks for any help!

[ADezii]

I have a puzzle for all you sql fans.

Imagine a polygon with 12 sides (SideA, SideB, SideC, etc)

Within this polygon are 96 points (Pt1, Pt2, Pt3, etc).

Now let's say I have a table that has the distances from each point to each side
Point Side Distance
Pt1 SideA 25.6683
Pt1 SideB 37.6778
Pt1 SideC 576.8383
Pt2 SideA 352.2652
Pt2 SideB 3.8953
Pt2 SideC 46.9563


etc.

What I need to do is, for each point, find the two closest sides. So I need to sort on point name, find the two minimum values in Distance for that point, and report the side.

How do I do this? We are having a hard time wrapping our brains around the correct syntax.

Thanks for any help!

I don't think that it would be much of a problem to derive a code based solution, but SQL is my weakness. I'm imagine that it would have to involve a Sub-Query, something along the lines of:
[CODE=sql]
SELECT tblPolygon.Poin t, tblPolygon.Side , tblPolygon.Dist ance
FROM tblPolygon
WHERE tblPolygon.Dist ance IN
(SELECT TOP 2 Distance
FROM tblPolygon AS Dupe
WHERE Dupe.ID = tblPolygon.ID
ORDER BY Dupe.Distance, Dupe.ID)
ORDER BY tblPolygon.Poin t;[/CODE]
NeoPa is the expert in this area and I'll call him in on this. If you still want a code based solution, let me know and I'll see what I can do.

[Delerna]

Try This

[code=sql]
SELECT Point,Side,Dist ance
FROM
( SELECT a.Point,a.Side, a.Distance,
( SELECT Count(Point)
FROM tblPointDistanc es b
WHERE a.Point=b.Point
AND a.Distance>b.Di stance
)+1 AS Pos
FROM tblPointDistanc es a
)z
WHERE pos<3

[/code]

I dont know the name of your table so I used tblPointDistanc es

[NeoPa]

I'll have a look at this as soon as I get a spare 1/2 an hour or so.

In the mean-time, let us know if any of the suggestions so far submitted work (may save me the effort ;)).

[ADezii]

I'll have a look at this as soon as I get a spare 1/2 an hour or so.

In the mean-time, let us know if any of the suggestions so far submitted work (may save me the effort ;)).

Save yourself the trouble, NeoPa, since Delerna's solution works quite well. Thanks again.

[ADezii]

Try This

[code=sql]
SELECT Point,Side,Dist ance
FROM
( SELECT a.Point,a.Side, a.Distance,
( SELECT Count(Point)
FROM tblPointDistanc es b
WHERE a.Point=b.Point
AND a.Distance>b.Di stance
)+1 AS Pos
FROM tblPointDistanc es a
)z
WHERE pos<3

[/code]

I dont know the name of your table so I used tblPointDistanc es

Very nice solution, Delerna!

[FishVal]

Try This...

Bravo. An impressive solution.

Perfect logic, elegant coding and nice styling.

[AccessIdiot]

Harrumph, so much for "instant email notifcation"!

Thanks all for weighing in, I'm going to run this right now and see if it works!

*edit* okay, I'm a SQL noob too - WHERE do I write this? Can I create a new query and write that into the SQL view? Or should I do it in VBA in a DoCmd.RunSQL statement?

And how does the a. b. work? I know they're aliases but I don't know how that works.

The table is named "Nn" (I didn't create it) and the fields are SOURCEUID (the points), TARGETUID (the polygon sides), and DISTANCE.

This is the query I came up with in the SQL view of the query builder:

SELECT SOUREUID, TARGETUID, DISTANCE
FROM [SELECT a.SOURCEUID, a.TARGETUID, a.DISTANCE,
(SELECT Count(SOURCEUID) FROM Nn b WHERE a.SOURCEUID = b.SOURCEUID AND a.DISTANCE>b.DISTANCE)
+1 AS Pos
FROM Nn a
]. AS z
WHERE pos<3;

But that's not right and it just sort of hangs in a scary way.

Thanks for any help!

[NeoPa]

Harrumph, so much for "instant email notifcation"!
...

That's very dodgy at the moment. Every time you post to the thread this will be reset unless you explicitly set it again while editing the recently added post.

...*edit* okay, I'm a SQL noob too - WHERE do I write this? Can I create a new query and write that into the SQL view? Or should I do it in VBA in a DoCmd.RunSQL statement?
...

Either will work.

Typically, if wanting to design a QueryDef object do the former. This can then be tweaked as desired (Set timeout values etc etc.

...And how does the a. b. work? I know they're aliases but I don't know how that works.
...

Everywhere the table name could normally be used, the alias can be used instead. This is useful for using shorter or more meaning ful names for recordsets. It's also very useful for assigning usable names to subqueries within your SQL.

...
The table is named "Nn" (I didn't create it) and the fields are SOURCEUID (the points), TARGETUID (the polygon sides), and DISTANCE.

This is the query I came up with in the SQL view of the query builder:
[code=SQL]SELECT SOUREUID, TARGETUID, DISTANCE
FROM [SELECT a.SOURCEUID, a.TARGETUID, a.DISTANCE,
(SELECT Count(SOURCEUID ) FROM Nn b WHERE a.SOURCEUID = b.SOURCEUID AND a.DISTANCE>b.DI STANCE)
+1 AS Pos
FROM Nn a
]. AS z
WHERE pos<3;[/code]

But that's not right and it just sort of hangs in a scary way.

Thanks for any help!

Try (as a specific version of Delerna's code) :
[code=SQL]SELECT SourceUID, TargetUID, Distance
FROM (SELECT a.SourceUID,
a.TargetUID,
a.Distance,
(SELECT Count(SourceUID )
FROM Nn AS b
WHERE a.SourceUID=b.S ourceUID
AND a.Distance>b.Di stance)+1 AS Pos
FROM Nn AS a) AS z
WHERE Pos<3;[/code]

[NeoPa]

Harrumph, so much for "instant email notifcation"!
...

That's very dodgy at the moment. Every time you post to the thread this will be reset unless you explicitly set it again while editing the recently added post.

...*edit* okay, I'm a SQL noob too - WHERE do I write this? Can I create a new query and write that into the SQL view? Or should I do it in VBA in a DoCmd.RunSQL statement?
...

Either will work.

Typically, if wanting to design a QueryDef object do the former. This can then be tweaked as desired (Set timeout values etc etc).

...And how does the a. b. work? I know they're aliases but I don't know how that works.
...

Everywhere the table name could normally be used, the alias can be used instead. This is useful for using shorter or more meaning ful names for recordsets. It's also very useful for assigning usable names to subqueries within your SQL.
In this case, there is a subquery within a subquery. For the inner subquery to refer to the recordset (table) in the first subquery, it must refer to it by the alias (a - see lines 7 & 8 of the suggested version below).

...
The table is named "Nn" (I didn't create it) and the fields are SOURCEUID (the points), TARGETUID (the polygon sides), and DISTANCE.

This is the query I came up with in the SQL view of the query builder:
[code=SQL]SELECT SOUREUID, TARGETUID, DISTANCE
FROM [SELECT a.SOURCEUID, a.TARGETUID, a.DISTANCE,
(SELECT Count(SOURCEUID ) FROM Nn b WHERE a.SOURCEUID = b.SOURCEUID AND a.DISTANCE>b.DI STANCE)
+1 AS Pos
FROM Nn a
]. AS z
WHERE pos<3;[/code]

But that's not right and it just sort of hangs in a scary way.

Thanks for any help!

Try (as a specific version of Delerna's code) :
[code=SQL]SELECT SourceUID, TargetUID, Distance
FROM (SELECT a.SourceUID,
a.TargetUID,
a.Distance,
(SELECT Count(SourceUID )
FROM Nn AS b
WHERE a.SourceUID=b.S ourceUID
AND a.Distance>b.Di stance)+1 AS Pos
FROM Nn AS a) AS z
WHERE Pos<3;[/code]
PS. The [...]. version (in your code) is a known bug/feature of MS Access SQL as provided by the Query design. See Access QueryDefs Mis-save Subquery SQL for more on this.

[AccessIdiot]

Yeah! Works great, though very very slow - even scrolling through the resulting table causes it to hang.

But it's exactly what we're after - thanks!!

Very complicated query and not something I could have come up with. Very elegant!

[Delerna]

yes that is the most annoying bug/feature ever.
Everytime you go back into Query view, access replaces the () of the subquery with [].

Whats worse is the query won't work like that. If you replace the []. with the proper () and save and exit, everything works fine (assuming the query is written correctly of course).

I find that while developing a query, if I highlight the query code and copy it before I save and exit, then if my change didn't work I can go back to Query view
get rid of the access changed query and paste my copy back. Then I can make some changes, take another copy, save and exit, and test the query. Round and Round that goes until the query works as intended

[NeoPa]

I knocked up a version which is fundamentally the same as Delerna's (uses the same logic) but is slightly tweaked (added ORDER BY, named subqueries to reflect what they do, etc).
[CODE=SQL]SELECT SourceUID,
TargetUID,
Distance
FROM (SELECT SourceUID,
TargetUID,
Distance,
(SELECT Count(*)
FROM Nn AS iNn
WHERE iNn.SourceUID=o Nn.SourceUID
AND iNn.Distance<oN n.Distance) AS Pos
FROM Nn AS oNn) AS sNn
WHERE Pos<2
ORDER BY SourceUID,
Pos[/CODE]
PS. The prefixes i; o; & s (iNn; oNn & sNn) stand for :
i=Inner
o=Outer
s=Subquery

[NeoPa]

yes that is the most annoying bug/feature ever.
...

Are you still using A2000 by any chance?
I found that A2003, while still returning the [...]. SQL, nevertheless handles it ok when it tries to re-parse it.

PS. I did try to use the following version without success, as the WHERE clause usage of the Pos field causes it to be tested before it's evaluated :(
Otherwise that would save a level of subquery, but it was not to be :(
[CODE=SQL]SELECT oNn.SourceUID,
oNn.TargetUID,
oNn.Distance,
(SELECT Count(*)
FROM Nn AS iNn
iNn.SourceID=oN n.SourceID) AS Pos
FROM Nn AS oNn
WHERE Pos<2[/CODE]

[Delerna]

I like your idea NeoPa
this would probably do what you were trying
[code=sql]
SELECT oNn.SourceUID,
oNn.TargetUID,
oNn.Distance
FROM Nn AS oNn
WHERE (SELECT Count(*)
FROM Nn AS iNn
iNn.SourceID=oN n.SourceID)<2
[/code]
I don't know how that would affect speed though, probably not by much if anything at all.


If speed is a problem, I would add a field to the table to hold the pos and run this
[code=sql]
SELECT Count(*)
FROM Nn AS iNn
iNn.SourceID=oN n.SourceID
[/code]
modified to act as an update query on the new field.
The update query could then be run before the select query.
That way, the position determining query would only be called once instead of once every row. That should improve performance considerably

Yes I am still using 2000. I do have office 2007 but I hate it.I'll have to get used to it one day I suppose