Populate the potential duplicates queue with more locations

[simonw]

It's empty right now. https://discord.com/channels/799147121357881364/813861006718926848/844627119363719170

Use same pattern as #549.

[simonw]

I'm going to try for "locations with similar names within 500m of each other."

[simonw]

Exact names performs better - this query gives great results:

SELECT a.public_id, a.name, b.public_id, b.name, a.full_address, b.full_address, ST_Distance(a.point, b.point)
FROM location a, location b 
WHERE a.name = b.name
AND a.id != b.id
and a.soft_deleted = false and b.soft_deleted = false
and ST_Distance(a.point, b.point) < 500
limit 10

https://vial.calltheshots.us/dashboard/?sql=SELECT+a.public_id%2C+a.name%2C+b.public_id%2C+b.name%2C+a.full_address%2C+b.full_address%2C+ST_Distance%28a.point%2C+b.point%29%0D%0AFROM+location+a%2C+location+b+%0D%0AWHERE+a.name+%3D+b.name%0D%0AAND+a.id+%21%3D+b.id%0D%0Aand+a.soft_deleted+%3D+false+and+b.soft_deleted+%3D+false%0D%0Aand+ST_Distance%28a.point%2C+b.point%29+%3C+500%0D%0Alimit+10%3As3xSh5IkDeAmgOAlZNDZoYeaHjrHrCUq0AWwKnCmMgM

[simonw]

Running a count(*) against that hits the statement time limit.

[simonw]

I ran the query against a connection without the time limit - it returned 698 rows in 15s. I'm going to use that to construct the potential dupes.

[simonw]

Here's the magic query:

with potential_duplicates as (
  select
    a.public_id as public_id_1,
    b.public_id as public_id_2,
    ST_Distance(a.point, b.point) as distance_m
  from location a, location b 
    where a.name = b.name
    and a.id != b.id
    and a.soft_deleted = false and b.soft_deleted = false
    and ST_Distance(a.point, b.point) < 500
)
select json_agg(json_build_object(
  'task_type', 'Potential duplicate',
  'location', public_id_1,
  'other_location', public_id_2,
  'details', json_build_object(
    'same_name', true,
    'distance_m', distance_m
  )
)) from potential_duplicates

Took 14s to run and gave me back this: https://gist.github.com/simonw/967e0bb8dcd939323231cdf082b1834e

[simonw]

Hang on, I think each pair is showing up twice - need to de-dupe on location / other_location.

[simonw]

I can filter those out by adding and a.public_id < b.public_id:

with potential_duplicates as (
  select
    a.public_id as public_id_1,
    b.public_id as public_id_2,
    ST_Distance(a.point, b.point) as distance_m
  from location a, location b 
    where a.name = b.name
    and a.id != b.id
    and a.soft_deleted = false and b.soft_deleted = false
    and ST_Distance(a.point, b.point) < 500
    and a.public_id < b.public_id
)
select json_agg(json_build_object(
  'task_type', 'Potential duplicate',
  'location', public_id_1,
  'other_location', public_id_2,
  'details', json_build_object(
    'same_name', true,
    'distance_m', distance_m
  )
)) from potential_duplicates

That returns 349 which is half of 698.

[simonw]

I pasted the JSON result from that into https://vial.calltheshots.us/api/importTasks/debug

[simonw]

I tried running this with levenshtein instead of exact name matching but even with limit 10 it took more than 300 seconds and didn't return any results:

with potential_duplicates as (
  select
    a.public_id as public_id_1,
    b.public_id as public_id_2,
    ST_Distance(a.point, b.point) as distance_m
  from location a, location b 
    where a.name != b.name
    and levenshtein(a.name, b.name) < 5
    and a.id != b.id
    and a.soft_deleted = false and b.soft_deleted = false
    and ST_Distance(a.point, b.point) < 500
    and a.public_id < b.public_id
  limit 10
)
select json_agg(json_build_object(
  'task_type', 'Potential duplicate',
  'location', public_id_1,
  'other_location', public_id_2,
  'details', json_build_object(
    'same_name', true,
    'distance_m', distance_m
  )
)) from potential_duplicates

[simonw]

Rather than stress the database out I'm going to try running these offline against the export from https://api.vaccinatethestates.com/v0/locations.geojson

[simonw]

Here's a dashboard showing the current queue: https://vial.calltheshots.us/dashboard/potential-duplicate-queue/

[simonw]

Queue is empty again. I'm going to re-populate it.

[simonw]

I ran the query from #592 (comment) again - it took 8.9s and return 156 results, which I have pasted into the API. Those now show up in https://vial.calltheshots.us/dashboard/potential-duplicate-queue/