Calculate distances between records in two tables

Question

I need to identify the nearest 3 childminders (approx 500 records) to the nearest school (approx 120 records). Both the childminders and schools layers reside in PostGIS. Can anyone offer any assistance?

I do have FME at my disposal however this only returns the nearest childminder to each school using the NeighborFinder transformer.

Answer 1

For FME it's best to have FME2015 or newer, because it has a new option for "Number of Neighbors to Find":

Here I find the nearest three parks to a specific location. Notice I set Neighbors to Find = 3 and set a list name as ParkList. That list will contain the attributes of the three closest parks.

I saved the above workspace as a template for FME 2016 and you can find it here: https://www.dropbox.com/s/ncap80yaif3tv2j/NeighborsList.fmwt?dl=0

NB: I just found a bug in my workspace, in that the origin point is not where I thought it was! I gave it an x/y in the wrong coordinate system. The workspace still works, but it doesn't find the three parks I expected. Change the Creator coordinates to 491202.6913,5458495.6828 and all will be well!

The other possibility is to calculate distance along a road network instead of just a straight-line distance as above. For that you would need the road network (obviously) and would use the NetworkCostCalculator transformer.

Answer 2

Please see my solution below, I'm sure I could amalgamate this into a more elegant solution. Step 1 involved creating a view of the distance for every school to every childminder

CREATE OR REPLACE VIEW education.vw_childminder_distance AS 
SELECT all_schools."Dfes", all_schools."UPRN", all_schools."NAME", childminders.title, childminders.ofsted_urn, st_distance(all_schools.wkb_geometry, childminders.wkb_geometry)::integer AS distance
FROM education.all_schools, education.childminders
ORDER BY all_schools."NAME", st_distance(all_schools.wkb_geometry, childminders.wkb_geometry)::integer;

Second step I partitioned the original view and created a new view of the nearest five chilminders to every school. Apologies for not presenting the SQL in a readable fashion.

CREATE OR REPLACE VIEW education.vw_nearest_childminders_to_school AS 
 SELECT sc."NAME" AS school_name, sc."UPRN" AS school_uprn, sc."Dfes" AS school_dfes, max(
        CASE
            WHEN sc.school_name = 1 THEN sc.title
            ELSE NULL::character varying
        END::text) AS childminder_1, max(
        CASE
            WHEN sc.school_name = 1 THEN sc.ofsted_urn
            ELSE NULL::character varying
        END::text) AS childminder_1_urn, max(
        CASE
            WHEN sc.school_name = 1 THEN sc.distance
            ELSE NULL::integer
        END) AS childminder_1_distance, max(
        CASE
            WHEN sc.school_name = 2 THEN sc.title
            ELSE NULL::character varying
        END::text) AS childminder_2, max(
        CASE
            WHEN sc.school_name = 2 THEN sc.ofsted_urn
            ELSE NULL::character varying
        END::text) AS childminder_2_urn, max(
        CASE
            WHEN sc.school_name = 2 THEN sc.distance
            ELSE NULL::integer
        END) AS childminder_2_distance, max(
        CASE
            WHEN sc.school_name = 3 THEN sc.title
            ELSE NULL::character varying
        END::text) AS childminder_3, max(
        CASE
            WHEN sc.school_name = 3 THEN sc.ofsted_urn
            ELSE NULL::character varying
        END::text) AS childminder_3_urn, max(
        CASE
            WHEN sc.school_name = 3 THEN sc.distance
            ELSE NULL::integer
        END) AS childminder_3_distance, max(
        CASE
            WHEN sc.school_name = 4 THEN sc.title
            ELSE NULL::character varying
        END::text) AS childminder_4, max(
        CASE
            WHEN sc.school_name = 4 THEN sc.ofsted_urn
            ELSE NULL::character varying
        END::text) AS childminder_4_urn, max(
        CASE
            WHEN sc.school_name = 4 THEN sc.distance
            ELSE NULL::integer
        END) AS childminder_4_distance, max(
        CASE
            WHEN sc.school_name = 5 THEN sc.title
            ELSE NULL::character varying
        END::text) AS childminder_5, max(
        CASE
            WHEN sc.school_name = 5 THEN sc.ofsted_urn
            ELSE NULL::character varying
        END::text) AS childminder_5_urn, max(
        CASE
            WHEN sc.school_name = 5 THEN sc.distance
            ELSE NULL::integer
        END) AS childminder_5_distance
   FROM ( SELECT sc."Dfes", sc."UPRN", sc."NAME", sc.title, sc.ofsted_urn, sc.distance, row_number() OVER (PARTITION BY sc."NAME" ORDER BY sc.distance) AS school_name
           FROM education.vw_childminder_distance sc) sc
  GROUP BY sc."NAME", sc."UPRN", sc."Dfes";