By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,059 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

Find Nearest Sides (Polygon)

AccessIdiot
100+
P: 493
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!
Apr 28 '08 #1
Share this Question
Share on Google+
41 Replies


ADezii
Expert 5K+
P: 8,636
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblPolygon.Point, tblPolygon.Side, tblPolygon.Distance
  2. FROM tblPolygon
  3. WHERE tblPolygon.Distance IN
  4. (SELECT TOP 2 Distance                    
  5.    FROM tblPolygon AS Dupe                              
  6.    WHERE Dupe.ID = tblPolygon.ID        
  7.    ORDER BY Dupe.Distance, Dupe.ID) 
  8. ORDER BY tblPolygon.Point;
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.
Apr 29 '08 #2

Delerna
Expert 100+
P: 1,134
Try This

Expand|Select|Wrap|Line Numbers
  1. SELECT Point,Side,Distance
  2. FROM
  3. (   SELECT a.Point,a.Side,a.Distance,
  4.              (   SELECT Count(Point) 
  5.                  FROM tblPointDistances b 
  6.                  WHERE a.Point=b.Point 
  7.                    AND a.Distance>b.Distance
  8.              )+1 AS Pos
  9.     FROM tblPointDistances a
  10. )z
  11. WHERE pos<3
  12.  
  13.  
I dont know the name of your table so I used tblPointDistances
Apr 29 '08 #3

NeoPa
Expert Mod 15k+
P: 31,489
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 ;)).
Apr 29 '08 #4

ADezii
Expert 5K+
P: 8,636
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.
Apr 29 '08 #5

ADezii
Expert 5K+
P: 8,636
Try This

Expand|Select|Wrap|Line Numbers
  1. SELECT Point,Side,Distance
  2. FROM
  3. (   SELECT a.Point,a.Side,a.Distance,
  4.              (   SELECT Count(Point) 
  5.                  FROM tblPointDistances b 
  6.                  WHERE a.Point=b.Point 
  7.                    AND a.Distance>b.Distance
  8.              )+1 AS Pos
  9.     FROM tblPointDistances a
  10. )z
  11. WHERE pos<3
  12.  
  13.  
I dont know the name of your table so I used tblPointDistances
Very nice solution, Delerna!
Apr 29 '08 #6

FishVal
Expert 2.5K+
P: 2,653
Try This...
Bravo. An impressive solution.

Perfect logic, elegant coding and nice styling.
Apr 29 '08 #7

AccessIdiot
100+
P: 493
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT SOUREUID, TARGETUID, DISTANCE
  2. FROM [SELECT a.SOURCEUID, a.TARGETUID, a.DISTANCE,
  3. (SELECT Count(SOURCEUID) FROM Nn b WHERE a.SOURCEUID = b.SOURCEUID AND a.DISTANCE>b.DISTANCE)
  4. +1 AS Pos
  5. FROM Nn a
  6. ]. AS z
  7. WHERE pos<3;
  8.  
But that's not right and it just sort of hangs in a scary way.

Thanks for any help!
Apr 29 '08 #8

NeoPa
Expert Mod 15k+
P: 31,489
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT SOUREUID, TARGETUID, DISTANCE
  2. FROM [SELECT a.SOURCEUID, a.TARGETUID, a.DISTANCE,
  3. (SELECT Count(SOURCEUID) FROM Nn b WHERE a.SOURCEUID = b.SOURCEUID AND a.DISTANCE>b.DISTANCE)
  4. +1 AS Pos
  5. FROM Nn a
  6. ]. AS z
  7. WHERE pos<3;
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) :
Expand|Select|Wrap|Line Numbers
  1. SELECT SourceUID, TargetUID, Distance
  2. FROM (SELECT a.SourceUID,
  3.              a.TargetUID,
  4.              a.Distance,
  5.              (SELECT Count(SourceUID)
  6.               FROM Nn AS b
  7.               WHERE a.SourceUID=b.SourceUID
  8.                 AND a.Distance>b.Distance)+1 AS Pos
  9.       FROM Nn AS a) AS z
  10. WHERE Pos<3;
Apr 29 '08 #9

NeoPa
Expert Mod 15k+
P: 31,489
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT SOUREUID, TARGETUID, DISTANCE
  2. FROM [SELECT a.SOURCEUID, a.TARGETUID, a.DISTANCE,
  3. (SELECT Count(SOURCEUID) FROM Nn b WHERE a.SOURCEUID = b.SOURCEUID AND a.DISTANCE>b.DISTANCE)
  4. +1 AS Pos
  5. FROM Nn a
  6. ]. AS z
  7. WHERE pos<3;
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) :
Expand|Select|Wrap|Line Numbers
  1. SELECT SourceUID, TargetUID, Distance
  2. FROM (SELECT a.SourceUID,
  3.              a.TargetUID,
  4.              a.Distance,
  5.              (SELECT Count(SourceUID)
  6.               FROM Nn AS b
  7.               WHERE a.SourceUID=b.SourceUID
  8.                 AND a.Distance>b.Distance)+1 AS Pos
  9.       FROM Nn AS a) AS z
  10. WHERE Pos<3;
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.
Apr 29 '08 #10

AccessIdiot
100+
P: 493
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!
Apr 29 '08 #11

Delerna
Expert 100+
P: 1,134
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
Apr 29 '08 #12

NeoPa
Expert Mod 15k+
P: 31,489
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).
Expand|Select|Wrap|Line Numbers
  1. SELECT SourceUID,
  2.        TargetUID,
  3.        Distance
  4. FROM (SELECT SourceUID,
  5.              TargetUID,
  6.              Distance,
  7.              (SELECT Count(*)
  8.               FROM Nn AS iNn
  9.               WHERE iNn.SourceUID=oNn.SourceUID
  10.                 AND iNn.Distance<oNn.Distance) AS Pos
  11.       FROM Nn AS oNn) AS sNn
  12. WHERE Pos<2
  13. ORDER BY SourceUID,
  14.          Pos
PS. The prefixes i; o; & s (iNn; oNn & sNn) stand for :
i=Inner
o=Outer
s=Subquery
Apr 29 '08 #13

NeoPa
Expert Mod 15k+
P: 31,489
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 :(
Expand|Select|Wrap|Line Numbers
  1. SELECT oNn.SourceUID,
  2.        oNn.TargetUID,
  3.        oNn.Distance,
  4.        (SELECT Count(*)
  5.         FROM Nn AS iNn
  6.         iNn.SourceID=oNn.SourceID) AS Pos
  7. FROM Nn AS oNn
  8. WHERE Pos<2
Apr 29 '08 #14

Delerna
Expert 100+
P: 1,134
I like your idea NeoPa
this would probably do what you were trying
Expand|Select|Wrap|Line Numbers
  1. SELECT oNn.SourceUID,
  2.        oNn.TargetUID,
  3.        oNn.Distance
  4. FROM Nn AS oNn
  5. WHERE  (SELECT Count(*)
  6.         FROM Nn AS iNn
  7.         iNn.SourceID=oNn.SourceID)<2
  8.  
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
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*)
  2.         FROM Nn AS iNn
  3.         iNn.SourceID=oNn.SourceID
  4.  
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
Apr 30 '08 #15

ADezii
Expert 5K+
P: 8,636
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!
When I get a chance, I'll work on a strictly code based solution which may/may not improve performance. It should be interesting to see how the Sub-Query vs VBA battle turns out. Stay tuned to this station!
Apr 30 '08 #16

Delerna
Expert 100+
P: 1,134
In this case I vote that VBA will win
Apr 30 '08 #17

NeoPa
Expert Mod 15k+
P: 31,489
Likewise Delerna. Very neat thinking. I should have thought of that technique after determining what caused my earlier attempt to fail.

Unfortunately, I couldn't get it to work :(

I did change it slightly. I suspect to what you'd intended.
Expand|Select|Wrap|Line Numbers
  1. SELECT SourceUID,
  2.        TargetUID,
  3.        Distance
  4. FROM Nn AS oNn
  5. WHERE (SELECT Count(*)
  6.        FROM Nn AS iNn
  7.        WHERE iNn.SourceID=oNn.SourceID
  8.          AND iNn.Distance>oNn.Distance)>2
It didn't seem to like the subquery in the WHERE clause at all.

PS. VBA may win in Access but generally SQL would work faster (in my experience). In another engine (EG. MS SQL, Oracle, etc) which better supports subqueries (Access is notorious for throwing up its hands in defeat when trying to optimise SQL subqueries or UNION queries) it would almost certainly be more efficient in SQL..
Apr 30 '08 #18

NeoPa
Expert Mod 15k+
P: 31,489
This is sort of a PPS, but as I noticed I was on 8,999 posts I thought "What the hell" and did it in a separate post :D

Anyway, AccessIdiot (M), I'm interested in your data running so slowly as mine wasn't too bad. Can you let me have a copy of your data for me to load into my version of this. I can PM you e-mail details again if you like or, if you still have them, please send it on as either a text file or in Excel or even a whole Access database if you can get it to be small enough (compression etc).
Apr 30 '08 #19

Delerna
Expert 100+
P: 1,134
I did change it slightly. I suspect to what you'd intended.
Expand|Select|Wrap|Line Numbers
  1. SELECT SourceUID,
  2.        TargetUID,
  3.        Distance
  4. FROM Nn AS oNn
  5. WHERE (SELECT Count(*)
  6.        FROM Nn AS iNn
  7.        WHERE iNn.SourceID=oNn.SourceID
  8.          AND iNn.Distance>oNn.Distance)>2
Yes, that looks more like it.

Yes I agree, in general SQL work faster than VB, for the same reason that record based cursors in SQL are slower than set based queries. Cursors have their place of course.
By the way, when I say I vote VBA will win I mean against the first solution. Not sure about the second
Apr 30 '08 #20

ADezii
Expert 5K+
P: 8,636
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!
I'm almost finished writing the code that will provide you the same functionality, but hopefully without the sluggishness that you are currently experiencing via the SQL route. There is one small detail that I would like to know. Can you be absolutely, positively sure that there will always be at least two distances for any given point. Should this be the case, code execution will be significantly faster for a large number of Records, because it does not have to traverse the Recordset (rst.MoveLast ==> rst.MoveFirst) in order to retrieve an accurate Record Count, and the dbOpenForwardOnly Option can also be used when opening the Recordset.
Apr 30 '08 #21

Delerna
Expert 100+
P: 1,134
There is a thread in the SQLSever forum Here with a very similar requirement.
This one wants the rows with the best 3 prices for models of cars. The technique is reported to be slow there also. I must say, most of my development is with SQLServer and I have used this technique myself on resonably large tables without too much of a speed issue. I do have indexes though.
Apr 30 '08 #22

FishVal
Expert 2.5K+
P: 2,653
Hello, all.

I've coded hydride SQL/VBA query.

Code Module
Expand|Select|Wrap|Line Numbers
  1. Public Function Enumerate(varPoint As Variant) As Long
  2.  
  3.     Static varCurPoint As Variant
  4.     Static lngNum As Variant
  5.  
  6.     If varCurPoint <> varPoint Then
  7.         lngNum = 0
  8.         varCurPoint = varPoint
  9.     End If
  10.     lngNum = lngNum + 1
  11.     Enumerate = lngNum
  12.  
  13. End Function
  14.  
Query
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl.*
  2. FROM tbl
  3. WHERE Enumerate(tbl.Point)<3
  4. ORDER BY tbl.Point, tbl.Distance;
  5.  
Seems to run faster, though Delerna's solution with subqueries runs reasonably fast too on 96x12=1152 records table (Access2003, Win XP SP2, Celeron 3.33GHz, RAM 512M).

Regards,
Fish
Attached Files
File Type: zip PolygonPoints.zip (23.1 KB, 92 views)
Apr 30 '08 #23

AccessIdiot
100+
P: 493
Wow this post has generated a lot of interest! :-)

This whole thing actually stems from a GIS field project with testing wells at a project site location. The data was stored in a geodatabase which is a fancy access database so yes, I have all the data stored in an mdb and am happy to send it to anyone who wants to see it (NeoPa I'll need your email again I think, please PM me). It's pretty big, about 9 MB, and was really more of an exercise in how it could be done than any kind of project deadline, so I wouldn't spend too much time on it, though it would be useful for future projects.

cheers!
Apr 30 '08 #24

NeoPa
Expert Mod 15k+
P: 31,489
I would think the easiest and lightest way to do it (all I require is the bare data) would be to open the table (Nn) and open up a new spreadsheet in Excel. When both are open, select the whole table's data (Square at top-left) and use Ctrl-C to copy it to the clipboard. When that process has completed, use Ctrl-V to paste it into the Excel spreadsheet. That should be a smaller file to e-mail.

This thread has generated interest for two reasons :
  1. It's a tricky / fiddly problem which are naturally interesting anyway.
  2. ADezii, PMed me (and possibly others too) to draw my attention to it as he's a naturally helpful chap and SQL is his weaker area (He's a VBA code guru).

PS. I'll PM you with my details later.
Apr 30 '08 #25

AccessIdiot
100+
P: 493
Will do. There are 162,000 records. Do you want the whole thing or just a subset? Let me know!
Apr 30 '08 #26

ADezii
Expert 5K+
P: 8,636
Here's a drastically different approach. Download the Attachment and see what you think.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL_1 As String
  2. Dim strSQL_2 As String
  3. Dim strSQLInsert As String
  4. Dim MyDB As DAO.Database
  5. Dim rstUniquePoints As DAO.Recordset
  6. Dim rstNn As DAO.Recordset
  7. Dim intCounter As Integer
  8.  
  9. DoCmd.SetWarnings False
  10.   DoCmd.RunSQL "DELETE * FROM tblFinalResults;"
  11. DoCmd.SetWarnings True
  12.  
  13. Set MyDB = CurrentDb()
  14.  
  15. 'Needed in order to return Unique Points in Ascending Sort Order
  16. strSQL_1 = "SELECT DISTINCT Nn.SOURCEUID FROM Nn ORDER BY Nn.SOURCEUID;"
  17. Set rstUniquePoints = MyDB.OpenRecordset(strSQL_1, dbOpenForwardOnly)
  18.  
  19. If rstUniquePoints.RecordCount = 0 Then Exit Sub
  20.  
  21. With rstUniquePoints
  22.   Do While Not .EOF
  23.     strSQL_2 = "SELECT * FROM Nn WHERE Nn.[SOURCEUID] = '" & ![SOURCEUID] & _
  24.                "' ORDER BY Nn.DISTANCE ASC;"
  25.     Set rstNn = MyDB.OpenRecordset(strSQL_2, dbOpenSnapshot)
  26.       If rstNn.RecordCount >= 2 Then      'need at least 2 Points
  27.         For intCounter = 1 To 2           'return 2 shortest Distances for the Point
  28.           strSQLInsert = "INSERT INTO tblFinalResults (SOURCEUID, TARGETUID, DISTANCE)" & _
  29.                          "Values ('" & rstNn![SOURCEUID] & "', '" & rstNn![TARGETUID] & "', " & _
  30.                          rstNn![DISTANCE] & ")"
  31.           MyDB.Execute strSQLInsert, dbFailOnError
  32.             rstNn.MoveNext
  33.         Next
  34.             rstNn.MoveFirst
  35.       End If
  36.     .MoveNext
  37.   Loop
  38. End With
  39.  
  40. rstNn.Close
  41. rstUniquePoints.Close
  42. Set rstNn = Nothing
  43. Set rstUniquePoints = Nothing
  44.  
  45. 'After all that work, let's see the results
  46. DoCmd.OpenTable "tblFinalResults", acViewNormal, acReadOnly
Apr 30 '08 #27

NeoPa
Expert Mod 15k+
P: 31,489
Will do. There are 162,000 records. Do you want the whole thing or just a subset? Let me know!
I'll have the whole lot if you can get them. Compress the file if it's that big please.

At that number of records it will be easiest to send the whole database.
Apr 30 '08 #28

NeoPa
Expert Mod 15k+
P: 31,489
Update :
Expand|Select|Wrap|Line Numbers
  1. Table Name=[Nn]
  2. Field;      Type;        IndexInfo
  3. ID;         AutoNumber;  PK
  4. SourceUID;  String
  5. TargetUID;  String
  6. Distance;   Numeric
There are various measurements (Distances) for each Point/Line as each line is considered to have multiple measurement positions (nodes).
As this may possibly yield a top 2 which both point to the same side (:() this will actually complicate the problem somewhat further. A fun intellectual exercise for us all I'm sure ;)

BTW This explains why there are more than the 1,152 records that would otherwise have been expected.
Apr 30 '08 #29

AccessIdiot
100+
P: 493
Yep, sorry about that all. The problem was explained to me verbally and then I was given the db and realized it was much bigger than originally explained. Looks like the guys that handed me the puzzle took the polygon and busted each side up into equally spaced nodes, the theory being that the shortest distance will be the most perpendicular, which is what they were after in the first place.

My apologies for not providing enough info.
Apr 30 '08 #30

NeoPa
Expert Mod 15k+
P: 31,489
I'm just busy at the moment getting timing info for the three states I'm interested in :
Expand|Select|Wrap|Line Numbers
  1. Main1 = PK=[ID] + Ix2=[SourceUID] + Ix3=[TargetUID]
  2. Main2 = PK=[ID] + Ix2=[SourceUID] + Ix3=[TargetUID] + Ix4=[SourceUID]&[TargetUID]
  3. Main3 = PK=[ID] + Ix2=[SourceUID] + Ix3=[TargetUID] + Ix4=[SourceUID]&[TargetUID]&[Distance]
When I received the database, the table Nn was set up as (approx) Main1. My expectation is that Main3 will provide the quickest results. I'm using the SQL from post #18 as the query, but saved into a pre-optimised QueryDef for consistency.

I'll post when the results arrive.
May 1 '08 #31

NeoPa
Expert Mod 15k+
P: 31,489
Well, that'll teach me.

I ran the tests only once, but my results were :
Expand|Select|Wrap|Line Numbers
  1. Main1: 01/05/2008 01:54:39 - 01/05/2008 02:02:03 = 7'24"
  2. Main2: 01/05/2008 01:34:21 - 01/05/2008 01:42:32 = 8'11"
  3. Main3: 01/05/2008 01:45:27 - 01/05/2008 01:53:01 = 7'34"
I may have to run them again after fiddling the SQL to return the correct results, but these results certainly indicate that the added index didn't help (as I would have anticipated).

Anyway, enough for tonight. I'm for bed :)

PS. My code was :
Expand|Select|Wrap|Line Numbers
  1. ?"Main1: " & Now(); : Call DoCmd.OpenQuery("qryTop2",acViewNormal) : ?" - " & Now()
I redesigned the table and changed the value in the first string between each run of course (and calculated the resultant time manually and added it afterwards).
May 1 '08 #32

NeoPa
Expert Mod 15k+
P: 31,489
My curiosity got the better of me so I tried running with only the ID index set, and using the following (more refined) code :
Expand|Select|Wrap|Line Numbers
  1. datBeg=Now() : _
  2.   ?"Main0: " & datBeg; : _
  3.   Call DoCmd.OpenQuery("qryTop2",acViewNormal) : _
  4.   datEnd=Now() : _
  5.   ?" - " & datEnd & " = " & Format(datEnd-datBeg,"h\'n\""")
I gave up after over 20 mins had elapsed with the output still saying :
Expand|Select|Wrap|Line Numbers
  1. Main0: 01/05/2008 02:23:33
:(
May 1 '08 #33

NeoPa
Expert Mod 15k+
P: 31,489
I produced some more SQL to handle this situation. It is a considerably more complicated situation than the previous one I'm afraid, and you can expect quite severely slowed performance.
Expand|Select|Wrap|Line Numbers
  1. SELECT SourceUID,
  2.        TargetUID,
  3.        Distance
  4. FROM (SELECT SourceUID,
  5.              TargetUID,
  6.              Distance,
  7.              (SELECT Count(*)
  8.               FROM (SELECT SourceUID,
  9.                            TargetUID,
  10.                            Min(Nn.Distance) As Distance
  11.                     FROM Nn
  12.                     GROUP BY SourceUID,
  13.                              TargetUID) AS iNn
  14.               WHERE iNn.SourceUID=oNn.SourceUID
  15.                 AND iNn.Distance<oNn.Distance) AS Pos
  16.       FROM Nn AS oNn) AS sNn
  17. WHERE Pos<2
  18. ORDER BY SourceUID,
  19.          Pos
I'm time-testing now but as I post it's taken over 20' :(
May 1 '08 #34

NeoPa
Expert Mod 15k+
P: 31,489
I set up the time trial with my best indexing and it still took over 1:30 (when I aborted).

I will have to think of a practicable way to handle this. It will almost certainly involve adding an ordinal field to the table and an update query being run first.

Disappointed -NeoPa.
May 2 '08 #35

NeoPa
Expert Mod 15k+
P: 31,489
As a last attempt before looking at a table update method, I've noticed that both SourceUID & TargetUID are text fields of 255 allowable characters. None of the values is longer than 7 characters so I will see what setting the field lengths to ten does. I also changed the index I created (SourceUID; TargetUID; Distance) to specify Unique.

I understand that the space taken up in the data is no more if the field is specified as 255 than if it's done as ten, but it may effect the indexing performance. I don't know. I'll see.
May 2 '08 #36

NeoPa
Expert Mod 15k+
P: 31,489
2.5 hours later there are still no results.

Regardless of whether or not this is more efficient, it's clearly not a solution that is workable in the real world.

Next:
Add a field into the table which is reset for every run. Essentially a scratch field which reflects the ordinal position of the Point/Side record by Distance. Maybe even a simple flag to indicate whether or not it's the closest.

That way a SELECT query can be run along similar lines to the earlier versions, but which ignores all but the nearest Distances from the start. A much more manageable set of records to deal with. I'll post progress.
May 3 '08 #37

NeoPa
Expert Mod 15k+
P: 31,489
Step 1:

The procedure (that works) is first of all to add a new Yes/No field called [Nearest] to the table. This is not added into any indexes. The recently added Main index (SourceUID; TargetUID; Distance) should still be in place.

The overall work is done by three queries (QueryDefs in Access). Remember, the optimisation of the QueryDef is done the first time it's run, so don't rely on timings of that run. Run normally, these QueryDefs in sequence, take very little more than 8 minutes in total. Considering the number of records that are to be processed this is not a long time at all.
May 3 '08 #38

NeoPa
Expert Mod 15k+
P: 31,489
Step 2:

[Nearest] must start off as set (to True) in all records. To do this we have a QueryDef called qryTopSet set up with the following SQL :
Expand|Select|Wrap|Line Numbers
  1. UPDATE Nn
  2. SET [Nearest]=True
I used the following code in the Immediate pane to run this and log the time taken :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False) : _
  2.   datBeg=Now() : _
  3.   ?"Main1: " & datBeg; : _
  4.   Call DoCmd.OpenQuery("qryTopSet",acViewNormal) : _
  5.   datEnd=Now() : _
  6.   Call DoCmd.SetWarnings(True) : _
  7.   ?" - " & datEnd & " = " & Format(datEnd-datBeg,"n\'s\""")
The results of this step were :
Expand|Select|Wrap|Line Numbers
  1. Main1: 03/05/2008 02:01:21 - 03/05/2008 02:01:28 = 0'7"
May 3 '08 #39

NeoPa
Expert Mod 15k+
P: 31,489
Step 3:

Next, the Point/Side records which have [Distance]s which are larger than ANY of the other matching Point/Side [Distance]s, must have [Nearest] reset (to False). To do this we have a QueryDef called qryTopReset set up with the following SQL :
Expand|Select|Wrap|Line Numbers
  1. UPDATE Nn AS Nn1 INNER JOIN Nn AS Nn2
  2.     ON (Nn1.TargetUID=Nn2.TargetUID)
  3.    AND (Nn1.SourceUID=Nn2.SourceUID)
  4. SET Nn1.Nearest=False
  5. WHERE (Nn1.Nearest)
  6.   AND (Nn1.Distance>Nn2.Distance)
I used the following code in the Immediate pane to run this and log the time taken :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False) : _
  2.   datBeg=Now() : _
  3.   ?"Main2: " & datBeg; : _
  4.   Call DoCmd.OpenQuery("qryTopReset",acViewNormal) : _
  5.   datEnd=Now() : _
  6.   Call DoCmd.SetWarnings(True) : _
  7.   ?" - " & datEnd & " = " & Format(datEnd-datBeg,"n\'s\""")
The results of this step were :
Expand|Select|Wrap|Line Numbers
  1. Main2: 03/05/2008 02:02:15 - 03/05/2008 02:10:11 = 7'56"
May 3 '08 #40

NeoPa
Expert Mod 15k+
P: 31,489
Step 4:

Now all the records are prepared. Only the nearest Point/Side records are flagged as [Nearest]. Now, and only now, are we in a position to execute the logic (code) that was originally considered for this problem in the knowledge that we're only processing through 1,152 records rather than 161,880. To do this we have a QueryDef called qryTop2 set up with the following SQL :
Expand|Select|Wrap|Line Numbers
  1. SELECT sNn.SourceUID,
  2.        sNn.TargetUID,
  3.        sNn.Distance
  4. FROM (SELECT SourceUID,
  5.              TargetUID,
  6.              Distance,
  7.              (SELECT Count(*)
  8.               FROM Nn AS iNn
  9.               WHERE iNn.Nearest
  10.                 AND iNn.SourceUID=oNn.SourceUID
  11.                 AND iNn.Distance<oNn.Distance) AS Pos
  12.       FROM Nn AS oNn
  13.       WHERE oNn.Nearest) AS sNn
  14. WHERE (sNn.Pos<2)
  15. ORDER BY sNn.SourceUID,
  16.          sNn.Pos
I used the following code in the Immediate pane to run this and log the time taken :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False) : _
  2.   datBeg=Now() : _
  3.   ?"Main3: " & datBeg; : _
  4.   Call DoCmd.OpenQuery("qryTop2",acViewNormal) : _
  5.   datEnd=Now() : _
  6.   Call DoCmd.SetWarnings(True) : _
  7.   ?" - " & datEnd & " = " & Format(datEnd-datBeg,"n\'s\""")
Expand|Select|Wrap|Line Numbers
  1. Main3: 03/05/2008 02:10:50 - 03/05/2008 02:10:57 = 0'7"
The results are a perfect set of 190 records (95 pairs) exactly as required :)
May 3 '08 #41

NeoPa
Expert Mod 15k+
P: 31,489
I'm curious to know how you got on with this. I received your e-mail, but I don't know if 8 minutes was within the sort of time-frame you'd envisaged as acceptable.
May 13 '08 #42

Post your reply

Sign in to post your reply or Sign up for a free account.