Identifying Repeated Vegetation Enquiries Over Time
Context
I’m working with a dataset of over 80,000 vegetation-related enquiries submitted by the public over a 10-year period. Each enquiry is represented as a point with the following attributes:
- Easting
- Northing
- Logged Date (with time)
- Enquiry Subject Name
Objective
My goal is to identify repeated enquiries occurring at least twice over the time period with at least a year between enquiries. I want to find all repeat enquires to get a sense of the overall picture and then filter/reduce the overall number down to just a single enquiry to get a sense of how many I'm dealing with.
This will help reveal patterns of recurring issues (e.g., persistent vegetation problems) and allow me to filter down to unique cases for further analysis.
Challenges Encountered
- Duplicate Submissions
Some locations have multiple identical points due to users clicking repeatedly when submitting an enquiry. In some cases, this results in 6+ identical points.
Solution:
I used Excel to remove duplicates based on a combination of:
- Enquiry Subject Name
- Logged Date/Time
- Easting
- Northing
- Inconsistent Terminology & Location Accuracy
Because the data is submitted by the public:
- Terminology varies e.g., “Tree Overgrown/Untidy” vs “Vegetation Overhanging” may refer to the same issue.
- Location accuracy varies points may not align precisely with assets, leading to spatial scatter. See screenshot below.
Methods Used – Image below.
Method 1: Buffer-Based Spatial Join (Blue Points)
- Removed records with null coordinates, subject name, or date.
- Clipped points to the area of interest.
- Created a 10m buffer around each point.
- Performed a spatial join between the buffer layer and the original point layer (1-to-many relationship).
- Dissolved results based on:
- Enquiry Subject Name (from join)
- Easting
- Northing
This method helped group nearby points with similar subjects but missed some cases.
Method 2: Python-Based Temporal Filter (Red Points)
- Same initial cleaning and clipping steps.
- Performed a spatial join.
- Used a Python script to identify repeated enquiries:
- Matching Easting, Northing, and Subject Name
- Logged more than 1 year apart
This method captured some cases missed by Method 1 but also failed to group certain most overlapping points.
Visual Comparison
As shown in the image below, red points (Method 2) and blue points (Method 1) don’t always overlap. Each method captures different aspects of the problem, and neither is fully comprehensive.
Next Steps
Looking for recommendations for improvements and next steps, I could append the 2 datasets and remove the intersecting points but want to look at mistakes I have made or alternative approaches which could highlight repeated enquires I am missing.
