Spocky runs the Wildcare help phone line for this part of the world on Sunday afternoons for a few hours. People call in asking for help for injured animals, etc, and one of the problems is that when they say which suburb they’re in, she has to quickly go through pages and pages of lists of people in many areas to try to find one that’s the closest.

So.

Would it be possible to make up an Excel (or whatever) database that had all the rescuers and their locations, so that you could type in a suburb and have the database find the closest one? I’m happy to sit down and type in the Lat/Long of many suburbs. Also in the result it’d have to have the name & phone number of the closest person.

Any advice is greatly appreciated.

Well if you want to make her life easier you could also go lo tech, just have a map with carers locations marked and then all you need is a ruler from client location to nearest highlighted carer.

AwesomeO said:

Well if you want to make her life easier you could also go lo tech, just have a map with carers locations marked and then all you need is a ruler from client location to nearest highlighted carer.

Nah, it’ll be easier with a database thingy.

Spiny Norman said:

AwesomeO said:

Well if you want to make her life easier you could also go lo tech, just have a map with carers locations marked and then all you need is a ruler from client location to nearest highlighted carer.

Nah, it’ll be easier with a database thingy.

Well it’ll be easier than what you are doing now. And you can also see at a glance who’s nearest the suburb of the person ringing so hard to see how you can get easier than that.

Should be fairly easy if you have a column for suburb name or postcode. You could use the filter function (under the “data” tab) to filter the appropriate column only to display a particular suburb or postcode, or if you know your suburbs generally you can manually select a few either side.

party_pants said:

Should be fairly easy if you have a column for suburb name or postcode. You could use the filter function (under the “data” tab) to filter the appropriate column only to display a particular suburb or postcode, or if you know your suburbs generally you can manually select a few either side.

The problem is that there aren’t rescuers in every suburb.

Spiny Norman said:

party_pants said:

Should be fairly easy if you have a column for suburb name or postcode. You could use the filter function (under the “data” tab) to filter the appropriate column only to display a particular suburb or postcode, or if you know your suburbs generally you can manually select a few either side.

The problem is that there aren’t rescuers in every suburb.

Well, you’d need to group suburbs together into zones, and set up a new column for zones. Or consult a map for suburbs nearby so you can select those too in your filter search. Or if post-codes are distributed logically you can search for a range of postcodes.

party_pants said:

Spiny Norman said:

party_pants said:

Should be fairly easy if you have a column for suburb name or postcode. You could use the filter function (under the “data” tab) to filter the appropriate column only to display a particular suburb or postcode, or if you know your suburbs generally you can manually select a few either side.

The problem is that there aren’t rescuers in every suburb.

Well, you’d need to group suburbs together into zones, and set up a new column for zones. Or consult a map for suburbs nearby so you can select those too in your filter search. Or if post-codes are distributed logically you can search for a range of postcodes.

Has anyone mentioned Google Earth?

yes

Spiny Norman said:

party_pants said:

Should be fairly easy if you have a column for suburb name or postcode. You could use the filter function (under the “data” tab) to filter the appropriate column only to display a particular suburb or postcode, or if you know your suburbs generally you can manually select a few either side.

The problem is that there aren’t rescuers in every suburb.

But there is probably one in every postcode. It could even be done on paper lists. Organise a list for each postcode, and if there is none in a particular postcode then that entry would be for surrounding postcodes. On the other hand, perhaps there are too many carers in each postcode. Quandary.

I’d do it in Excel, with paper lists as backup, because I’m not really familiar with databases such as Mapinfo.

With Excel, one way would be to give each carer a GPS location in latitude and longitude with multipliers that convert that into x and y coordinates, such as y = latitude, x = (longitude-constant)*cos(latitude). Then input GPS latitude and longitude and Excel will immediately calculate distances to all the carers using distance = sqrt((x-x0)^2+(y-y0)^2). Sort carers by distance to get the nearest ones.

Yes it could be done in Google Earth. You could colour code different types of carers.

mollwollfumble said:

Spiny Norman said:

party_pants said:

Should be fairly easy if you have a column for suburb name or postcode. You could use the filter function (under the “data” tab) to filter the appropriate column only to display a particular suburb or postcode, or if you know your suburbs generally you can manually select a few either side.

The problem is that there aren’t rescuers in every suburb.

But there is probably one in every postcode. It could even be done on paper lists. Organise a list for each postcode, and if there is none in a particular postcode then that entry would be for surrounding postcodes. On the other hand, perhaps there are too many carers in each postcode. Quandary.

I’d do it in Excel, with paper lists as backup, because I’m not really familiar with databases such as Mapinfo.

With Excel, one way would be to give each carer a GPS location in latitude and longitude with multipliers that convert that into x and y coordinates, such as y = latitude, x = (longitude-constant)*cos(latitude). Then input GPS latitude and longitude and Excel will immediately calculate distances to all the carers using distance = sqrt((x-x0)^2+(y-y0)^2). Sort carers by distance to get the nearest ones.

Yes it could be done in Google Earth. You could colour code different types of carers.

I agree it would be pretty easy in Excel.

You would need one table with a list of all the people with their GPS location, and another table with the centroid locations of every post code. You can then enter a post code number and use VLookup to return the GPS data and the first table will calculate all the distances to that suburb. You can then just sort the table by that distance to get the closest people to the caller sorted to the top.

Converting from Lat/Long to X/Y would be pretty straightforward in Excel as well, and would just be a one-off.

The Rev Dodgson said:

mollwollfumble said:

Spiny Norman said:

The problem is that there aren’t rescuers in every suburb.

But there is probably one in every postcode. It could even be done on paper lists. Organise a list for each postcode, and if there is none in a particular postcode then that entry would be for surrounding postcodes. On the other hand, perhaps there are too many carers in each postcode. Quandary.

I’d do it in Excel, with paper lists as backup, because I’m not really familiar with databases such as Mapinfo.

With Excel, one way would be to give each carer a GPS location in latitude and longitude with multipliers that convert that into x and y coordinates, such as y = latitude, x = (longitude-constant)*cos(latitude). Then input GPS latitude and longitude and Excel will immediately calculate distances to all the carers using distance = sqrt((x-x0)^2+(y-y0)^2). Sort carers by distance to get the nearest ones.

Yes it could be done in Google Earth. You could colour code different types of carers.

I agree it would be pretty easy in Excel.

You would need one table with a list of all the people with their GPS location, and another table with the centroid locations of every post code. You can then enter a post code number and use VLookup to return the GPS data and the first table will calculate all the distances to that suburb. You can then just sort the table by that distance to get the closest people to the caller sorted to the top.

Converting from Lat/Long to X/Y would be pretty straightforward in Excel as well, and would just be a one-off.

It’d be best to use MGA94 co-ordinates (Eastings and Northings in metres) which is a UTM compliant X/Y grid. Most GPS instruments can give a readout in MGA94 as does Google Earth.

The formula for conversion (IIRC) is somewhat complicated to get into Excel, but is widely available. There are probably on-line tools to help you do so.

Please be aware that there are earlier mapping standards that can be easily confused because they have similar names, zones and coordinates (AMG 66 and AMG 84). Don’t use the older systems as they’ll be hundreds of metres out.

So, in summary, if you are using GPS and/or Google Earth, set them to MGA94 and you’ll have a standardised X/Y coordinate system to make Excel calculations in metres.

The Rev Dodgson said:

mollwollfumble said:

Spiny Norman said:

The problem is that there aren’t rescuers in every suburb.

But there is probably one in every postcode. It could even be done on paper lists. Organise a list for each postcode, and if there is none in a particular postcode then that entry would be for surrounding postcodes. On the other hand, perhaps there are too many carers in each postcode. Quandary.

I’d do it in Excel, with paper lists as backup, because I’m not really familiar with databases such as Mapinfo.

With Excel, one way would be to give each carer a GPS location in latitude and longitude with multipliers that convert that into x and y coordinates, such as y = latitude, x = (longitude-constant)*cos(latitude). Then input GPS latitude and longitude and Excel will immediately calculate distances to all the carers using distance = sqrt((x-x0)^2+(y-y0)^2). Sort carers by distance to get the nearest ones.

Yes it could be done in Google Earth. You could colour code different types of carers.

I agree it would be pretty easy in Excel.

You would need one table with a list of all the people with their GPS location, and another table with the centroid locations of every post code. You can then enter a post code number and use VLookup to return the GPS data and the first table will calculate all the distances to that suburb. You can then just sort the table by that distance to get the closest people to the caller sorted to the top.

Converting from Lat/Long to X/Y would be pretty straightforward in Excel as well, and would just be a one-off.

Jolly good, thanks. I’m not very proficient in Excel but I know a few people locally that are so I’ll try them first.
Fingers crossed they’ll be able to do it easily enough.

The way they do this in (some) emergency service dispatch is to pinpoint the job on an electronic map, then conduct a radial search of the five nearest emergency responders by their ‘pick’ point (GPS position), calculate the travel time (distance times a fixed average travel speed), add to that their average turnout time, and produce a list that ranks them according to who is likely to get their quickest, with their contact details.

I know this sounds over-blown, but it is exactly what Spocky needs, and it’s already out there in the world, being used by similar emergency mobs… It might even be available to her, I don’t know.

I’ve got contacts if you want more…

Rule 303 said:

The way they do this in (some) emergency service dispatch is to pinpoint the job on an electronic map, then conduct a radial search of the five nearest emergency responders by their ‘pick’ point (GPS position), calculate the travel time (distance times a fixed average travel speed), add to that their average turnout time, and produce a list that ranks them according to who is likely to get their quickest, with their contact details.

I know this sounds over-blown, but it is exactly what Spocky needs, and it’s already out there in the world, being used by similar emergency mobs… It might even be available to her, I don’t know.

I’ve got contacts if you want more…

Thanks, software like that could be rather good.

• I know this sounds over-blown, but it is exactly what Spocky needs, and it’s already out there in the world, being used by similar emergency mobs… It might even be available to her, I don’t know.

Just about every bricks and mortar (chain) shop with an online presence has a similar thing with “Find your nearest store”…

Spiny Norman said:

Rule 303 said:

The way they do this in (some) emergency service dispatch is to pinpoint the job on an electronic map, then conduct a radial search of the five nearest emergency responders by their ‘pick’ point (GPS position), calculate the travel time (distance times a fixed average travel speed), add to that their average turnout time, and produce a list that ranks them according to who is likely to get their quickest, with their contact details.

I know this sounds over-blown, but it is exactly what Spocky needs, and it’s already out there in the world, being used by similar emergency mobs… It might even be available to her, I don’t know.

I’ve got contacts if you want more…

Thanks, software like that could be rather good.

I believe it could be done in Google Maps.

If you want a starting point, I know for sure that these guys are using it (radial search nearest responder system) and they have a very friendly community liaison operation, if you’re patient.

furious said:

• I know this sounds over-blown, but it is exactly what Spocky needs, and it’s already out there in the world, being used by similar emergency mobs… It might even be available to her, I don’t know.

Just about every bricks and mortar (chain) shop with an online presence has a similar thing with “Find your nearest store”…

Yeah, similar.

Furious gave me an idea:

https://developers.google.com/maps/solutions/store-locator/clothing-store-locator

Overview

This tutorial shows you how to build a Google Maps application for your website that allows your users to search for store locations that are nearest to them.

Also, a word of caution – If you group by suburb, postcode, zone, region, or whatever, it’s only a matter of time before the search will produce stupid results. Even line-of-sight calculation can be dicey.

If you maximum bang-for-your-buck, just load the location and contact details of all your rescuers into a Google map and let Spocky’s wonderful human brain pinpoint the job and estimate the travel times.

This has possibilities.
Youtube stuff

Have a look at Map My Contacts or similar
https://www.labnol.org/internet/google-contacts-map/26107/

Brett

The Rev Dodgson said:

JudgeMental said:

Michael V said:

The Rev Dodgson said:

SCIENCE said:

The Rev Dodgson said:

that pressing Ctrl+` in Excel will toggle between displaying formulas and displaying their values.

Where ` is under the ~ symbol, to the left of the 1 key.

oh yeah that was a useful trick we discovered, how were you viewing formulas before though

By selecting the cell with the formula and looking in the edit bar and/or press F2.

Me: I click on the cell and look in the formula bar above. Not too onerous.

I don’t use excel.

Yeah, the shortcut is to make all the formulas display as formulas, which I rarely want to do, so that is why I didn’t know the shortcut.

Might come in handy sometimes though.

oh yes that would have been it

we thank JudgeMental for reminding us

we were bulk copying formulas to our text editor of choice at the time, maybe 500 Ms ago now, and it was the only way we could find to do it

probably doing some kind of image processing slash neural network 爱 at the time we think

mirrored from the tomorrow we remembered thread

SCIENCE said:

The Rev Dodgson said:

JudgeMental said:

I don’t use excel.

Yeah, the shortcut is to make all the formulas display as formulas, which I rarely want to do, so that is why I didn’t know the shortcut.

Might come in handy sometimes though.

oh yes that would have been it

we thank JudgeMental for reminding us

we were bulk copying formulas to our text editor of choice at the time, maybe 500 Ms ago now, and it was the only way we could find to do it

probably doing some kind of image processing slash neural network 爱 at the time we think

mirrored from the tomorrow we remembered thread

Moving to the appropriate thread:

“Not sure why you’d want to bulk copy formulas to a text editor, but each to their own,”

good point maybe we were just looking to do a replace all on formulas rather than values

You can do that in Excel.

In fact I often copy code from a text editor into Excel to make it easier to compare and edit different versions of computer code.

The Rev Dodgson said:

SCIENCE said:

The Rev Dodgson said:

Yeah, the shortcut is to make all the formulas display as formulas, which I rarely want to do, so that is why I didn’t know the shortcut.

Might come in handy sometimes though.

oh yes that would have been it

we thank JudgeMental for reminding us

we were bulk copying formulas to our text editor of choice at the time, maybe 500 Ms ago now, and it was the only way we could find to do it

probably doing some kind of image processing slash neural network 爱 at the time we think

mirrored from the tomorrow we remembered thread

Moving to the appropriate thread:

“Not sure why you’d want to bulk copy formulas to a text editor, but each to their own,”

good point maybe we were just looking to do a replace all on formulas rather than values

You can do that in Excel.

In fact I often copy code from a text editor into Excel to make it easier to compare and edit different versions of computer code.

yeah so we do a whole heap of stuff in plaintext not sure exactly why it’s just lightweight and easy

Link for where I learned about the display formulas thing

Some other useful things there + some “new” features like:

Focus Cell
If you work with large spreadsheets, the Focus Cell feature (available in Microsoft 365 for Windows beta) highlights the row and column of the selected cell, making it easier to trace data across large ranges:

that Lotus 123 had about 40 million seconds ago.

The Rev Dodgson said:

Link for where I learned about the display formulas thing

Some other useful things there + some “new” features like:

Focus Cell
If you work with large spreadsheets, the Focus Cell feature (available in Microsoft 365 for Windows beta) highlights the row and column of the selected cell, making it easier to trace data across large ranges:

that Lotus 123 had about 40 million seconds ago.

wait does Lotus 1-2-3 even exist 40 Ms ago

It’s going to be difficult, Mr Norman, if you want it to find “ the nearest” to a particular location. ie. automatically and gradually “zoom out” from a particular location (different for each search) until it finds a carer.

Satnavs in the car do it, say if you are wanting the nearest Harvey Norman to where you currently are. But Sat Navs don’t have wildlife carers…. or do they? I haven’t looked to see if they do, eg. WIRES.

How large is the region that Ms Spocky covers?

I’d also suggest a map on the wall (or similar) with pins in where local carers are located.

SCIENCE said:

The Rev Dodgson said:

Link for where I learned about the display formulas thing

Some other useful things there + some “new” features like:

Focus Cell
If you work with large spreadsheets, the Focus Cell feature (available in Microsoft 365 for Windows beta) highlights the row and column of the selected cell, making it easier to trace data across large ranges:

that Lotus 123 had about 40 million seconds ago.

wait does Lotus 1-2-3 even exist 40 Ms ago

Sure, it even exists now, and I will be using it to do my invoicing later today.

But I missed out a 24 in my calcs. I meant about 0.8 Gs ago.

The Rev Dodgson said:

SCIENCE said:

The Rev Dodgson said:

Link for where I learned about the display formulas thing

Some other useful things there + some “new” features like:

Focus Cell
If you work with large spreadsheets, the Focus Cell feature (available in Microsoft 365 for Windows beta) highlights the row and column of the selected cell, making it easier to trace data across large ranges:

that Lotus 123 had about 40 million seconds ago.

wait does Lotus 1-2-3 even exist 40 Ms ago

Sure, it even exists now, and I will be using it to do my invoicing later today.

But I missed out a 24 in my calcs. I meant about 0.8 Gs ago.

wait 9.8.2 is that good that it completes with modern Excel respect