Forum Discussion

masplin's avatar
masplin
Impactful Individual
8 years ago

Mapping postcode to Easting/Northing set

I have a set of temprature data that is displayed in a regular grid for example a point is Easting 102500 Northing 587500. I want to work out what is the nearest postcode district to this point so i can report by county or region

 

This table Daily_Time_Series looks like this

 

Date Location Easting Northing Min Temp

01/01/00102500-5875001025005875002.09
02/01/00102500-5875001025005875003.91
03/01/00102500-5875001025005875003.74
04/01/00102500-5875001025005875001.71

 

The location data I have is by postocde district and contains the easting/northing of roughly the centre of the area. For example Postcode_District table is 

 

Postcode Easting Northing

AB1391839804005
AB10392991804882
AB12393424801375
AB11394446805136

 

Essentially I need to calcuate for each row in the first table the following formula (roughly a sq error term)

 

Error:=(  'Daily Time Series data'[Easting]- Postcode_District[Easting])*(  'Daily Time Series data'[Northing]- Postcode_District[Northing])

 

and then return the value of postcode where the error is the smallest. 

 

I understnad the principal of the calcuation but not the syntax. for each row of Daily Time Series i calcaute a table of these error terms by iterating the Postcode_District Table, and then find the min.

 

Any help greatly appreicated as tried 4 or 5 different ideas but none work

 

Mike 

Need Help

3 Replies

    • masplin's avatar
      masplin
      Impactful Individual
      8 years ago

      Problem is its TOPN of a function across a whole table. I've tried using SUMMARIZE to create a table and apply my formula but doesn't work. 

      • masplin's avatar
        masplin
        Impactful Individual
        8 years ago

        I've made a small amount of progress. This calcuated column correctly calcuates the smallest vlaue of my error term for each location in my original data set

         

        =CALCULATE(
                        MINX(
                                  SUMMARIZE(
                                                        Postcode_District,
                                                        Postcode_District[Postcode],
                                                        Postcode_District[Easting],
                                                        Postcode_District[Northing]
                                                        ),
                                   (('Daily Time Series data'[Average of Easting]-Postcode_District[Easting])^2)+(('Daily Time Series data'[Average of Northing]-Postcode_District[Northing])^2)
                                   )
                            )

         Now I need to identify the row in my Postcode_District table that contained the easting/nohing combination that matches this number  and retrun the Postcode_District[Postocde] value?

         

        I tried this (without really understanding the syntax) and got a circular error.

         

        =CALCULATE(
                        VALUES(Postcode_District[Postcode]),
                                  TOPN(
                                            1,
                                  SUMMARIZE(
                                                        Postcode_District,
                                                        Postcode_District[Postcode],
                                                        Postcode_District[Easting],
                                                        Postcode_District[Northing]
                                                        ),
                                            (('Daily Time Series data'[Average of Easting]-Postcode_District[Easting])^2)+(('Daily Time Series data'[Average of Northing]-Postcode_District[Northing])^2),
                                            ASC
                                            )
                                       
                            )