create your own POIs from electronic yellow pages

[edonoho]

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...

 

[Birdman]

Sounds like a good method, but doesn't this procedure also require a user to own MS Access as well?

 

[edonoho]

Sounds like a good method, but doesn't this procedure also require a user to own MS Access as well?

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)

    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