473,322 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Find Nearest Sides (Polygon)

AccessIdiot
493 256MB
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
41 3928
ADezii
8,834 Expert 8TB
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
1,134 Expert 1GB
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
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
8,834 Expert 8TB
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
2,653 Expert 2GB
Try This...
Bravo. An impressive solution.

Perfect logic, elegant coding and nice styling.
Apr 29 '08 #7
AccessIdiot
493 256MB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
493 256MB
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
1,134 Expert 1GB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
1,134 Expert 1GB
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
8,834 Expert 8TB
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
1,134 Expert 1GB
In this case I vote that VBA will win
Apr 30 '08 #17
NeoPa
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
1,134 Expert 1GB
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
8,834 Expert 8TB
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
1,134 Expert 1GB
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
2,653 Expert 2GB
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, 124 views)
Apr 30 '08 #23
AccessIdiot
493 256MB
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
32,556 Expert Mod 16PB
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
493 256MB
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
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
493 256MB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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

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

Similar topics

1
by: paul | last post by:
library for querying virtual polygon over raster spatial data in my c++ application, after calculating my polygon i wish to define it (simple 4 sided polygon with floats or line parameters) and...
2
by: Shamli | last post by:
I am looking for an algorithm that enlarge a 2D polygon. cheers,
6
BSOB
by: BSOB | last post by:
im gone for a long weekend so plenty of time for anyone to answer. if i have 4 points representing a polygon and each point is represented by an x and a y coordinate, is there an easy (or slightly...
1
by: renu | last post by:
Hello, I have drawn polygon on window. And I want to check wheather given point is in that polygon region or not? How shold I find that? I have created object of class region GraphicsPath path...
1
by: jojojjose | last post by:
function draw() { var s=''; s+='<v:polygon points="'; s+= //points from server s+="> </v:polygon>'"; document.wrie(s); }
3
by: jojo41300000 | last post by:
Hi, Is anyone know that how to get the x and y points inside the polygon using C++ program? I have the given polygon data to draw the polygon, but i don't know how to get all the points...
2
by: washakie | last post by:
Hello, I have a list of datetime objects: DTlist, I have another single datetime object: dt, ... I need to find the nearest DTlist to the dt .... is there a simple way to do this? There isn't...
2
by: Swan666 | last post by:
I have a function which creates a polygon of blue color. I call this function inside a loop of 128 iterations. For every iteration, i change the value of x and y so that my polygon is rendered on new...
8
by: jpatchak | last post by:
Hello, I am having a problem using intersects method of a class I made that extends Polygon. Basically, I am trying to write code that allows the user to drag these polygons. I create a 1x1...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.