Chuesday Challenge:

I have 47 long/lat c-ordinates along a 200km stretch of road that I’d like to be more like 250 long lat co-ordinates along the same stretch.

They’re in excel, which seems like a reasonable place to start.

Any hints, tips, or answers?

Cheers loads,

Ed

edit: the road is wiggly.

What sort of interpolation do you want? Linear’s a POP, other forms get more complex.

Unless the road is straight, or there are consistent features, you are going to be on a hiding to nothing.
Why do you want to interpolate them? And could you not derive additional coordinates from a mapping program? Or even better use a GIS with a line feature?

Linear would not be any use if you want 250 from 47. you need to fit a curve really. Though fitting 4 points between every 2 seems a bit of a stretch

edit: the road is wiggly.

This makes interpolation pointless

This makes interpolation pointless

😆

As charlie says – if it’s wiggly then no amount of clever maths is going to predict where those wiggles go. If you had orientation of the road at each point as well as the positions then you could have a go at fitting something to the data but there’s no guarantee it’d be even close.

“Interpolate” makes it sound a bit mathsier that it probably needs to be.

I’d settle for 185: a mid point between each of my points as they are, and then mid points between the resulting list of 93.

I’ll give that a bash. it’ll be ugly and manual, as most of my excel tends to be, but I’ve give it a go…

If you want to do that, then fit a curve to the coordinates using the graphing tool and then use the resultant equation to calculate the additional points.

in that case just bisect the lines.

for x1.5 = ((x2-x1)/2)+x1 and the same for y1.5

geoffj, Sounds good! How easy is that? Can you explain how to do it in 50 words?

Charlies answer is better – and I’m not in front of a computer with excel on it.

ended up with taking the average between each long and lat, interspersing the two pairs of columns (labelling odd + even numbers then sorting numerically) then doing the same again.

I think that’s effectively the same as the same as charlie’s answer?

Hope I haven’t wasted too much time of anybody who didn’t want it wasting!

Bonus question, for fun only: how do I plot 185 long/lat values from excel into a mapping program, to see how spectacularly cock-eyed my “interpolation” is?

and thank you. speedy answers very much aprreciated. 🙂

Convert the excel/CSV into GPX?

http://www.topografix.com/GPX/1/1/

Have a loom at gps utility to convert from a dbf file to kml for viewing in google earth
Edit portlyone has it