analysis of duplicate entities for A4D,LBO, Tree, TPZ

[Swati-Dash]

@sianteesdale I have checked the duplicate entities report for these datasets, they are different from CA because we have duplicate data from the same organisation (unlike LPA-HE in CA). Its hard to take action based on those reports. For example if article-4-areas are overlapping but the entities are coming from same endpoint, we should not deduplicate it.

Duplicate entities on the platform could have been added because

1. LPAs have gave us multiple endpoints with different ref values which we might have added without checking
2. LPAs kept the endpoint persistent but kept changing there refs which have been added in our unknown entity assignment process

So in this ticket we need to check if the entity-count on the latest resource of the active endpoint is matching with the entity-count on the platform for any particular organisation. If they are matching we will ignore geometry overlap

• 1. All fields from this table https://datasette.planning.data.gov.uk/performance/reporting_historic_endpoints
• 2. Bring the entity counts from dataset_resource table (for example this table for article-4-direction-area https://datasette.planning.data.gov.uk/article-4-direction-area/dataset_resource by joining the resource)
• 3. Bring count of entitles for each organisation from platform data (https://files.planning.data.gov.uk/dataset/article-4-direction-area.csv), join with organisation table to get org-name and org-code

[Swati-Dash]

@sianteesdale @Ben-Hodgkiss I am going to spend time on this and add more details

[sianteesdale]

I followed the steps set out above to compare the entity counts between the platform data and the dataset_resource table. From that, I had 53 matches (out of 283).

The biggest discrepancies were seen in the LBO and Tree datasets.

Upon further investigation (and with thanks to Ben), it seems all the LBO resources have 0 entity-counts within their dataset_resource. And Tree has entity counts for some (not all!) LPAs. Currently talking to Owen to investigate if this is processing issue.

[sianteesdale]

With big thanks to @eveleighoj for helping me investigate the problems, we have found that:

• dataset_resource may not always record the entity_count
• when this happens, the entities should be counted from the transformed resource (e.g. from a URL like f"[http://files.planning.data.gov.uk/{collection}-collection/transformed/{dataset}/{resource}.csv"

I did a sample of 10 randomly selected resources to investigate (see below).
"No entity recorded" means the dataset_resource returned 0/null
"Missing" means the resource was not found in dataset_resource at all. E.g. here with a A4D example

So I think, going forward, using the transformed resource data where we're missing dataset_resource entity_count may be a good idea? Or we could just use transformed resource data entirely, and ignore dataset_resource?

Resource	Dataset	LPA	dataset_resource entity_count	platform entity_count	transformed resource entity_count
47b2c02399036050fe507e4b90ee532f25293b19f7cec9fb308336e625f7109b	LBO	Gloucester City	Missing	486	486
6eb49d0579ac5162126f793800ead81a74761b880e4066fc623a01ec72678952	TPZ	Leicester City	Missing	146	146
8b88687d996f7b9bbcfae4f4ba7c515f55eef34212a0064eb922891d3c99a97e	A4D	Tandridge	Missing	50	50
d632df6f6b49074036d830502a8f22df6b3e75593d9a8cd783d4ccf33d6dd67a	LBO	Epsom and Ewell	Missing	272	272
dd14d735a5cc249c6a3c4751e134c2040a83e58aa0c29475faed0825b54c2927	LBO	Barking and Dagenham	Missing	48	48
3081bb2dec818694e2a6eaccd39ad37abc7dca39131dd8b085d413931ee21109	Tree	Gloucester City	No entity recorded	1358	1358
ae9aa5602c72b8a76da0eac063f7190e9211fd198244e244c89d65b41306a192	Tree	Tower Hamlets	No entity recorded	1769	1677
bb6a8eb7e6f4720030aec3bd5fdff525700d8ae045cd7b32fd08f4c8614efc5e	LBO	Rossendale	No entity recorded	278	278
85e3264ae354c8701d3147d13c54a4bc6b5edbefebfe6add3cf010243303ef95	A4D	West Oxfordshire	84	84	n/a
5837666a86b654bdf27164f2fa5bdc6c74552efccda5c4ff982356df89fa1787	TPZ	Waltham Forest	217	217	n/a