Hi guys, so I was struggling to create a JSON file that is compatible with buzztouch using my existing SQL database. So I figured out a way to export data from my database into a .csv and created an excel file that takes the data and converts it into a JSON file compatible with buzztouch. Its not the most innovative approach but something that is usable and i've tried it out...works flawlessly. Your excel file needs to have the itemid, name, lat and long and if you want the subtitle (i left that blank). You then go over to the next tab, drag down the first column and the JSON data auto populates (The stuff between the square brackets). You copy and paste that into a text file and load it to your server. I cant seem to upload on here but if anybody needs help/directions on using it i'd be happy to send it to you and explain how to use it. Just offering some help.

I'd love to see what you've got. How many locations are you mapping? I've got an app that has a relatively large database of points and I'm having some difficulty pulling the data properly. Funny, I do this for quizzes all the time without problem, but the map file has given me some difficulties. Would you mind emailing me (jeff at jc-evans.com)? Thanks

No problem at all. I have 60 locations right now that I was able to put into the app within a few minutes. When I get home I would be happy to send it to you. I'll also do my best to post up a quick tutorial.

MultiMap here we go. Just wanted to follow the post.

If I were looking to do this, I might look at pipes.yahoo.com as a place to build a script that automatically fed out a database as Json or rss I've been pretty impressed with what can get done using pipes, and I kind of hope/assume yahoo won't disappear.

Great idea on pipes. I use pipes in a number of apps and it's been a great tool.

Has anyone had experience pulling in a large database -- say 20,0000 items? Trying to figure out the best way to tackle this. There are roughly 20,000 golf courses in the US and I've got a geocoded database I can draw from. I'd like to be able for a person to open their app and have a map of all the courses within a certain radius. The problem I'm running into is the size of the database makes loading difficult. Is there a way to store it locally in the app and pull from the database? Or does anyone have a suggestion for how to parse the app into smaller increments. I'd rather have the map function fully automatic rather than having the user pick a city or state first. Appreciate any thoughts/suggestions on this. Thanks.

MgoBlue, You can probably do this with pipes, I've got some ideas about how. Yahoo local module would help with using geocoding and you could pass users lat + long into pipe to pull nearby locations etc. Robertgtichy , g mail

MgoBlue, I've emailed a zip file to you. Please let me know you received it. In there i did up a quick instructions.txt, sample.txt and the excel file. Just to ensure I wasn't smoking something when I did this, I took some sample data from work and was able to successfully port over 1000 locations onto the iphone app. The only limit to how many you can do is the line limit in excel. If you have more than 65000 points then all you would need to do is populate this multiple times and append the result to the sample txt file.

Sats4eva, if you don't mind sharing I'd appreciate a copy. Looking at similar type App (not golf). Fred

Fred, np. what address can i send to?

Hi Sats4eva, please count me in for a copy. Love to see that example and try converting it to my own needs. Please send copy to info at parkstadmedia dot nl Thanks in advance, Danny

Hi Danny, just sent it to you.

20,000 locations, I can see how this could get slow, especially on a slow server. But, on well indexed database with a relatively good processor you shouldn't have any trouble doing this. I've done the exact same thing with more than 100,000 locations. My approach: a) Grab device location from URL (as demonstrated in a few samples / posts) b) Do a FAST sql query to load the courses. It's sometimes best to do NO filtering on these, the table will 'dump' very fast if you don't have any complicated WHERE / JOIN or other expensive (and almost never optimized) conditions c) use PHP (middle layer) to do the filtering around the 'point' in the radius. Lots and lots of folks get bogged down (and bog down their db) by trying to get the data filtered in the query. Sure you can do it but it's normally much faster to do this with PHP than with SQL. I realize this goes against common wisdom and for sure you can optimize and tune your db to run your query faster but most folks don't understand large datasets and performance. So, PHP comes in handy for this kinda thing and does wonders. My two cents, sounds like a good challenge. If you want to send me a CSV or SQL dump with the locations I can have a look and offer some tips?

Hi Sats, File recieved. Much thanks and appreciation for this. Best Regards, Danny

@Sats4eva, I'd also like a copy if you would! skidz at ureach dot com. Thanks in advance! Thanks for the input David, I didn't think about it like that! That'll be another approach to try!

Fred at Myskylla.com Fred

Fred/Dkeller, sent it to you. rgtichy, I like the pipes idea. Do you have a pre-built pipe that you can share? I'm guessing you'd have to fetch the data in a CSV and then filter out what you need and convert to a JSON? Also does this mean you'd need to have a job to convert SQL to CSV automatically (assuming I wanted this updated daily). Also are you aware if the JSON output from pipes is compatible to buzztouch? I used a JSON from another source that was not compatible.

Hi Sats, just wanted to let you/everyone know that the excel sheet doesn't work with OpenOffice and LibreOffice. Don't have MS-Office, just these 2. Formula on sheet1 gives output on every row: #VALUE! (too bad) Best Regards, Danny

Is that because OpenOffice doesn't support certain formulas?

Hi Sats, I guess so or OpenOffice has a different system in creating formulas. Don't really know. Best Regards, Danny

Sats4eva, regarding Yahoo Pipes; I don't have a pipe like the one we're talking about here, but if someone started building one at Yahoo, I could help with it, to the best of my abilities. (I'm getting decent at it.) But also on the developer help boards in pipes, there is a user named hapdaniel (who is in the UK, so timezones affect his timeliness for me at least) that can make pipes dance! I mean it, he seemingly can turn a wordpress blog into a youtube video using pipes.

I would guess that a pipes solution might involve using YQL (Yahoo Query Language) against the database, to subselect a smaller set of golf courses (on zipcode, e.g.), then geocode those in pipes and then filter again in pipes...

Sats4eva if you could also please send me a copy! Looks like you are most popular on this post !! =)!. jonj.24hour(at)hotmail. Thanks!

need to fallow this post lol

John, just sent it to you. Danny, if you send me your data in a CSV in the format that i've suggested I can convert it for you as a test to see how it works.

Sats4eva thanks you sir! Jon

Hi Sats, I managed to get your example working. Looks nice and thanks for sharing. Bad thing is that I can't edit your file to suit my needs (because of the OpenOffice problem). It gives me an idea how to do it and maybe convert it (eventually) to a php-script. Best Regards, Danny

Sats4eva, Could you please send to paddy2a(at)hotmail com ? Very interested, thanks

hello there, this seems just what i need for my app. in the first edition i manually filled them in and it took me some time ;-) could you send me this tutorial Chris at app-o-theker.nl thanks in advance