Author Topic: Spreadsheet and OS mapping genius solution sought  (Read 2960 times)

slope

  • Inclined to distraction
    • Current pedalable joys
Spreadsheet and OS mapping genius solution sought
« on: 20 November, 2018, 03:39:38 pm »
I've got a considerable spreadsheet with OS grid references for their benchmarks.

I'd like to refine the data down to a more personal centric view.

Is it possible? to formulate/thin down this 'stuff' to just give me all the grid references within a 10km (say) radius/or10km x 10km square of my postcode (the centre of which is SH 6378 5143)?

Here's a screen shot of the relevant bits on the spreadsheet



and as a bonus here's a pic (zero editing straight from an iPhone), last week whilst out cycling for groceries in these parts





FifeingEejit

  • Not Small
Re: Spreadsheet and OS mapping genius solution sought
« Reply #1 on: 20 November, 2018, 04:04:06 pm »
I've got a considerable spreadsheet with OS grid references for their benchmarks.

I'd like to refine the data down to a more personal centric view.

Is it possible? to formulate/thin down this 'stuff' to just give me all the grid references within a 10km (say) radius/or10km x 10km square of my postcode (the centre of which is SH 6378 5143)?

10km doesn't take you out of SH which should make it easier

So i think that makes the pseudofilter for your 10x10 box
NG Letters = SH
Easting between 5378 and 7378
AND Northing between 4143 and 6143


slope

  • Inclined to distraction
    • Current pedalable joys
Re: Spreadsheet and OS mapping genius solution sought
« Reply #2 on: 20 November, 2018, 05:00:26 pm »
Quote from: FifeingEejit[/quote
10km doesn't take you out of SH which should make it easier

So i think that makes the pseudofilter for your 10x10 box
NG Letters = SH
Easting between 5378 and 7378
AND Northing between 4143 and 6143

Much appreciate the seemingly positive/achievable reply  :)

Regrettably, I'm thick and too old/brain elsewhere :-[

What is a "pseudofilter" and how in child like steps, would one apply such a thing?

fuaran

  • rothair gasta
Re: Spreadsheet and OS mapping genius solution sought
« Reply #3 on: 20 November, 2018, 08:44:55 pm »
Or convert it to GPX, then display it on a map? Would make it a lot easier to see what is where, and find the nearest points.

What's the source of the data? Can you download it in any other formats?

Re: Spreadsheet and OS mapping genius solution sought
« Reply #4 on: 20 November, 2018, 08:56:13 pm »
Quote from: FifeingEejit[/quote
10km doesn't take you out of SH which should make it easier

So i think that makes the pseudofilter for your 10x10 box
NG Letters = SH
Easting between 5378 and 7378
AND Northing between 4143 and 6143

Much appreciate the seemingly positive/achievable reply  :)

Regrettably, I'm thick and too old/brain elsewhere :-[

What is a "pseudofilter" and how in child like steps, would one apply such a thing?

In Numbers on a Mac, as per the screenshot, choose Select All from the Edit menu, then go to Organise (top right), click Filter, then add rules for each of the above.

Re: Spreadsheet and OS mapping genius solution sought
« Reply #5 on: 20 November, 2018, 09:36:56 pm »
The origin of the OS grid square SH is 200000 (Easting), 300000 (Nothing).  So SH 6378 5143 is actually 263780,351430.

I'd be tempted just to add that offset to your OS references, and to your centre, and then use a formula based on Pythagoras to calculate the distances.

David Martin

  • Thats Dr Oi You thankyouverymuch
Re: Spreadsheet and OS mapping genius solution sought
« Reply #6 on: 20 November, 2018, 10:31:10 pm »
The origin of the OS grid square SH is 200000 (Easting), 300000 (Nothing).  So SH 6378 5143 is actually 263780,351430.

I'd be tempted just to add that offset to your OS references, and to your centre, and then use a formula based on Pythagoras to calculate the distances.
Why bother if they are all in square SH. The pythogoras works by difference anyway so adding to both sides of the operator is pointless.

you just need a formula like distance =  if ( square ='SH', sqrt((x1-x)*(x1-x) +(y1-y)*(y1-y)), 1000)
then sort by  distance from the shortest to the longest.
"By creating we think. By living we learn" - Patrick Geddes

Re: Spreadsheet and OS mapping genius solution sought
« Reply #7 on: 20 November, 2018, 11:13:03 pm »
Why bother if they are all in square SH. The pythogoras works by difference anyway so adding to both sides of the operator is pointless.

I always like to look at the bigger picture.  Today 10 km, tomorrow the world.   :D

slope

  • Inclined to distraction
    • Current pedalable joys
Re: Spreadsheet and OS mapping genius solution sought
« Reply #8 on: 21 November, 2018, 08:11:59 am »
Quote from: FifeingEejit[/quote
10km doesn't take you out of SH which should make it easier

So i think that makes the pseudofilter for your 10x10 box
NG Letters = SH
Easting between 5378 and 7378
AND Northing between 4143 and 6143

Much appreciate the seemingly positive/achievable reply  :)

Regrettably, I'm thick and too old/brain elsewhere :-[

What is a "pseudofilter" and how in child like steps, would one apply such a thing?

In Numbers on a Mac, as per the screenshot, choose Select All from the Edit menu, then go to Organise (top right), click Filter, then add rules for each of the above.

Thanks FifeingEejit and grams :thumbsup: Much appreciated

Brilliant! I've just performed my first ever pseudo filter and it worked  :)

slope

  • Inclined to distraction
    • Current pedalable joys
Re: Spreadsheet and OS mapping genius solution sought
« Reply #9 on: 21 November, 2018, 08:24:24 am »
Or convert it to GPX, then display it on a map? Would make it a lot easier to see what is where, and find the nearest points.

What's the source of the data? Can you download it in any other formats?

Thanks fuaran  :) That would also be very useful*. But the written location details in the spreadsheet are sometimes vital in addition to a pin on a map.

The source is https://www.ordnancesurvey.co.uk/benchmarks/ and seemingly only available in .csv



There is another online resource which does provide .gpx downloads, however the list of locations is not as complete as the OS data. https://www.bench-marks.org.uk/

Re: Spreadsheet and OS mapping genius solution sought
« Reply #10 on: 21 November, 2018, 02:52:17 pm »
That CSV file is >500,000 lines!
I've spent a whole morning trying to extract some local data from a very unwieldy spreadsheet. Eventually I opened the CSV file in a text file, copied the data I wanted, then imported it to a spreadsheet.
Still it's a crap day outside.

slope

  • Inclined to distraction
    • Current pedalable joys
Re: Spreadsheet and OS mapping genius solution sought
« Reply #11 on: 21 November, 2018, 03:44:41 pm »
That CSV file is >500,000 lines!
I've spent a whole morning trying to extract some local data from a very unwieldy spreadshhet. Eventually I opened the CSV file in a text file, copied the data I wanted, then imported it to a spreadsheet.
Still it's a crap day outside.

Numbers on the Mac couldn't/wouldn't handle the entire file, unlike Excel. But I just extracted all the OS SH stuff for North Wales  :)

Re: Spreadsheet and OS mapping genius solution sought
« Reply #12 on: 21 November, 2018, 04:19:30 pm »
You might like to look here http://www.deformedweb.co.uk/trigs/  The Trig Tools page.

FifeingEejit

  • Not Small
Re: Spreadsheet and OS mapping genius solution sought
« Reply #13 on: 21 November, 2018, 05:37:43 pm »
Quote from: FifeingEejit[/quote
10km doesn't take you out of SH which should make it easier

So i think that makes the pseudofilter for your 10x10 box
NG Letters = SH
Easting between 5378 and 7378
AND Northing between 4143 and 6143

Much appreciate the seemingly positive/achievable reply  :)

Regrettably, I'm thick and too old/brain elsewhere :-[

What is a "pseudofilter" and how in child like steps, would one apply such a thing?

In Numbers on a Mac, as per the screenshot, choose Select All from the Edit menu, then go to Organise (top right), click Filter, then add rules for each of the above.

Thanks FifeingEejit and grams :thumbsup: Much appreciated

Brilliant! I've just performed my first ever pseudo filter and it worked  :)

Ah but a pseudofilter is just a non-implementation specific description of a filter (see also pseudocode).
So you've actually just performed your first ever filter in your spreadsheet of choice.

Not being a mac user I had no idea of the spreadsheet software you were using so didn't want to give you an Excel formula (which is what I use)

As for the origin of "SH" being 200000 300000, I didn't have a 1940s New Popular 1 inch map handy to get them.
Using a 10km square is by far the easiest to figure out, if you do want a radius based filter then rather than doing all the maths there's probably something in the google api to load the benchmarks in and then apply a 10km radius circle to it.

frankly frankie

  • I kid you not
    • Fuchsiaphile
Re: Spreadsheet and OS mapping genius solution sought
« Reply #14 on: 21 November, 2018, 06:13:03 pm »
For a fee.
when you're dead you're done, so let the good times roll

David Martin

  • Thats Dr Oi You thankyouverymuch
Re: Spreadsheet and OS mapping genius solution sought
« Reply #15 on: 24 November, 2018, 12:29:44 am »
Personally I'd load it into QGIS and do a radius select.
"By creating we think. By living we learn" - Patrick Geddes