create your own POIs from electronic yellow pages

Discussion in 'System Configuration and Customization' started by edonoho, Feb 24, 2008.

  1. edonoho

    edonoho

    Joined:
    Feb 24, 2008
    Messages:
    2
    Likes Received:
    0
    Sharing the wealth here... Create your own dunkin donuts POIs (or whatever). ;)

    I discovered that Street Atlas 2008 (plus edition) has a 4gb phone book of the whole united states (whate pages and yellow pages)... and you can "trick" it into geocoding the addresses (yielding longitude/latitude) into a Microsoft Access MDB file... You do this by using the export to XDATA feature. This export (takes a few minutes) actually geocodes the addresses and writes it out as a MDB file ... it has a DDS extension (but it's really an Access MDB)... and the password is "SELECT USER".

    One catch... SA only allows 1000 records in an export at a time. So, this can be a bit painful if you have more than 1000 hits (then you'll have to export by region, etc)... but, hey... it's pretty cool!

    So, once you have your MDB file, you can use Access to create a CSV file ready for import into a POI editor.

    I have a SQL query at my blog to help with the Access export... http://ericstupidcrap.blogspot.com/

    Geocoding for free ... nearly... SA2008 is like $60... pretty reasonable...
     
    edonoho, Feb 24, 2008
    #1
    1. Advertisements

  2. edonoho

    Birdman

    Joined:
    Dec 9, 2007
    Messages:
    778
    Likes Received:
    87
    Location:
    Jericho, VT
    TomTom Model(s):
    TomTom Go 920
    Sounds like a good method, but doesn't this procedure also require a user to own MS Access as well?
     
    Birdman, Feb 24, 2008
    #2
    1. Advertisements

  3. edonoho

    edonoho

    Joined:
    Feb 24, 2008
    Messages:
    2
    Likes Received:
    0
    No, you can use a simple VBScript to extract the data to the console (or output to a file):

    paste the following script into a VBS file (example, x.vbs) and run it with CSCRIPT.EXE from a command prompt (1st argument is the path to the exported XDATA dds file)

    cscript.exe x.vbs "c:\programdata\delorme docs\datasets\47129-m.dds" >poi.csv

    Note: change q to chr(34) (eg, q=chr(34)) if you want each elemement surrounded by a quote, and change qcq to whatever delemiter you want (eg, qcq = q & "," & q for comma separated)

    Code:
        Dim cn, rs, sout, q, qcq, xdata
        q = ""
        qcq = q & vbtab & q    
        xdata = WScript.Arguments.Item(0)
    
        Set cn = CreateObject("ADODB.Connection")
        cn.CursorLocation = 3
    
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xdata & ";Jet OLEDB:Database Password=SELECT USER;"
        
        Set rs = CreateObject("ADODB.Recordset")
        
        rs.Open "select q.id, q.listing, q.address, q.city, q.state, q.zip, q.phone, q.yellow_page_heading, p.geolat /  -8388608.07678 as lat, p.geolon / 8388607.9284564 as lon  from  (select a.* from xusertable a inner join ( select min(id) as i, ucase(phone) as ph, address, zip from xusertable where score >= 97 group by ucase(phone), address, zip ) b on a.id = b.i ) q inner join point p on q.id = p.pointid", cn, 3, 4
        
        If Not (rs.EOF And rs.BOF) Then
            Do Until rs.EOF
    
                sout = q & rs.fields("id").value & qcq & rs.fields("listing").value & qcq & rs.fields("address").value & qcq & rs.fields("city").value & qcq & rs.fields("State").value & qcq & rs.fields("zip").value & qcq & rs.fields("phone").value & qcq & rs.fields("yellow_page_heading").value & qcq & rs.fields("lat").value & qcq & rs.fields("lon").value & q
                
                wscript.echo sout
                rs.MoveNext
            Loop
        End If
        rs.Close
        cn.Close
        
        Set rs = Nothing
        Set cn = Nothing
    
     
    edonoho, Feb 24, 2008
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.