Devise a way to identify multiple sites on the same JUROL that have the same civic number but different street names

[mraross]

@mraross commented on Wed Sep 14 2016

One way is to find accessPoints that are associated with more than one address. You need to exclude sub-units. For example, 1004, 1008,1104,1108, 1100 Pavillion-Clinton Rd, Clinton all share the same accessPoint

---

@gleeming commented on Wed Sep 14 2016

To even start, this will require us to resurrect a way to load the current data formats used in the now database-free geocoder environment into a database.

---

@mraross commented on Wed Sep 14 2016

Ok, will investigate ways of scripting this internally without a db.

---

@cmhodgson commented on Wed Sep 14 2016

Nitpicky but important: they do not share the same "accessPoint". Their accessPoints are at the same location. I guess its fair to say they share the same "access point" (note the space and lack of camelCase).

I make these accessPoints in the rangeGen, we don't really need to find them after the fact. They end up at the same location because they are "off the end" of the Street's centerline linestring. Certainly, MOST accessPoints that are offset from the end-point of the Street's lineSegment probably do not belong there.

---

@gleeming commented on Wed Sep 14 2016

I suspect many of them are actually legitimate where a street ends in a cul-de-sac.

I have a semi-working database environment in postgis that can ultimately be used to load the new data formats; it still needs more tweaking. About 10% of all APs share the same coordinate with at least one other AP (also included restriction to have same locality ID and street segment ID for a fast response). Note that subsites currently don't have their own APs because custodians don't supply it, so the counts are related to sites only. About 3500 records comprise the set of APs where 50 or more of them have a shared location.

SELECT access_point_id, site_id, ap.locality_id, ap.street_segment_id, civic_number, count, ap.geometry FROM
(SELECT * FROM
(SELECT ap.geometry, locality_id, street_segment_id, count(*) AS count FROM bgeo.bgeo_access_points ap
LEFT JOIN bgeo.bgeo_sites s ON ap.site_id = s.site_id
WHERE s.parent_site_id IS NULL
GROUP BY locality_id, street_segment_id, ap.geometry) AS foo
WHERE count > 1) AS foo2
LEFT JOIN bgeo.bgeo_access_points ap ON ap.geometry = foo2.geometry
AND ap.locality_id = foo2.locality_id AND ap.street_segment_id = foo2.street_segment_id
ORDER BY ap.geometry, ap.locality_id, ap.street_segment_id;

---

@mraross commented on Wed Sep 14 2016

Perhaps restricting to APs that share the same point with three or more APs would narrow it down a bit.

---

@gleeming commented on Wed Sep 14 2016

Cuts the number of cases in half, but there are still way too many to be useful for review. I could try cross-referencing to and ignoring dead end street cases, but this may still not be good enough.

The worst offender is 143 APs at the same location around 1822 Purcell Way, North Vancouver. They are mid-block because all the custodian records for this block complex have the same site location. It isn't a dead end case, they just all extrapolate to the same mid-block location from their common starting geometry.

---

@gleeming commented on Tue Sep 27 2016

Here's a revised query that ignores cases on dead-end segments. It cuts the number of cases in half, but with over 60k results is still too excessive for a broad review. As previously noted, looking for the largest clusters may provide the best starting point.

SELECT access_point_id, site_id, ap.locality_id, ap.street_segment_id, civic_number, count, ap.geometry FROM
(SELECT ap.geometry, locality_id, street_segment_id, count(*) AS count FROM bgeo_access_points ap
LEFT JOIN bgeo_sites s ON ap.site_id = s.site_id
WHERE s.parent_site_id IS NULL
GROUP BY locality_id, street_segment_id, ap.geometry) AS foo
LEFT JOIN bgeo_access_points ap ON ap.geometry = foo.geometry
AND ap.locality_id = foo.locality_id AND ap.street_segment_id = foo.street_segment_id
LEFT JOIN bgeo_street_segments ss ON ss.street_segment_id = ap.street_segment_id
LEFT JOIN bgeo_street_intersections si1 ON si1.street_intersection_id = ss.start_intersection_id
LEFT JOIN bgeo_street_intersections si2 ON si2.street_intersection_id = ss.end_intersection_id
WHERE count > 1 AND si1.degree > 1 AND si2.degree > 1
ORDER BY ap.geometry, ap.locality_id, ap.street_segment_id;