Database system

 

....

Are you looking for volunteers to possibly help convert to a database system? I used to program before a became a manager. smile Do you have a specification for the format you need the data in?

No promises, I'm pretty rusty, but I'm willing to take a shot at it.

ADMIN NOTE: This deserves its own topic, so I've moved it to a new thread.

volunteers

CXI wrote:

Are you looking for volunteers to possibly help convert to a database system? I used to program before a became a manager. smile Do you have a specification for the format you need the data in?

No promises, I'm pretty rusty, but I'm willing to take a shot at it.

Volunteers would be great. smile

For the first milestone, I'd like to get locations from existing files moved into the new database as cleanly as possible. This means breaking out each piece of info into separate fields and saving it in a file format that's easy to import.

I'll need to spec out the file structure for the cleaned up data and then modify the import utility to accept it.

For a future milestone, we'll go back and combine duplicates within and across the files in the database.

I'll get started on creating the import file spec. We'll also need to solidify a process for managing the cleanup before everyone rushes out and starts building import files.

Thanks,

JM

Database Size Restrictions

If you do not already know, MS Access has a size restriction. I found this out after developing my own access database for pictures. All of a sudden I couldn't put anymore pictures in it. I finally switched to My-SQL and it works much better.

--
Larry - Nuvi 680, Nuvi 1690, Nuvi 2797LMT

Database

We're running on MySQL. smile

MySQL You Say?

JM wrote:

We're running on MySQL. smile

I have a wee bit 'o experience in MySql; v4.0 - v4.1 & v5.1

they don't call me the dr for nothing.

thoughts...

JM wrote:

This means breaking out each piece of info into separate fields

JM (in another thread) wrote:

A lot of files have data mixed together, and it's hard to break out each piece of info into separate fields (e.g., address, city, state, zipcode, phone number, etc

I actually did some of this, with the Memphis Attractions POI file...
See: http://www.poi-factory.com/node/7863

I used GeePeeEx Editor to do it - and I'm wondering if its functionality could be used as the basis of a distributed tool.

I imported the original 4 column .csv file and used the phone number recognition algorithm to separate that out. The rest of the address ended up in its entirety in one long line (in what should have been just line 1 of the address).
However, by using the built-in Geocoder (which lets you specify the required precision - so no 'nonsense' results), I was able to get Yahoo! to return neatly formatted addresses that were dropped into the right 'boxes'.
For the problem entries, the inbuilt-in Google Map support gave lots of clues as to what the address should have been. The whole file took about 20 minutes. I saved it out as a .gpx file, using Garmin's address extensions - but the latest version allows multi-column csv output as well.

I'm sure that Geocoding technology can help solve this problem, but it would have to be distributed - otherwise the amount of traffic generated from a single source would require use of a commercial service.

Now the fly-in-the-ointment, is that GeePeeEx Editor is a commercial venture - and I'm nowhere near recouping the many man-months effort that have gone into it so far.

What I could do though, is add an "Upload to POI-Factory" button, to the Trial Version, with no limitations. That way, users could create or modify any POI files they wanted, but would only see the results of their labours at a separate stage, when they downloaded them from POI-Factory.

Of course, this is not entirely altruistic on my part - I would live in hope that one or two folk would be tempted to hit the "Buy Now" button wink

Also, I don't pretend that this approach would address every issue - some of the POI files have phone numbers embedded in the 'Name' fields and all sorts of other, similar issues. But if the file format is defined and the upload procedure agreed -(maybe the existing process is sufficient anyway?) - then this could be part of an arsenal of distributed tools?
I think distributing the problem, is the key to solving it.

JM also wrote:

I'll need to spec out the file structure for the cleaned up data

Not so keen on .gpx then wink ... the multi-column csv file ought to be viable alternative...

(Never has my finger hovered so long over the "Post Comment" button!).

Discuss!...

--
------------------------ Phil Hornby, Stockport, England ----------------------               http://GeePeeEx.com - Garmin POI Creation made easy           »      

Whee

Well I've fired up a Linux virtual machine and I've started on a Perl script to parse CSV files, starting with my own. It shouldn't be too much trouble unless the format variations in the CSV files get really wild. The goal would be to batch convert everything that's already created into new CSV files that have each data element in its own column (coords, name, street, state, zip, phone, notes, ???).

Format

Should you choose to adopt it, GeePeeEx Editor's export csv field headers are:-

Longitude,Latitude,Name,Description,Elevation,StreetAddress1,
StreetAddress2,City,State,PostCode,Country,Phone,Phone2,Phone3,
Email,Link,Speed,Proximity

which derived from their equivalent .gpx tags.
See also: http://geepeeex.googlepages.com/importingmulti-columncsvfile...

--
------------------------ Phil Hornby, Stockport, England ----------------------               http://GeePeeEx.com - Garmin POI Creation made easy           »      

.

Just checking to make sure you are still breathing, Phil. : )

field headers

Hornbyp wrote:

Should you choose to adopt it, GeePeeEx Editor's export csv field headers are:-

Longitude,Latitude,Name,Description,Elevation,
StreetAddress1,StreetAddress2,City,State,
PostCode,Country,Phone,Phone2,Phone3,
Email,Link,Speed,Proximity

which derived from their equivalent .gpx tags.
See also: http://geepeeex.googlepages.com/importingmulti-columncsvfiles

This is pretty close to the new database structure. Your format has a few extra fields, but we could either map those into notes or perhaps look at adding columns for them in the database.

In any case, this would be a good framework for organizing data for import.

I'll connect with you offline to discuss the "trial version" idea in your other post.

JM

Batch conversion

CXI wrote:

Well I've fired up a Linux virtual machine and I've started on a Perl script to parse CSV files, starting with my own. It shouldn't be too much trouble unless the format variations in the CSV files get really wild. The goal would be to batch convert everything that's already created into new CSV files that have each data element in its own column (coords, name, street, state, zip, phone, notes, ???).

It would be great to get a batch processing solution that handles the the typical stuff. The more complicated stuff might take some manual effort.

If you want to chat offline, send me a note via the contact form on my user profile.

JM

TriMet POI Files

I currently generate CSV files from SQLite databases for my POIs, so I could pretty easily write something to export to an arbitrary input spec, if you want something to test importing with.