3

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.

2
3

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

enter image description here

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.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.