Referring to a range in access as in excel

[PHILnTEX]

Hi All,
newbie question. I have two tables in Access 2016, one of points (x/y coordinates) and another of polygons (consisting of an Poly_ID and a series of x/y nodes defining each poly perimeter). I need to discover what points are in what polygon. The actual database has over 250K points and 4K polygons.

Below is a vb function (written by Rick Rothstein in another forum) for excel that works brilliantly for one point in one polygon.

I’d like to use this function, or something like it, in Access but can’t get my head around how to refer to a range in access.
Can anyone point me in the right direction?

Pic and sample db attached.

Public Function PtInPoly(Xcoord As Double, Ycoord As Double, Polygon As Variant) As Variant
  Dim x As Long, NumSidesCrossed As Long, m As Double, b As Double, Poly As Variant
  Poly = Polygon
  For x = LBound(Poly) To UBound(Poly) - 1
    If Poly(x, 1) > Xcoord Xor Poly(x + 1, 1) > Xcoord Then
      m = (Poly(x + 1, 2) - Poly(x, 2)) / (Poly(x + 1, 1) - Poly(x, 1))
      b = (Poly(x, 2) * Poly(x + 1, 1) - Poly(x, 1) * Poly(x + 1, 2)) / (Poly(x + 1, 1) - Poly(x, 1))
      If m * Xcoord + b > Ycoord Then NumSidesCrossed = NumSidesCrossed + 1
    End If
  Next
  PtInPoly = CBool(NumSidesCrossed Mod 2)
End Function

 

[Ranman256]

Access is stored in records,so you'd have to load the records into an array POLY().
Then you can reference the coords.

 

[PHILnTEX]

Ranman256, thanks so much for your reply.

I took your suggestion and did get it to work on a single polygon with multiple points. But I’m now stuck on how to get it to work with multiple polygons.
The code below reads from a query that list the nodes for a single polygon. How would I go about looping through the polygons?

I’d appreciate any pointer you may have on how I could proceed?

Thanks so much in advance

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
'------------------------ swap array order -----------------
    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