Point In Polygon Question

[DBS1]

I am trying to design a query in Access that captures points that are within a polygon. I have two tables, one with several polygons, and one that includes points. The polygon table (tblPoly) has multiple polygons, but I use a query (qryPoly) to select a specific polygon, by filtering the area, to evaluate. The trips table show stops at different locations, and there is a query (qryTrips) that I'm using as the data source for the points. The task I’m trying to accomplish with the query that I’m building is to show all of the points (which are coordinates at which stops occurred) within the polygon. I have searched this topic but haven’t found a solution, although there were a couple on accessforums that I tried to adapt but was unable to get to work. Also, if possible, I’d like to show trips that went through the polygon but no stops were made (i.e. trip 2), and it would be helpful to know any stops prior to the polygon (those two requirements are secondary to identifying the stops within the polygon).

Here is what my data looks like.

qryPoly
ID Area Seq Lat Lon Vertices
1 1 1 33.43 -97.39 14
2 1 2 33.40 -96.33 14
3 1 3 32.85 -96.30 14
4 1 4 32.83 -96.09 14
5 1 5 32.37 -96.08 14
6 1 6 32.34 -96.41 14
7 1 7 32.06 -96.94 14
8 1 8 32.28 -97.10 14
9 1 9 32.17 -97.51 14
10 1 10 32.21 -97.62 14
11 1 11 32.55 -97.62 14
12 1 12 32.57 -97.55 14
13 1 13 32.99 -97.55 14
14 1 14 33.00 -97.41 14
15 1 15 33.43 -97.39 14

qryTrips
ID TripNumber StopDate StopLat StopLon
1 1 05/18/22 32.76 -97.78
2 1 05/18/22 32.73 -97.36
3 1 05/18/22 33.03 -97.28
4 1 05/18/22 33.64 -97.15
5 2 05/23/22 32.32 -96.21


6 2 05/23/22 32.91 -96.14
7 3 06/02/22 32.01 -97.13
8 3 06/02/22 32.64 -96.83
9 3 06/02/22 32.93 -97.09
10 4 06/12/22 32.10 -96.46
11 4 06/12/22 32.79 -96.80
12 4 06/12/22 33.20 -96.61
13 4 06/12/22 33.63 -96.62

Any help pointing me in the right direction would be greatly appreciated.

[June7]

This is attempting to replicate complex coding accomplished by sophisticated geomatics software like ESRI ArcGIS. I very much doubt SQL alone can handle this. Doesn't help that you want several different outputs from this analysis.

If you found some examples that you attempted to adapt, you should provide that info.

I do not envy you the challenge.

[davegri]

I agree with June7.
This is a task easily solved in Python. If you know NOTHIN of Python, you can probably learn enough in a week to code a solution, especially if you are a proficient coder in another language.
But then, you'd need a method to transfer the solution into Access. That might be harder than getting the solution in hand from Python.

[Edgar]

If anyone needs visual help of what OP wants, this is the graphic representation

[madpiet]

Not an answer to your question, but you could use a Polygon in SQL Server and then determine if a set of points is within the Polygon. But in Access? Ouch.

[davegri]

Seeing Edgar's image in post #4 makes visualizing the solution algorithm easy (coding it is another thing!).
Imagine a given point (PO) known to be outside the polygon.
Draw a line from PO to any point in question (PQ).
If line PO-PQ does not cross a polygon side, PQ is outside the polygon.
If line PO-PQ crosses the polygon sides an ODD number of times, PQ is inside the polygon.
If line PO-PQ crosses the polygon sides an EVEN number of times, PQ is outside the polygon.

[DBS1]

Thanks everyone for your responses. Here are some of the resources I've found with a Google search and tried to adapt. My VBA abilities are very basic and what I'm trying to do is beyond what I'm able to figure out on my own. At this point, I'd settle for just identifying the coordinates inside of the polygon. I appreciate the time everyone takes to read and comment.

1. https://www.accessforums.net/showthr...hlight=polygon - this kindof works, and uses a query to determine min and max of lat and min and max of lon, and would suit my needs if the polygons were square or rectangle.

2. https://www.accessforums.net/showthr...hlight=polygon - This is close to what I'm trying to accomplish. I tried to adapt this to my data and couldn't get it to work correctly. I hard coded a coordinate in place of the one that the original author used, which should have been inside of the polygon, but the message box indicated it was outside of the polygon. Also, instead of a message box, I want all of the points that meet the criteria to be included in a query. I'm also not sure how to reference the coordinates from qryTrips in place of where the author had the coordinates hard coded.

3. https://www.excelfox.com/forum/showt...Polygon-Or-Not - this is based on Excel, and on page 4 of the post, there is a code posted by Phil_in_IN that I've copied and modified to fit my data. When I run it I get a message in the Immediate Window saying "15 records retrieved." and "1 Lines crossed". Looks like this is heading down the right path, but it apparently isn't running all of the trips through. I want to somehow transfer that message in the immediate window to capturing that stop in my query, but not sure how to do it. UPDATE - I noticed that the code referred to above is set as Option Explicit, and my code below is Option Compare Database. When I change to Option Explicit in my code I get an error saying variable not defined (Xcoord). I'm sure that is because it is not referenced, but I'm not exactly sure how to reference it. END UPDATE Code I have is as follows...

<Code>
Option Compare Database


Sub PIP() '(Xcoord As Double, Ycoord As Double)
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, transposeArray As Variant
Dim dbs As DAO.Database
Dim Polyrst As DAO.Recordset


Set dbs = CurrentDb
Set Polyrst = dbs.OpenRecordset("SELECT Lat, Lon FROM qryPoly", dbOpenSnapshot)
With Polyrst
.MoveLast
.MoveFirst
Poly = .GetRows(.RecordCount)
End With
'GetRows() is weird in that it returns rows & columns horizontally,
' the code below "transposes" the data to read down instead of across
Xupper = UBound(Poly, 2)
Yupper = UBound(Poly, 1)
ReDim transposeArray(Xupper, Yupper)
For Xx = 0 To Xupper
For Yy = 0 To Yupper
transposeArray(Xx, Yy) = Poly(Yy, Xx)
Next Yy
Next Xx
Poly = transposeArray
'-----------------------------------------------------------
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 Sub
</Code>

[June7]

Please post lengthy code between CODE tags to retain indentation and readability.

Please clarify why polygon point 15 is a duplicate of point 1. If there should be 14 vertices why are there 15 coordinate pairs? Okay, answered that by reading the 3rd thread. Why have you commented out variable declarations in Sub declaration? And why make it a Sub instead of Function?

Sub PIP() '(Xcoord As Double, Ycoord As Double)

[DBS1]

June7 - thanks for the advice on the Code tags. I just edited and added them. 15 is a duplicate to close the polygon, but there are only 14 points, thus the reason for 14 vertices. Should it be 15 vertices?

[June7]

Code tags are not correct. Use # icon on edit toolbar or change <> to [].

I can make second shorter function of 3rd referenced link to execute with following code:

Sub PIP()
Dim rsP As DAO.Recordset
Dim rsT As DAO.Recordset
Dim aryP As Variant
Dim strA As String, strM As String
Set rsP = CurrentDb.OpenRecordset("SELECT Lon, Lat, Area FROM tblPolys WHERE Area=1 ORDER BY Seq")
Set rsT = CurrentDb.OpenRecordset("SELECT * FROM tblTrips")
rsP.MoveLast
rsP.MoveFirst
strA = rsP!Area
aryP = rsP.GetRows(rsP.recordCount)
Do While Not rsT.EOF
    strM = strM & strA & ": " & rsT!StopID & " : " & PtInPoly(rsT!StopLon, rsT!StopLat, aryP) & vbCrLf
    rsT.MoveNext
Loop
MsgBox strM
End Sub

I changed ID field names to VertID and StopID.

Unfortunately, every point returns as outside polygon.

I also tested 2nd referenced code and again, outside polygon.

Am I passing arguments in correct order? Do values need to be positive? Still testing.

[DBS1]

June7 - thank you so much for your efforts. The shorter code looks much better and I see you fixed one of my problems by adding "Set rsT...." I knew that table had to be referenced but wasn't sure how to do it. I'm not sure of the order of the arguments and not sure why the points are showing outside of the polygon. Hopefully it's something simple.

[June7]

Functions in workbook do work. Even with negative coordinates.

Workbook is passing polygon as a range but then sets Poly variant to that range and loops this array. My Access adaptation is passing an array using GetRows, however, function is not looping through this array. So something is wrong about the array. I need to figure out structure of Excel array. I think it is 20 fields and 2 rows - a pivot of range. Try this version (again, calling shorter procedure from link reference 3):

Sub PIP()Dim rsP As DAO.Recordset
Dim rsT As DAO.Recordset
Dim aryP(15, 2) As Double
Dim strA As String, strM As String, x As Integer
Set rsP = CurrentDb.OpenRecordset("SELECT Lon, Lat, Area FROM tblPolys WHERE Area=1 ORDER BY Seq")
Set rsT = CurrentDb.OpenRecordset("SELECT * FROM tblTrips")
strA = rsP!Area
Do While Not rsP.EOF
    aryP(x, 0) = rsP!Lon
    aryP(x, 1) = rsP!Lat
    x = x + 1
    rsP.MoveNext
Loop
Do While Not rsT.EOF
    strM = strM & strA & " : " & rsT!StopID & " : " & PtInPoly(rsT!StopLon, rsT!StopLat, aryP) & vbCrLf
    rsT.MoveNext
Loop
MsgBox strM
End Sub

Needs more work. For instance, array could be more dynamic by using ReDim statement so that polygon isn't limited in number of vertices. Selection of polygon could be variable or might want to loop all polygons to determine if each trip point falls into any area(s).

If you want to call procedure from query, that needs other modifications. Either a function to build vertices array with Area number passed to it or just pass an Area number to PtInPoly modified to open recordset and build array there. Or don't pass a specific area and loop all polygons and function returns first (or all) Area that point is within.

Code is not as lengthy as I feared but I never would have figured out that algorithm on my own. Addressing your 2 other needs is definitely outside my skill set.

[DBS1]

June7 - thanks for the more simplified code (and congrats on 52,500 posts). I changed the name to PIP2 since I already had a procedure named PIP, which is one of my many attempts to get something to work. As you can see in the screenshot below, I got an error and moved the dim rsP line to the next line. At that point, I get an error saying "Compile Error: Sub or Function not defined". I'm probably going to try a few other things to see if I can get something to work, and if not, I think I'll put it aside for a while and come back to it later. I need to do a little reading on VBA so I can better understand the procedures. I have another database I'm working on that I'm trying to figure out a few things. Once I get this figured out I'll post on here.

[June7]

My apologies. For some reason the forum always combines procedure declaration and first line of code when pasting and I forgot to fix (if I could still edit that post, I would).

New error is probably because missing PtInPoly() function. That code comes from item 3 referenced site. But I will show it here:

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, 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
  PtInPoly = CBool(NumSidesCrossed Mod 2)
End Function

Every module should have Option Explicit in header. Can set VBA properties to do this automatically for new modules. Will have to manually add to existing.

[DBS1]

How are you calling procedure?

I was running it using the Run button on the toolbar.