HomeMicrosoft


POI files for beautiful villages of France
werdnanostaw
Hi Everybody

We are in La Rochelle in France.

We rely on Microsoft AutoRoute to find all the tiny roads that hug the coast - inland is so boring.

Every night I consult our guide books (look away now people who work for a company that is into DRM) - 40 or so photographed travel books stored on the PC - to work out where to go the next day. I also have POIs for "The Most Beautiful Villages in France"

Les plus beaux villages de France - Site officiel

stored in AutoRoute that I use to plot the route by dragging the route between the start and stop hotels. There are usually 20 or 30 via points (to use Garmin terminology) each day.

Last year we took 4 months to drive from Paris, down the west coast of Italy, around the toe of the boot, ferry to Greece, Turkish Med coast to the Syrian border, up to the Black Sea, back to Istanbul, northern Greece, ferry to Venice and back to Paris through Germany.

It was a great relief to get back to AR in Greece after 2 months in Turkey using Garmin. Turkey would be a great addition to AR's map coverage.

The year before that we took 4 months to drive from Paris to Holland, down the coast to Brittany, ferry to Ireland where we followed the coast all the way around, ferry from Belfast to Scotland, down through UK, ferry to Calais and back to Paris.

The year before that was 6 months touring France, Holland and Germany and England, Scotland and Wales.

None of this would be possible without AR. Please keep up the good work.

In case you can't guess we're retired.
SpadesFlush
I am intrigued by the Villages link to which you have alerted us. Unfortunately, the site does not seem to provide a CSV or GPX download. Any chance you have created one you could share? Or an AXE file? You don't want to force me into making one, do you?

Whilst you are in La Rochelle, I hope you will venture across the bridge to Ile de Re, where I have spent many vacations. It is not as pretty as it used to be (the bridge lets the riff-raff in) but the oysters are still good.
werdnanostaw
I don't have the POI file with me. I only have my AXT file that has a lot of other stuff in it.

This site has an amazing collection of POIs in France.

GpsPasSion Forums - SOMMAIRE PRINCIPAL : Accs aux collectes

One of the links leads to

GpsPasSion Forums - Villages coup de coeur

Villages coup de coeur, which I think is the one I used.

There are more POIs than are listed on the Plus Beaux Villages site. I have intended to extract just the PBVs but I've never got around to it.

I am a little dubious of some of the sites listed. Some are quite ordinary.

Also, we have found a lot of beautiful towns/villages that aren't listed, eg Libourne near Bordeaux which we visited today. Well maybe not beautiful but certainly interesting with it's bridge, river frontage and shopping arcades around main square.

http://maps.google.com/maps?sll=44.915002,-0.244811&sspn=0.004657,0.011737&rq=1&ev=zi&q=44...&cbp=12,34.8,,0,3.91

While searching I found this site:

Les Plus Beaux Détours de France : 100 destinations à découvrir

that has a lot of sites. I'm not sure what overlap there is. No POI file though.

I'm afraid we didn't get to Ile de Re as we spent all day in La Rochelle. Carnaval was on.

On the way down to Bordeaux we really liked Royan, particularly the 1950s concrete cathedral. Unfortunately, they didn't put enough cover over the rebar and they now have a major concrete cancer problem. [/End Civil Engineer mode from my previous life before PCs]

http://maps.google.com/maps?f=q&source=s_q&hl=en&q=45.624753,-1.031985&aq=0&sll=45.624753,...0.0023,0.005869&z=18



http://quazen.com/arts/architecture/royan-breathtaking-church-of-the-fifties/

I learned how to create links to Google Maps at this site:

How to Share a Google Map and Link to it


Andrew Watson
Bordeaux, France
SpadesFlush
Ah, too bad about Il de Ré.

I couldn't wait for an AXE file of the Villages so I created one, see attached: Beaux Villages.axe

Thanks for all the other links; I will explore.
werdnanostaw
Could you please supply the list of Beaux Villages as a CSV or XLS file?

I want to go through the Villages coup de coeur list and remove the Beaux Villages so that we can differentiate between true BVs and the not so BVs.

Is there some way to extract the name and latitude / longitude info in Pushpins from an AXE file?

I have found that I can select all points in Data, Data set properties, Matching tab, select all, right click, Copy (but not CTRL+C).

I can then paste them into another AXE file.

I can also paste them into an Excel file but only the text is transferred, not the lat/lons.

Also I want to create a POI file for the Les Plus Beaux Détours de France.

How did you create the BV list? Did you get the list of town names off the BV site and then use some clever way to get the lat/lons for the towns in bulk or did you just brute force it and look each one up individually?


Andrew Watson
Bayonne, France
SpadesFlush
To create the pushpin set, I went through the list on the BV site and copied and pasted one-by-one. If one achieves a semi-robotic processing state, it can be accomplished reasonably quickly.

To get a CSV equivalent file, you might try to open the GPX file with Excel and then save it as CSV. I know you can see it line-by-line with the lat/long if you open it thusly.

Whatever you have in an AXE file can be exported to a GPX file and then opened with Excel and then everything is on display.

I don't really have time to create a 'perfect' CSV file right now, but if you are stuck I may be able to work on it over the weekend.
SpadesFlush
Between the two attached files I hope you can get what you want, Andrew.
Attached Files
File Type: csv BV.csv (59.3 KB)
File Type: gpx Le-Plus-Beaux-Villages-de-France.gpx (55.5 KB)
Larry
Quote:
If one achieves a semi-robotic processing state, it can be accomplished reasonably quickly.
That statement made me laugh so hard. Believe me, I know this semi-robotic state of which you speak.
werdnanostaw
Marvin

Can you please extract the digression into POI files for beautiful villages of France from Post #6 into a separate thread to make it easier for others to find.

The POI file for Plus Beaux Détours de France can be found here:

Balades et voyages moto : POI et utilitaires GPS

I will now compare the 3 files and remove duplicates from the Villages coup de coeur list.

I will post the final file here.


Andrew Watson
Pau, France (for the next 2 nights)
werdnanostaw
Villages coup de coeur (CC)
475 records
8 Duplicates
467 unique records
109 Beaux Villages included of 153 towns
8 Beaux Detours included of 96 towns, not 100 BD as advertised
1 town (Gourdon) in both BV and BD lists
350 CC sites not BV or BD

I used Excel to compare the lists.

To extract place names from records I used
=mid(cell containing text,where to start extracting,number of chars to extract) eg =mid(a1,18,100)

To remove leading, trailing and duplicate internal spaces I used

=trim(cell containing text) eg =trim(a1)

I converted all hyphens to spaces to remove problem of whether hyphens were or were not used using CTRL+H.

I assumed compilers of lists used the same spelling and accented characters as I had no way to easily check/fix any problems.

To check for duplicated entries I used

=A1=A2

To see if CC were in BV or BD lists I used

=vlookup(cell containing CC town,absolute link to range in BV or BD list containing town names,1,false) eg =vlookup(a1,$b$1:$b$150,1,false)

If town is in both lists town name is displayed. Otherwise, #NA is displayed.

I'll post this now in case it gets lost. I will now create CSV file for CC and post it when it is ready.


Andrew Watson
werdnanostaw
The website listed in this post allows you to batch convert a list of addresses or just place names to lat/lons.

http://www.laptopgpsworld.com/4412-batch-conversions-address-latitude-longitude-forward-geocoding#post38554



Andrew Watson
Pau, France
SpadesFlush
Andrew, I do not know which version of Excel you are using but Excel 2010 provides a neat little wizard that will help you parse CSV-type data in columns called appropriately enought "Convert Text to Columns Wizard."

This does all those "Trim" and other functions automatically. See below.

Search help

More on Office.com: downloads | images | templates

Split names by using the Convert Text to Columns Wizard

Show All
Hide All
Use the Convert Text to Columns Wizard to separate simple cell content, such as first names and last names, into different columns.
Full nameFirst name Last name Syed AbbasSyedAbbasMolly DempseyMollyDempseyLola JacobsenLolaJacobsenDiane MargheimDianeMargheim
Depending on the way your data is arranged, you can split the cell content based on a delimiter, such as a space or a character (such as a comma, a period, or a semicolon), or you can split it based on a specific column break location within your data.

What do you want to do?Split content based on a delimiter

Use this method if your names have a delimited format, such as "First_name Last_name" (where the space between First_name and Last_name is the delimiter) or "Last_name, First_name" (where the comma is the delimiter).
Split space-delimited content

To complete these steps, copy the following sample data to a blank worksheet.

How to copy an example
  1. Select the example in this article.
Important Do not select the row or column headers.


Selecting an example from Help
  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
After you copy the example to a blank worksheet, you can adapt it to suit your needs.

1234

ASyed AbbasMolly DempseyLola JacobsenDiane Margheim
  1. Select the range of data that you want to convert.
  2. On the Data tab, in the Data Tools group, click Text to Columns.
  1. In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.
  2. In Step 2, select the Space check box, and then clear the other check boxes under Delimiters.
The Data preview box shows the first and last names in two separate columns.

  1. Click Next.
  2. In Step 3, click a column in the Data preview box and then, under Column data format, click Text.
Repeat this step for each column in the Data preview box.
  1. If you want to insert the separated content into the columns next to the full name, click the icon to the right of the Destination box, and then select the cell next to the first name in the list (B2, in this example).


Important If you do not specify a new destination for the new columns, the split data will replace the original data.
  1. Click the icon to the right of the Convert Text to Columns Wizard.
  1. Click Finish.
Top of Page
Split character-delimited content

To complete these steps, copy the following sample data to a blank worksheet.

How to copy an example
  1. Select the example in this article.
Important Do not select the row or column headers.


Selecting an example from Help
  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
After you copy the example to a blank worksheet, you can adapt it to suit your needs.

1234

AAbercrombie,KimCavaglieri,GiorgioIto,ShuPhilips,Carol
  1. Select the range of data that you want to convert.
  2. On the Data tab, in the Data Tools group, click Text to Columns.
  1. In Step 1 of the Convert Text to Columns Wizard, click Delimited, and then click Next.
  2. In Step 2, under Delimiters, select the Comma check box.
The Data preview box displays the first names and last names in two separate lists.

  1. Click Next.
  2. In Step 3, click a column in the Data preview box and then, under Column data format, click Text.
Repeat this step for each column in the Data preview box.
  1. If you want to show the separated content in the columns next to the full name, click the icon to the right of the Destination box, and then select the cell next to the first name in the list (B2, in this example).


Important If you do not specify a new destination for the new columns, the divided data will replace the combined data.
  1. Click the icon to the right of the Convert Text to Columns Wizard.
  1. Click Finish.
Top of Page
Split cell content based on a column break

You can also customize how you want your data to be separated by specifying a fixed column break location. As an example, the data you are importing may have "padding" between columns.

For example, a column containing first names may allow up to 10 characters, and a column containing last names may allow up to 15 characters. Any unused characters for each column may be filled with spaces. So, in the text file that you are importing, the name Lola Jacobsen may appear as Lola (followed by 6 spaces) Jacobsen (followed by 7 spaces).
  1. Select the cell or range of cells that contains the data that you want to split.
  2. On the Data tab, in the Data Tools group, click Text to Columns.
  1. In Step 1 of the Convert Text to Columns Wizard, click Fixed Width, and then click Next.
  2. In the Data preview box, drag a line to indicate where you want the content to be divided.


Tip To delete a line, double-click it.
  1. Click Next.
  2. In Step 3, select a column in the Data preview box and then, under Column data format, click a format option.
Repeat this step for each column in the Data preview box.
  1. If you want to show the split content in the columns next to the full name, click the icon to the right of the Destination box, and then click the cell next to the first name in the list.


Important If you do not specify a new destination for the new columns, the divided data will replace the original data.
  1. Click the icon to the right of the Convert Text to Columns Wizard.
  1. Click Finish.
werdnanostaw
Thank you for this lesson on Text to columns.

I have used spreadsheets since 1984 so I was aware of it but I haven't used it in Excel 2010. I find that Excel 2003 is good enough for me.

At one stage in my career I was responsible for developing and running monthly, spreadsheets that extracted data from generic SAP reports for $1 billion per annum worth of projects. This was supposed to be a short term fix until better SAP reports could be developed. Of course, if you ever had anything to do with SAP you would know that using "short term" in relation to SAP development is a term that really doesn't apply. In the end SAP couldn't do what we wanted and web based reports were developed.

I found that formulae and macros were more repeatable than the Text to columns wizard.

Text to columns wouldn't work in the case of the POI data because the number of chars and spaces before the data I wanted to extract was variable [Village/Villages/Village NV/Villages NV]

I sorted by name and adjusted my formula by changing the second parameter as necessary to extract the required data.

The other formulae I listed had nothing to do with data extraction. They were used to tidy up the data.

Today we walked 4km around Lourdes:

http://4.bp.blogspot.com/_TWd4Wggn66A/TC7zG5zgnAI/AAAAAAAABp0/1b6Z6V7aG9Q/s1600/Lourdes+church.JPG

This blog has lots of good photos:

http://thebillats.blogspot.com/2009/10/holy-moly.html

We really liked the underground church:

http://sphotos.ak.fbcdn.net/hphotos-ak-snc3/hs041.snc3/12831_185286443587_501983587_2842504_2519197_n.jpg

http://www.eurotours.com.mt/kportal/content/images/Underground%20Basilica%20lourdes.jpg

Then we walked 10km to the Cirque de Gavarnie:

http://www.hotel-saintesuzanne-lourdes.com/images/cirque.jpg

http://www.ladepeche.fr/content/photo/biz/2007/10/29/gavarnie_zoom.jpg



Andrew Watson
Tarbes, France
Mr Earl
Many thanks to the OP and others who linked to POI sources for French locations. We're headed to France via London on May 27, and all this information is really going to enhance the trip.

I've been using Autoroute since 2001 (when I had a copy sent to me by a Libretto forum pal living in the UK, it being unobtanium in the US) but had no idea that all this info had been compiled and made available.

Thanks again!
© laptopgpsworld.com About