UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> A Little Help With Next Step, Please, Access 2016    
 
   
PhilnTx
post Jul 17 2017, 09:13 AM
Post#1



Posts: 34
Joined: 16-March 17



Hi All,
I’m new to Access 2016, and every other programming language, but I’m learning bit-by-bit.

I have a database with over 250,000 lon/lat points and about 4,000 polygons. The goal is to identify which points are in which polygons. The VBA code below works in finding multiple points within a single polygon, but I need help in taking it to the next step of now searching multiple polygons.
Attached is a picture and a Acces-2000 mdb
If there is a better method to approach this issue - please let me know.

Thanks so much in advance

CODE
Option Explicit

Public Function PtInPoly(Xcoord As Double, Ycoord As Double) As Variant
Dim X As Long, inPoly As String, NumSidesCrossed As Long, m As Double, b As Double, Poly As Variant
Dim Xx As Long, Yy As Long, Xupper As Long, Yupper As Long, tempArray As Variant
Dim dbs As DAO.Database
Dim Polyrst As DAO.Recordset

Set dbs = CurrentDb
Set Polyrst = dbs.OpenRecordset("SELECT x_nodes, y_nodes ,Poly_ID FROM Poly_ID_2only", dbOpenSnapshot)
    With Polyrst
        .MoveLast
        .MoveFirst
        Poly = .GetRows(.RecordCount)
    End With
'--- Problem is GetRows() returns a bass ackwards array, the code below fixes this -----------------
    Xupper = UBound(Poly, 2)
    Yupper = UBound(Poly, 1)

    ReDim tempArray(Xupper, Yupper)
    For Xx = 0 To Xupper
        For Yy = 0 To Yupper
             tempArray(Xx, Yy) = Poly(Yy, Xx)
        Next Yy
    Next Xx
    Poly = tempArray
'-----------------------------------------------------------
Debug.Print UBound(Poly) + 1 & " records retrieved."
    For X = LBound(Poly) To UBound(Poly) - 1
        If Poly(X, 0) > Xcoord Xor Poly(X + 1, 0) > Xcoord Then
            m = (Poly(X + 1, 1) - Poly(X, 1)) / (Poly(X + 1, 0) - Poly(X, 0))
            b = (Poly(X, 1) * Poly(X + 1, 0) - Poly(X, 0) * Poly(X + 1, 1)) / (Poly(X + 1, 0) - Poly(X, 0))
            If m * Xcoord + b > Ycoord Then NumSidesCrossed = NumSidesCrossed + 1
        End If
    Next
Debug.Print NumSidesCrossed + 1; "Lines Crossed"

If CBool(NumSidesCrossed Mod 2) = True Then
    inPoly = Poly(0, 2)
Else
    inPoly = "not in polygon"
End If

PtInPoly = inPoly
End Function


Attached File(s)
Attached File  TEST_Point_in_Polygon1.zip ( 42.34K )Number of downloads: 3
Attached File  TEST_Point_in_Polygon_.jpg ( 110.21K )Number of downloads: 10
 
Go to the top of the page
 
doctor9
post Jul 17 2017, 09:34 AM
Post#2


UtterAccess Editor
Posts: 17,593
Joined: 29-March 05
From: Wisconsin


PhilnTx,

Can you clarify what you want to do? "searching multiple polygons" is a little vague. Do you want to know if a point is within polygons 1, 2, or 3? Or Do you only want to search polygons 1, 2 and 3 for this point, and ignore other polygons?

Either way, you would probably just need to change your query's SQL.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
PhilnTx
post Jul 17 2017, 10:05 AM
Post#3



Posts: 34
Joined: 16-March 17



Thanks Dennis for your quick reply.

Absolutely, the points are addresses and the polygons are defined areas of the city, so a point can only be in one polygon or not in any at all. So I need to find the first polygon that a point resides in, then move on to the next point.

Can you expand a bit of what you mean by “change your query’s SQL”?

Thanks again
This post has been edited by PhilnTx: Jul 17 2017, 10:08 AM
Go to the top of the page
 
doctor9
post Jul 17 2017, 10:18 AM
Post#4


UtterAccess Editor
Posts: 17,593
Joined: 29-March 05
From: Wisconsin


PhilnTx,

If I'm reading your post correctly, it looks like you have a function that you've written that tells you whether a given x,y coordinate falls within any polygons.

If polygons truly represent a city, you wouldn't need to find "the first" polygon that a point resides in, surely. A point would only ever reside in one polygon, since city borders don't overlap. And doesn't the function already identify which polygon (if any) that a point resides in?

> Can you expand a bit of what you mean by “change your query’s SQL”?

If you want to list all of the points that within polygons 1, 2, or 3, you could add a WHERE clause to your query to only return records where the function returns a 1, 2 or 3 value.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
PhilnTx
post Jul 17 2017, 10:51 AM
Post#5



Posts: 34
Joined: 16-March 17




QUOTE
If I'm reading your post correctly, it looks like you have a function that you've written that tells you whether a given x,y coordinate falls within any polygons.


That’s my problem. The current function, as written, but can only search a “singular” polygon. So, I need a way to separate out each polygon (and all its associated nodes in absolute order) and then run this function against that. I don’t know how to write it to search for the next polygon’s set of nodes.

As far as the WHERE clause, I don’t know ahead of time the name of the polygons (the Poly_ID can be anything from 0 to ########) or how to make it loop through the list of Poly_IDs. So, I’m guessing I need some sort of multidimensional array that will list the Poly_IDs with start and end rows. If the WHERE clause can do that then I’m not sure how to structure it. This is where my ignorance REALLY shines :😉

Any examples you can point me too that does something similar?
Again, thanks for your help!
Go to the top of the page
 
doctor9
post Jul 17 2017, 12:58 PM
Post#6


UtterAccess Editor
Posts: 17,593
Joined: 29-March 05
From: Wisconsin


PhilnTx,

Ah! So the function does NOT work yet. Gotcha. I thought you were wondering how to use it. Okay, so the function looks like it's using a hard-coded query to grab just the data for poly #2. So, we need to loop through each poly, and exit when we find a match. Try this:

In your current function, change the header to:
CODE
Public Function PtInPoly(Xcoord As Double, Ycoord As Double, intPoly As Integer) As Variant

This will take intPoly as the polygon we want to check.

For the bit that sets the Recordset, use this instead:
CODE
Set Polyrst = dbs.OpenRecordset("SELECT x_nodes, y_nodes, Poly_ID FROM Polygons WHERE Poly_ID=" & intPoly, dbOpenSnapshot)

This will select the polygon data points from whichever polygon was specified.

That's it. Now we need a separate function to call this one repeatedly, once for each polygon:

CODE
Public Function FindPtInPoly(Xcoord As Double, Ycoord As Double) As Variant

    Dim dbs As DAO.Database
    Dim rstListOfPolys As DAO.Recordset, varPt As Variant

    Set dbs = CurrentDb
    
'   Create a list of unique Poly_ID values.
    Set rstListOfPolys = dbs.OpenRecordset("SELECT DISTINCT Poly_ID " & _
                                          "FROM Polygons " & _
                                          "ORDER BY Poly_ID;")
    
'   Loop through each Poly_ID
    While Not rstListOfPolys.EOF

'       Use the original function to return either a number or a failure message
        varPt = PtInPoly(Xcoord, Ycoord, rstListOfPolys!Poly_ID)

'       is the point within this polygon?
        If varPt <> "not in polygon" Then
'           Yes!  We're done.
            FindPtInPoly = varPt
            GoTo Cleanup
        End If
        
'       No.  Let's try the next polygon.
        rstListOfPolys.MoveNext
    Wend

'   We ran out of polygons!
    FindPtInPoly = "not in polygon"
    
Cleanup:
    rstListOfPolys.Close
    dbs.Close
    Set rstListOfPolys = Nothing
    Set dbs = Nothing
    
End Function


Finally, to fix the "Get PolyID for each Point" query, change the function call to this:
CODE
Within Poly_ID: FindPtInPoly([X_LON],[Y_LAT])

In other words, call the new function, not the original one.

Technically, you could probably combine these two functions into a single one, but I don't think you'd get a significant performance return. Plus, if the original function worked, I didn't want to mess with it more than I had to, since I didn't quite understand how it worked.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
PhilnTx
post Jul 17 2017, 01:59 PM
Post#7



Posts: 34
Joined: 16-March 17



FANTASTIC!!!!!! It works beautifully!!!!
I can’t thank you enough Dennis – I REALLY appreciate it.

I will study this in detail to make sure I totally understand it.
How do I give you public KUDOs in this forum?
Go to the top of the page
 
doctor9
post Jul 17 2017, 02:28 PM
Post#8


UtterAccess Editor
Posts: 17,593
Joined: 29-March 05
From: Wisconsin


PhilnTx,

You just did. smile.gif

I put comments into the new function so hopefully it will make sense. Basically, if you open the Poly_ID_2only query in SQL view, you see this:

SELECT Polygons.x_nodes, Polygons.y_nodes, Polygons.Poly_ID
FROM Polygons
WHERE (((Polygons.Poly_ID)=2));

It's pretty obvious that you just need to swap out the "2" for any other Poly number to see the other records. The easiest way to get that number to change was either to set up a loop inside the existing function or create a separate function and create the loop there, and call the original function inside the loop.

Pretty cool function by the way.

Glad to help,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th September 2017 - 09:45 AM