Excel and MapPoint 2006 question

I received my MapPoint 2006 a couple hours ago and have started to play with the data wizard import features.

Looks basic enough, but evidently my GPS data in Excel is in the wrong format as it puts everything into Longitude east (or positive) which puts my pushpins in the heart of China.

I can't get Excel to take a negative longitude format, and I don't see a degree/minute/second option in the number formatting in Excel.

How do I get my longitudes to be picked up correctly by MapPoint? It likes the positive values well enough.

John

If I understand correctly, you have one column with decimal longitudes, and one column with decimal latitudes?

If so, just add a third column that is the negative of the (incorrectly) positive longitudes.

Eg. C1 = -A1, etc.


Richard

Richard,

Thanks! I have that part figured out....but the latest issue is that the point is off by about 10 miles from its actual location. So I still have conversion issues... The data came with a format like this: 119.58.56
When I put the -negative in, Excel doesn't like the second decimal, so it has to come out.

It looks like the data from the GPS unit is in decimal minutes, as the second values are over 60 (some of them), but that MapPoint may be reading it as minutes and seconds. I need to play with it a bit and figure out what is going on.

John

Looks like your Lon. is in degrees/Min/Seconds instead of degrees plus decimal degrees

You can check by looking at your data and seeing if anything past the decimal is over 60.

Convert: decimal degrees = degrees+Min/60+seconds/6000

You could also change the convention in Mappoint to D.M.S (I think it's under options).

Good luck.

Cheers,

Eric

Yes I agree with Eric - you need to convert to decimal degrees.
The error that you are quoting is about right - I would expect errors of "upto a few tens of miles".

Once you have that fixed, if you still see an error but it is much smaller - about 100m or so, and roughly consistent across a region (eg. all locations in a city are shifted about 10-150m in the same direction), then you probably have a geoid problem.
MapPoint uses WGS84 (the same as that used internally by the GPS satellites) - but we'll tackle that problem when/if we come to it :-)


Richard

Hi,

The data came with a format like this: 119.58.56
It looks like the data from the GPS unit is in decimal minutes, as the second values are over 60 (some of them)

If the secondary values are over 59 (like 119.88.55) then the 88 is not minutes. Are you sure this comes from a GPS receiver without some obscure conversion bofore it is stored into Excellformat ?

Normally a GPS receiver gives data in NMEA0183 format witch is ddmm.fff(f) for longitude and dddmm.fff(f) for latitude. Where fff(f) are the fractions of the minute.

Thanks everyone--sorry been extra busy for a while just now seeing all the responses. Here is a sample of my data:

119.51.47 36.22.67
119.51.34 36.24.23
119.51.23 36.23.08
119.51.47 36.24.83
119.57.07 36.30.58
119.57.50 36.30.58
119.42.05 36.29.36
119.40.30 36.28.88
119.52.44 36.28.91
119.51.90 36.29.11
119.52.45 36.29.22
119.51.89 36.28.67
119.51.25 36.28.69
119.51.69 36.29.18
119.58.20 36.32.47
119.58.12 36.32.45

It doesn't look like the last data is "seconds" (over 60) so I am guessing it is decimal minutes.

John

Hi,

Do you have a single coordinate where you know the exact place off ? This way it is more easy to check out what formatting is used.

You guys are the best!

The conversion process worked well---but it took a while to realize that "seconds" could be greater than 60 (mental block).

Once I divided by 60 for the mins and then 6,000 for the secs, everything is where it belongs.

My next question--import data wizard only allows 8 records. What do I need to do to get all the records on the same map?

John

I think you would have to code something to import each pushpin separately, the wizard as you are finding does not allow more. It's one of the many weak points of MapPoint.
Eric

well, that is an unexpected big bummer. MapPoint for me is useless with only 8 of the records on a map.

When you mention custom coding, what language, how involved, etc.

I have about 4,000 records I want to map.

John

Hi,

Any language will do (or at least the most). Have you a little experiance in one or some ?

I use the Link Data Wizard to link approx. 3000 locations on 2004 with no problems. I also just tried the Import data wizard in 2004 and will handle all my locations. Are you guys saying that 2006 will not do this? If so, any idea why this changed?