# Calculate distances between records in two tables

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.

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.

• Thanks for your assistance, I developed the solution in PostGIS however I will try this. Jun 8 '16 at 8:57

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";
``````