Why is it so difficult to create clean POI files

 

I modify POI files for the way I want to see them. The problem I find is that 80% of the ones I use are from 20% to 50% messed up because of extra spaces, commas, colons, missing data, bad data, brackets, mis-spellings.....
Why is it so hard to get clean files?
The first thing I have to do is load the file into Excel then search for problems. There are a few that are actually perfect. No misplaced data, duplicate phone numbers, improperly formatted zip codes, missing addresses......

I get them cleaned, up to 98% correct, but must do it for each update. I tried to give away the clean files or upload them as clean but no one seems to care.

Really? Work ethic is even lost on volunteer projects now.

What works for some... (you know the rest)

Well "ruggb",

I'm having trouble finding POI files you have created, so I have no idea what you consider perfect.

If you are opposed to commas in general, then there are probably a lot of folks that rather like them, as they contribute to legibility.

Similarly, some of us like State/Province names spelled-out, rather than a 2-character abbreviation, for the same reason.

I'm guessing you are also not in favour of apostrophes either, as they no doubt add "clutter". (Please excuse the extra "u" in favor, but this is not an entirely US-centric web site, so is not in fact a miss-spelling).

A lot of what we create is a labour-intensive (there I go again) scroll, copy, paste from comapany web sites. We don't have time to proof-read all they have. In addition, typos are bound to creep in, dropped digits from phone numbers, etc.

As for postal codes (sorry, ZIP codes), why do we need them here anyway? (Really a topic for another thread). Are you mailing something from your vehicle?

Have you EVER contacted the originators of the POI files that have you so upset. Perhaps you don 't use any of mine, as I have never received an email from you with suggestions or corrections.

Perhaps the POI Factory is hiding your files from me when I view your profile (I see other contributors' that I have checked). I can see that you are rather prolific in the forums, so maybe you can give some specific file names that irk you so, or give us a list of yours so that we can see some examples of "perfection" that we may emulate.

email me

I have many problems with my POIs and the file. Can you email me one, or post here for me to download? I mean it, I can get any of mine to work, let me see one that you have that you know is good and I will copy it/mirror it to mine so I can get them right.

patrickg450@yahoo.com

thanks

--
PG

POI files

I would like to make a POI file of the rest areas in BC. Problem is, my database is a map. I'm sure the map is propagated by a data file somewhere but if there is a relatively simple way to extract data, I'd love to hear it.
https://www.th.gov.bc.ca/restareas/?zoom=5&loc=-126.600000%2...

zip codes

oldboy wrote:

~snip~

As for postal codes (sorry, ZIP codes), why do we need them here anyway? (Really a topic for another thread). Are you mailing something from your vehicle?

~snip~

I get the whole gist of your comment, however, I'd like to side track for a moment on zip codes.

I understand their importance. I live in an area that has more than one town with the same name, and many streets in different areas with the same name as well, so knowing the zip is pretty important to help zero in on the right destination.

That is all, back to the roast.

.

--
. 2 Garmin DriveSmart 61 LMT-S, Nuvi 2689, 2 Nuvi 2460, Zumo 550, Zumo 450, Uniden R3 radar detector with GPS built in, includes RLC info. Uconnect 430N Garmin based, built into my Jeep. .

Maybe?

doug r wrote:

I would like to make a POI file of the rest areas in BC. Problem is, my database is a map. I'm sure the map is propagated by a data file somewhere but if there is a relatively simple way to extract data, I'd love to hear it.
https://www.th.gov.bc.ca/restareas/?zoom=5&loc=-126.600000%2C54.589121

Use the Garmin map to situate your locations since most if not all people here probably use a Garmin and thereby use the Garmin maps which should make your locations jive with the GPS.
Works for me since there is NO way you can physically check out every location personally.

--
Nuvi 350 long gone, Nuvi 855LMT, Nuvi 2797LMT, DriveSmart 50 LMT-HD, 3790LMT now my daughters. Using Windows 10. DashCam A108C with GPS.

Accurate POIs

doug r wrote:

I would like to make a POI file of the rest areas in BC. Problem is, my database is a map. I'm sure the map is propagated by a data file somewhere but if there is a relatively simple way to extract data, I'd love to hear it.
https://www.th.gov.bc.ca/restareas/?zoom=5&loc=-126.600000%2C54.589121

Here's one way, but someone may have a better one.

Open the BC rest area map in a browser window (IE, Chrome, Firefox, etc.). Note: Since there doesn't seem to be a list of sites, you may want to do a screen grab and print it so you can mark off what you've captured. See: https://www.digitalcitizen.life/4-ways-take-screenshots-wind...

Open Google maps in a separate browser window by using the desktop icon.

Shrink and relocate both windows so that one is on the left half of the screen and the other is on the right half.

Zoom in on the "BC Rest Areas map" enough so that you can use it to locate the area on the Google Maps using "street view".

Once you think you are close enough, switch to "satellite view" (small rectangle in lower left of map).

Pan and/or zoom until you can see the rest area. Note: You may want to use "Street View" to verify that you do have the correct location. Go back to map/satellite view when finished "looking".

IMPORTANT! Ensure that the "Search Google Maps" box is empty - if not clear of all text, then the next steps will not work!

Do a SLOW double click on the highway (in Google maps) just before the entrance to the rest area. (Too fast and the map zooms in)
IMPORTANT! Don't use the center of the RS as the POI, as the driver will probably overshoot and/or will have to make a U-turn. Vehicles with trailers will not be happy. MAKE TWO POIs - one for each entrance if it's a 2 lane road. Only one POI is needed on 4 lane divided highways w/no crossover.

When you do a slow double click on the Google map, a gray dot appears with an exclamation point inside and near the bottom of the screen an "Info Box" appears.
IMPORTANT! Moving, panning or clicking on the map will close the box.

In the info box is the following data:
On the top line is street address or the highway number.
On the second line is the nearest city or town.
On the bottom line is the L/L that you want.
IMPORTANT! The L/L is displayed exactly backwards from the way it is entered for a Garmin.

Carefully click once on the L/L at the bottom of the info box.

The L/L will appear in the "Search box" in the upper left. Ex.: 59.998666, -129.053719 (Copying it from the Info Box does not work well)

At this point, you can "Copy & Paste" the data into you POI file. Just remember that the L/L is displayed backwards in Google Maps for Garmin POIs.

Click once on the map and all location info will disappear.

Mark off RS on your BC rest area map and repeat steps for the next POI.

I know it seems complicated, but it's really not. The hardest part is locating and verifying that you've got the right L/L for the RS. Seems that a lot of them are out in the Boonies where the satellite picture resolution is low.

BWT - The location above was for the RS at the BC/Yukon border (59.998666, -129.053719 ) on RT 37. Don't know how accurate Google is there. If you look at the map using satellite view, you can see the physical border cut through the trees. BUT, Google shows the border as a dotted line about five hundred feet north of there. "Google Earth" shows the same error. In satellite view it looks like the RS is on the east side of the highway, but using street view you will see that is very old and maybe it was a RS - but it's not now.

--
Metricman Nuvi 660, GTM-20 Traffic Receiver Nuvi 3597 GTM-60 Traffic Receiver Williamsburg, VA

We Seem to have wandered off-track

This thread seems to have drastically morphed into a completely different subject, namely Rest Areas in B.C.

Might I suggest that if you have a specific POI-related problem that you initiate a NEW thread, with relevant Subject line, instead of hijacking an existing topic.

That makes it much easier for the rest of us to spot, identify and maybe contribute a solution, rather than the random chance of a user just happening to be in an unrelated thread with maybe an answer.

A Lot Of Time..

And effort goes into creating these files. Many of us, including myself, rely on comments or corrections from other members for major errors.

All the files I download here work perfectly 95-100% of the time in my GPSr's. Isn't that what really matters?

i believe

bdhsfz6 wrote:

And effort goes into creating these files. Many of us, including myself, rely on comments or corrections from other members for major errors.

All the files I download here work perfectly 95-100% of the time in my GPSr's. Isn't that what really matters?

I believe what the OP's base problem centers around the fact there is no standardization of the data within the files. If you are talking about a CSV file, the only standard is the first three elements. These have to be Lat, Lon and Name. A few years back Garmin changed POI Loader where it would take Lat/Lon in either order when originally it was Lon/Lat. The fourth field found in the CSV format is description and there is no truly accepted standard on what info or format it needs to contain.

GPX files are different in that each element is defined with a label identifying what is contained. There is no standard as to the order the elements are in the fle as the loader puts each in its proper spot as the file is read.

--
Illiterate? Write for free help.

Pardon My Ignorance

Box Car wrote:
bdhsfz6 wrote:

And effort goes into creating these files. Many of us, including myself, rely on comments or corrections from other members for major errors.

All the files I download here work perfectly 95-100% of the time in my GPSr's. Isn't that what really matters?

I believe what the OP's base problem centers around the fact there is no standardization of the data within the files. If you are talking about a CSV file, the only standard is the first three elements. These have to be Lat, Lon and Name. A few years back Garmin changed POI Loader where it would take Lat/Lon in either order when originally it was Lon/Lat. The fourth field found in the CSV format is description and there is no truly accepted standard on what info or format it needs to contain.

GPX files are different in that each element is defined with a label identifying what is contained. There is no standard as to the order the elements are in the fle as the loader puts each in its proper spot as the file is read.

I agree with you here and what you say is true but respectfully, it doesn't answer my question. If I'm missing something, please point me in the right direction.

Contributors post files here for others to download and use in their GPSr's. In my experience, I have yet to see one rife with errors or fail to function. Yes, I do spot an occasional error and usually report it to the file creator.

If the files we download perform their intended purpose and work in our GPSr's, what difference does it make if they are "clean" or not?

What is the OP doing with these files that causes a problem?

My Sentiments

bdhsfz6 wrote:
Box Car wrote:
bdhsfz6 wrote:

And effort goes into creating these files. Many of us, including myself, rely on comments or corrections from other members for major errors.

All the files I download here work perfectly 95-100% of the time in my GPSr's. Isn't that what really matters?

I believe what the OP's base problem centers around the fact there is no standardization of the data within the files. If you are talking about a CSV file, the only standard is the first three elements. These have to be Lat, Lon and Name. A few years back Garmin changed POI Loader where it would take Lat/Lon in either order when originally it was Lon/Lat. The fourth field found in the CSV format is description and there is no truly accepted standard on what info or format it needs to contain.

GPX files are different in that each element is defined with a label identifying what is contained. There is no standard as to the order the elements are in the fle as the loader puts each in its proper spot as the file is read.

I agree with you here and what you say is true but respectfully, it doesn't answer my question. If I'm missing something, please point me in the right direction.

Contributors post files here for others to download and use in their GPSr's. In my experience, I have yet to see one rife with errors or fail to function. Yes, I do spot an occasional error and usually report it to the file creator.

If the files we download perform their intended purpose and work in our GPSr's, what difference does it make if they are "clean" or not?

What is the OP doing with these files that causes a problem?

Those are my sentiments. If the file works as intended, then it's fine. If the OP wants to disect the file and use it for his purposes, it's on him to make the necessary changes.

Angela already proofs the files for glaring errors and show stoppers.

--
Frank DriveSmart55 37.322760, -79.511267

The OP hasn't been back since to clarify

I too am curious.

--
Striving to make the NYC Metro area project the best.

I tried to post a cleaned up

oldboy wrote:

Well "ruggb",

I'm having trouble finding POI files you have created, so I have no idea what you consider perfect.

OLDBOY

I tried to post a cleaned up Walmart file and was told by admin it was unacceptable because it was just a different format.
Therefore, I can't show you unless I PM one to you.

Apparently, clean does not mean anything. HOWEVER, there are a few that are perfect relative to formatting.
I am also not talking about content. If the file is clean, it is not difficult to sort it the way I want to see it. If the file has bad data, end of story.
What messes up CSV files is misplaced commas, misplaced data - phone numbers where zip codes should be - etc.
I understand people use these files in different manners, but if the file is "clean" all the entries on a GPS will be formatted the same way and not with information scattered in different places.
I clean the files up using Excel. If you open your files with Excel it will be immediately obvious as you scan thru them if they are clean or not.
You may have to first edit them with Notepad and delete all the "" quote marks. This will the put all the comma delimited fields in separate columns making it very obvious.
Otherwise, it will be hard to notice a phone number b4 a zip code since the quoted text will be in a single column.
It would also be good if people separated provinces from zip codes as they do states from zip codes. Either put the comma in there or leave it out on all of them. Otherwise, they look and separate differently.
With a clean CSV file, I can format it the way I would like it in about 2 minutes. Having to clean a file every time it is updated b4 I can format it is a PITA.

WORK does not = WORK

bdhsfz6 wrote:

And effort goes into creating these files. Many of us, including myself, rely on comments or corrections from other members for major errors.

All the files I download here work perfectly 95-100% of the time in my GPSr's. Isn't that what really matters?

The operative word here is "WORK". How they WORK for you and how I want them to work for me obviously is not the same.
If you are happy with all the Target entries showing up on your screen as Target-Omaha instead of an address, that is good. I am not. Mine is set up so that I select Target, so all the entries I see are Target. What I want to see is the address, not Target-Cary. I know I am in Cary and I know that I am looking at Target listings and I know that I want to go to the Target on North St so why would I want to have to select one, go the the 2nd page to see if it is on North St, then go back and do it over because it was on West St.

If the files are clean, I can reformat them the way I want in a heartbeat. If they are not it takes me 1/2 for each file EVERY TIME it is updated.

Sorry

camerabob wrote:

I too am curious.

Sorry, I only get here once a week since I don't get an email when someone answers.

Who is listwening?

phranc wrote:

Those are my sentiments. If the file works as intended, then it's fine. If the OP wants to disect the file and use it for his purposes, it's on him to make the necessary changes.

Angela already proofs the files for glaring errors and show stoppers.

Obviously, I disagree. Since I cannot post cleaned up files per Angela, I cannot show you what the problem is and explaining it obviously doesn't get thru. It would be great if someone would establish a std format for these files. Then it would be a very simple matter to format them the way I want to see them. I have no problem doing that. When the data is all mixed up and there are extra commas all over the place, opening the files in Excel to reformat them produces a mess.

Maybe we could Zoom and I can show you what I mean.
On top of that, no one is interested in taking my clean Excel file and using it. If they did, I wouldn't have to reclean it with every update.
By CLEAN, I do not mean the way I want it to appear on my GPS. I mean that all phone numbers, store number, zip codes, etc. appear in a column for that data. Taking that Excel file and creating a CSV with any 3rd and 4th columns (they way GPS displays them) takes about 1 minute.
If you are making changes in a text editor it will never be right. It must be done in Excel. And if you are doing it in Excel and it is still bad you need some lessons on using Excel.

I would love to show anyone how it is done. It will save you, me and I am sure a lot of other people lots of time.

This definitely needs a standard.
Who is listening???

But I will try to explain, again

A typical Excel file column format:
LON, LAT, STREET ADDRESS, STATE/PROVINCE, ZIP, PHONE, DATA1, DATA2, DATA3,......

It does not matter what info is in what column as long as DATA1 is not put into the ZIP column, etc. I.E., the columns have mixed data. This is caused by commas.

My Excel work file has 3 sheets
INPUT, WORK, CSV

To fit on a GPS there is a max of 4 columns
LON, LAT, PAGE1, PAGE2
Any Page with an embedded comma must be enclosed in ""

I take a POIFactory CSV file, open it in Notepad and delete all the "".
Then I open it in Excel. I now have all the data in their own columns. Obviously, if the data is mixed up in the CSV file, it is now mixed up in the Excel columns. Also, If there are stray/extra commas in the CSV file it caused extra columns to be created. CSV = Comma Separated Values
I then must sort, scan and fix all the misplaced data to get a clean file.

I then copy and past that worksheet into my Excel work file INPUT sheet.

The WORK sheet is pre-programmed to take the data from the INPUT sheet and format it into LON, LAT, PAGE1, PAGE2 columns using the CONCATENATE function to create PAGE1 and PAGE2 the way I want to see them.

I then copy and past those 4 columns into the CSV page and save that worksheet as my new CSV file.

The CSV filenames are used in the GPS as POI titles. I have folder ALL with sub-folders GAS, DISCOUNT STORES, FAST FOOD, SCENIC PLACES, etc. and put each CSV file into the appropriate folder. Then I POILoad the ALL folder creating an ALL.gpi file. I POILoad the ALERT files (red light, speed camers, reat areas from the ALERT folder since I don't want them to appear with my other POIs. Then I copy ALERT.gpi and ALL.gpi to my 4 GPS Nuvis.

When I pull up my POIs, what I see is DISCOUNT STORES, FAST FOOD, SCENIC PLACES, etc. when I touch FAST FOOD is see McDONALD, BURGER KING, etc. WHEN I pull up BURGER KING, I see street addresses. If I go to PAGE2 I see all the rest of the info I want. I do not see BURGER KING on PAGE1 because I already know what I am looking at.

If you do not have yours set up like this, then you need to see McDONALD, and you can use the POIFactory file as is.

What I see when I open Walmart is S,G,D, 345 Main St Indicating it is a Supercenter, it has Gas and Diesel. I do not have to fumble to the PAGE2 for that info.
I have the same arrangement for other stores.

So, THE BOTTOM LINE is I don't care what data is in the CSV files uploaded to POIFactory as long as each part is comma separated.

There are two issues:
- STREET ADDRESSES with addition of suite numbers, etc that is comma delimited. Again, if it is consistent, I don't care. I can easily handle that. However, if you add that you need those commas after every street address if it does not include a suite. "street, suite" or "street,"
- Canadian Province/Zip codes - typically, and not necessarily consistent within a file they are not separated by commas, but US state and zip are. Either separate them all or not. I don't care, just be consistent.

MAYBE a format can be created out of this.
Put ANY data you want into the CSV files.
Use Excel (or OpenOffice Spreadsheet) to be sure it is consistent in that if you delete all the "" with Notepad and open the file in Excel all the data is in their columns.
That simply means you can't have any stray commas.

Now if you want to see how bad it is try that on any CSV file listed on POIFactory. Let me know what you find.

OH, BTW Angela, I think it is bad format to allow multiple stores in one file as in "Walmart_United States & Canada.csv" For one, there is already a MURPHY file and most people who shop at Walmart are not members of SAM's. And if I were looking for a SAM's file how would I know it is in the Walmart file.

ruggb

ruggb wrote:

...
OH, BTW Angela, I think it is bad format to allow multiple stores in one file as in "Walmart_United States & Canada.csv" For one, there is already a MURPHY file and most people who shop at Walmart are not members of SAM's. And if I were looking for a SAM's file how would I know it is in the Walmart file.

Thanks for all the posts.

First, let me comment on the quoted section above.

The names of the several .csv files on poi-factory are
Wal-Mart Stores, Super Wal-Mart & Sam's Club, and
Walmart, Sams Clubs, and Super Walmart Centers in the US & Canada

I guess I would ask Angela to add "Murphy" to the name to be totally correct. AND, I would be interested to know whether the separate Murphy file at
http://www.poi-factory.com/node/12083
is consistent with those in the "Walmart" files

I was having trouble understanding what your problem was.

I now understand that you want the information from a POI file displayed in a particular way on your GPS.

One of the problems that you are having is that the "Wal-Mart" file has fields that are not always present. Sometimes, the store number may not be available and things don't line up neatly.

SO - stripping out the Quotes does not result in a .csv file with consistency in the columns. I suspect these are being interpreted as extra commas.

Maybe you could use the .GPX file

GPX and Garmin CSV

It’s easy to end up frustrated when people are sharing files that have much of the information you want but that aren’t exactly matching your preferences.

Where things stand today is that POI Factory accepts two file formats that are directly usable by Garmin’s POI Loader software: GPX and Garmin CSV.

Of the two, GPX is more structured; and Garmin CSV is less structured.

Among other things, the GPX format (with Garmin extensions) includes support for many discrete fields such as:

- Longitude
- Latitude
- Name
- Comment
- Description
- Source
- Link
- Symbol
- Type
- Street Address
- City
- State
- Country
- Postal Code
- Phone Number

By contrast, the Garmin CSV format has only 3 or 4 discrete fields:

- Longitude
- Latitude
- Name
- Description (optional)

The common denominator is that either format is directly usable by Garmin’s POI Loader software.

If you’re looking to import and process information outside of POI Loader, GPX offers a lot more flexibility; but a trade-off is added complexity.

Some maintainers prefer to create and share files in GPX format; others prefer to share files in Garmin CSV format.

My sense is that we would loose maintainers if we required all files to be in GPX format or if we placed further constraints on formatting within the Garmin CSV format.

It's a trade-off, but I'm thankful that people are volunteering to share files and be part of the community.

no preference

JM wrote:

It’s easy to end up frustrated when people are sharing files that have much of the information you want but that aren’t exactly matching your preferences.

I don't have a preference for the files. Why can't you get that? All I want is for the files to be consistent relative to where columns are if I eliminate "" and open the file in Excel. I have a preference for the way they appear on my GPS. I don't expect people to create CSV files just for me. But it is a big PITA if I have to fix files before I can format them for me every time they are updated and contain the same inconsistencies and errors. If the field data is not always present, the comma should still be there. Otherwise, opening it in Excel will shift all the data.

It does not take a rocket scientist to do this and it doesn't take much time if one is using a spreadsheet to build these files. You would have to be insane not to be using a spreadsheet but by the looks of some of the files I would have to say some people do not know what a spreadsheet is.

If you have 10 pieces of information, use 10 columns. Make sure the right info is in the right column and do not use commas in a column.

Format it any way you like and create a CSV file. Any commas in the CSV file will translated into separate columns when opened in EXCEL after "" are removed. It becomes very easy to verify the data and edit things to fix data errors when needed.

But I guess I am talking to brick walls.
At any rate, I will gladly demonstrate how easy it is to anyone who is interested.

Please, read to understand me, not to answer me

jgermann wrote:

The names of the several .csv files on poi-factory are
Wal-Mart Stores, Super Wal-Mart & Sam's Club, and
Walmart, Sams Clubs, and Super Walmart Centers in the US & Canada

I guess I would ask Angela to add "Murphy" to the name to be totally correct. AND, I would be interested to know whether the separate Murphy file at
http://www.poi-factory.com/node/12083
is consistent with those in the "Walmart" files

I think you are confusing CSV file names with verbiage used in posts. The only file name I find is "Walmart_United States & Canada.csv" and it also contains Murphy, which by my inspection contains some, but not all, of what is in the Murphy file and vice versa - so no they are not consistent in the data.

The reason CSV files are not consistent after stripping out the "" is because they were not consistent when created. The inconsistency is mainly a result of stray commas, but is also about putting the info into the proper column. Having phone numbers and zip codes swapped is not a result of commas.
By "proper" I mean the column that contains all the rest of the phone numbers or zip codes or street addresses or suite numbers,...

I can send you one of my CSV files. You can strip out the "" and open it in Excel and the data will be in the proper columns.
Then you can rearrange the columns any way you want into 4 columns and create your own CSV file to view it just the way you want to see it. As long as there are commas ONLY separating each field within the "" in the CSV file, stripping out the "" and reopening it in Excel will get you back where you started.

PLEASE, do not read this to answer me, read it to understand me.