[Solved] Match closest East/North to Lat/long

[Fitch]

Hi all.

I am wondering if I can get a closest match from a mean Easting/Northing to a known Latitude and Longitude...

Here's the thing..
I trawled your site and found how to get the closest match in a column to a test cell.

My problem is, I need to get the closest match to 2 cells (East/North) to find the corresponding Lat/Long.

My effort is attached and can happily find the centre of my "map" as Falkirk. But that was lucky, as I'm only using the Eastings to find the match, It could actually be anywhere down a vertical line from Strathy Point to Exmouth. So I need the Northings as well.
Thing is, the file is too big, so I've split it in two, by removing rows 1500 to 3200.

cm1.ods

(106.18 KiB) Downloaded 118 times

Now it fits, but I'm probably not allowed to upload the bottom part, or am I?

cm2.ods

(93.28 KiB) Downloaded 135 times

It may look empty, but just go down to column 1501 and marry the two sets together.

My effort so far is in Sheet 2 of each part.

I get a nasty feeling you're going to talk to me about arrays..
Never did get the hang of them....

Thanks.
Alistair.

[Villeroy]

SQRT((x1-x2)^2 + (y1-y2)^2) returns the distance between 2 points with x and y coordinates.

[Fitch]

Thank you.

You must get really fed up with us not knowing maths.
I must admit it's been 40 years since I did Pythagoras, but at least I recognised it as soon as I saw it, so so my mate Al Zheimer hasn't found me yet...

Cheers!

[MrProgrammer]

I can offer two ways to approach the problem:

• Your data shows a strong linear correlation between Northing and Latitude and also between Easting and Longitude. So you can calculate Latitude and Longitude independently. Latitude is =FORECAST(A21;$H$3:$H$3115;$F$3:$F$3115); Longitude is =FORECAST(A18;$I$3:$I$3115;$E$3:$E$3115).
• ignoring the strong correlation, you want to do Multivariate interpolation. There are many ways to do that, but the only simple one is Nearest-neighbor interpolation (discussed in more detail in the reference). This is Villeroy's suggestion. Your data does not allow the Bilinear interpolation method.

For this data and Mean Northing 716553 Mean Easting 280827, calculating Latitude and Longitude independently gives (56.318,-3.833). Nearest-neighbor gives (56.329,-3.830). You can decide which approach you think will work best for your situation. Those locations are about 1.34 kilometers apart.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Fitch]

Wow!

Thank you both.
It looks like the latter post wins as it removes the need for another (hypotenuse) column.
The "approximation" to Lat/Long given is excellent.
And I've learned about FORECAST.

Problem solved.

Thank you again.

Alistair.

[Lupp]

(@Fitch, by a curious user:)

Would you mind to explain the terms Northing and Easting for the current case.

As far as I could get, this kind of coordinates allows for a selected (arbitrary) origin while Latitude / Longitude have the fix origin where the zero meridian and the equator intersect. If not the same origin is assured for the N/E system a linear approach is poblematic. However, linear interpolation based on lookups may deliver satisfying results if the grid's resolution is high enough (geographical range small enough). A linear approach concerning coordinate transformation would require a shearing, I suppose, even for small ranges.

As I interpret your data, your N/E system does not use one of the UTM zone's origins, but a point near Ashfield, Stirlingshire, UK. How did you get your tables for this second-to-many origin?

A few remarks concerning your 'Sheet2' of the first attachment:
You should consider to use absolute addressing for the first rows of your ranges at least.
The ranges used in columns M, N start with row 2 despite the fact that the data start with row 3. This will not cause errors in the specific case as long as related formulae use the respective skew ranges because row 2 does not contain numbers. However it may get problematic if maintenance/enhancements of the sheets make changes. (You may have set the option 'Expand references if...' and inserted a new row 2 after the creation of the formulae.)

Greeting Falkirk from München.

[Fitch]

Greetings München from Falkirk (well, slightly South, in West Calder).

The API I use is Geopostcode. to get the location of the full postcode (where the member lives), for instance: http://www.geopostcode.org.uk/api/EH55%208DW

The Northing & Easting is set as OSGB36
Straight out of Wikipedia: https://en.wikipedia.org/wiki/Ordnance_ ... ional_Grid

The British maps adopt a Transverse Mercator projection with an origin (the "true" origin) at 49° N, 2° W (an offshore point in the English Channel which lies between the island of Jersey and the French port of St. Malo)

So, Ashfield has me worried, although it is quite nice there, and not as wet.

The original data, to do the approximation with the outcodes, is from a csv file downloadable at:https://www.doogal.co.uk/PostcodeDistrictsCSV.ashx
There are others including https://github.com/Gibbs/uk-postcodes and https://www.freemaptools.com/download/o ... tcodes.csv

I will change to absolute addressing as suggested.
The skew in the data is probably how I pasted it across from the extremely large spreadsheet to one I could post. But I will check.

Thank you.
All the best from sunny Scotland.

[Lupp]

Well, 16°C and just a bit of cirrostratus(?), that's nice. I would love to be there.

However, I never used Northings and Eastings, and I think I should leave it at that. Survey feet also aren't my favorite dish.

Obviously I misunderstood some of your calculations. Going to columns E and F and looking for the minimal absolute values of N/E made me drown in the middle of the "Celtic Sea". OK. There must be some sense in it. The idea may be to use the point lying in the extreme south-west for a coordinate domain of the UK, and thus to avoid negative coordinates. On the other hand Jersey (well, not exactly UK) is listed with negative Northing in your tables.

Just one thing probably of relevance for you. Your Easting/Northing coulmns contain some entries looking like numbers, but actually being texts:
E829:F831;E1329:F1329;E1332:F1332;E2665:F2665;E2917:F2917;E2969:F2969;E2978:F2978
Concerning this observation another unsolicited advice: Do not change the default horizontal alignment. Texts will then be displayed left-aligned while numbers get right-aligned. This may help to avoid mistakes by making them obvious. Of course you may already have imported the tables errors and all.
Though Calc stresses the (sometimes dangerous) automatic conversion from "numerical" text to numbers, some functions don't. Amoung them SUM(), AVERAGE(), MIN(), MAX(), COUNT(). Your formulae may neither include the mentioned entries in evaluations nor find them with MATCH() if searching for a number.

Regards

[Fitch]

Found them!
Cleared direct formatting and manually removed the single quote at the front (Find and Replace couldn't see them).

Thanks again.

Cumulus now, but very big bright thing up in the sky. Don't know what that is, never seen it before...

[Lupp]

... but very big bright thing up in the sky. Don't know what that is, never seen it before...

Might it be something they secretly developed on the Stanford University Network? If so, it's closely related to our bright software.