GTFS (CSV) data to SQLite3

[eViLrAcEr]

Hi,
I'm making an Android app called "Android GTFS reader"

The GTFS data is a set of comma separated values files. around 7 files.

I have no experience (maybe 3 days) in SQLite [or any DBMS for that matter] so I need someone experienced hotshot to write a set of queries that will import this data from a text file
to an sqlite database.

Then I'll include that code into my app (of course, credit where due)

you can find the app here: http://code.google.com/p/android-gtfs-reader/

GTFS Data sample here: http://googlehsrdocs.hamilton.ca/

Please have a look and contribute if you can.

Thank you

 

[eViLrAcEr]

^^ when you don't have a clue about the thread...REFRAIN FROM POSTING. READ THE RULES

 

[Rapchik Killer]

well im not an sql guru but i did learn sql in a couple of hours when i needed to for a project.. there are only a few queries anyways.. just goto w3schools.com and see the basic queries..

as for the gtfs format, i checked the sample data you gave.. you could just read the first line to understand what goes where next and use that specification to read or write data from the next line on and insert data into a table..

as for sql lite in java, i havent worked with java much but i think sql lite has two main commands in java, exec and query (you have to init sqllite db before that ofcourse, an example program online would help).. where query is to find entries in a table and exec to do an operation on a table or an entry.. im not sure about this though, you should google the sql lite api for android to get more on that..

anyways for your project, just make a db for the entire project.. make 9 different tables for the 9 different files.. and make the number of different coloumns as the first line of each files says.. then just insert data into each table's each column as you read through the file..

btw dont quote me on this, i am pretty sure i am right here but i havent worked much with either sql or java..

 

[eViLrAcEr]

i know about making 9 tables, already did the agency stuff and its details. I'll have a look at w3schools for this. I know what goes where but I've never actually done this, let alone done this for Android.

Plus I need it optimized coz it's a LOOOOT of data for a phone to convert

in other words, I don't know how to read text files and import that (string I guess?) to the sqlite db, keeping different columns for each element after the comma

 

[Rapchik Killer]

well android is pretty much normal java i think.. should only have a few different packages. the android developer page should have a lot of help on that..

for optimization, yea this is too much data for a phone.. what is the project btw? maybe i could give you some ideas on improving speed..

as for reading text files, just get the 3 open,read and close commands.. and loop through all the data in the file until you get the eof signal.. there must be functions to convert a string to integer if the value is only integer based (this saves space and improves speed as there will be a fewer bytes to go through)..

to insert get the data in different variables for the current line and use the query "INSERT INTO <table name here> VALUES('" + <field 1 here> +"', '" + <field 2 here> + "')" btw you need to have a string value between this ' and '.. and pass this string to exec to execute the instruction..

this project has a lot of different aspects to it, so if you are just starting out on android than maybe you should start a little smaller.. but if you really like this project and want to learn working on this then definitely take your team and go through with it.. it will take quite some time but your understanding of android development would improve a LOT..

 

[eViLrAcEr]

well android is pretty much normal java i think.. should only have a few different packages. the android developer page should have a lot of help on that..

for optimization, yea this is too much data for a phone.. what is the project btw? maybe i could give you some ideas on improving speed..

as for reading text files, just get the 3 open,read and close commands.. and loop through all the data in the file until you get the eof signal.. there must be functions to convert a string to integer if the value is only integer based (this saves space and improves speed as there will be a fewer bytes to go through)..

to insert get the data in different variables for the current line and use the query "INSERT INTO <table name here> VALUES('" + <field 1 here> +"', '" + <field 2 here> + "')" btw you need to have a string value between this ' and '.. and pass this string to exec to execute the instruction..

this project has a lot of different aspects to it, so if you are just starting out on android than maybe you should start a little smaller.. but if you really like this project and want to learn working on this then definitely take your team and go through with it.. it will take quite some time but your understanding of android development would improve a LOT..

this is the project: http://code.google.com/p/android-gtfs-reader/

And really, I just want to make this thing...not much about learning Android development than just making and using this in daily life and probably taking some knowledge out of it for future stuff.

I will look into what you have suggested, not any time soon though because I still need to populate the stop tables now. I'm making the whole program first (without the importing of real GTFS data), then afterwards I will look into this stuff. Mainly because I know that if I start searching for options about the GTFS data, I will lose interest before I can see any progress

If you will see the Status or Changes page, I'm making progress daily

 

[ahmed6]

you want to read the files through java then ummm parse then and create whatever the database or stuff?

 

[eViLrAcEr]

you want to read the files through java then ummm parse then and create whatever the database or stuff?

Would it matter that the file size is 30MB for the stop times of a mid-sized city? Plus keeping in mind that it's gonna be processed on a phone, would it be possible...if yes, roughly how much time do you think it would take?


@RK: even if I fail to import databases from the text files, the end result would still mean that I'll be able to edit the database manually and use the app for my own use so I don't mind the amount of time spent.

I had a nice look at the data files.

It's supposed to be like this:

The user goes to a list of transit services, clicks a services, goes to the list of stops for that service.
That list of stops will be grabbed from the data in stops.txt

Then, they click one of the stops and it should show the arrival time of the next bus relative to the time on the phone...to get the stop times, the program will use the "stop_id" to query the "stop_times" table (from stop_times.txt).

The tricky part starts here: the stop_times table holds "stop_id" (not primary), arrival/depart time (I'll probably only use arrival time since it's identical mostly) and "trip_id" (not primary in this table)

Now this trip_id, sadly is not in routes.txt (which is the bus list), so now it has to look up in the trips.txt's table to find which route_id are we talking about (trip_id is a primary key in trips table)

that's how we see which bus is coming on the matched time to the corresponding stop. Holy shit. I salute you Google Maps.


Btw Stop Times has a stupid 701719 rows (that's a mid-sized city) and trips.txt has 15142 rows, stops is 2393 and routes is just a measly 66. Got nothing to do with shapes as of yet.


Brain fart. I'm gonna go to sleep before my brain implodes. I'll think later about what happens when a user wants the route list.

Oh and did I mention that the user has to be able to search stops by stop_desc? Since, at least in my city... stop_id != the stop id on the bus stop.

Tomorrow...or probably hopefully on 4th November, I'll be making the Stop list activity...lol, laughing at myself at the amount of work I think it's gonna be.

---------- Post added at 01:03 AM ---------- Previous post was at 12:40 AM ----------

edit: looks like importing the csv files is the least of my worries. It's the actual working I have found some stuff, a bit of python which I might be able to use the concept of to help in converting

 

[deffury]

If your data aint that big, have you considered using a Vector or ArrayList or Hashmap?

BTW still have to look into your data (and possible code), will reply soon after I have made a copy of your project

---------- Post added at 02:56 AM ---------- Previous post was at 02:43 AM ----------

Read it aannnd why are you complicating things?

Why dont you just load the data onto a server, and use your app to download the data. That way you can upload updated data onto the app.

Just save the agency list on to the app, and as the user selects the agent, the app will display its transit routes and what not. The rest of the code can easily be coded onto the server. Just use a webservice for as link between server an app.

 

[eViLrAcEr]

If your data aint that big, have you considered using a Vector or ArrayList or Hashmap?

BTW still have to look into your data (and possible code), will reply soon after I have made a copy of your project

---------- Post added at 02:56 AM ---------- Previous post was at 02:43 AM ----------

Read it aannnd why are you complicating things?

Why dont you just load the data onto a server, and use your app to download the data. That way you can upload updated data onto the app.

Just save the agency list on to the app, and as the user selects the agent, the app will display its transit routes and what not. The rest of the code can easily be coded onto the server. Just use a webservice for as link between server an app.

Because that would defeat the purpose of the app man <_<

The purpose is to have OFFLINE BUS TIMES. SERIOUSLY!!!

And lol go ahead and copy it, the progress right now is pretty basic stuff
And have a look and the project's main page to see that agency list isn't fixed, this is gonna be usable for all services that use Google Maps for transit times.

edit: lol, added license block and text file , basically it means now you have to share the whole source code if you derive a program out of my code also you can't sell it mwahahhaha (it's been licensed since the day I made the project, although I included the text just now)

 

[deffury]

Because that would defeat the purpose of the app man <_<

Dunno how much experience you have with Android, but pretty much every one uses online features

The purpose is to have OFFLINE BUS TIMES. SERIOUSLY!!!

You said yourself that the data is enormous, and that its about 30MB, for now!!
Why would anyone want to download it. Its much more feasible to have an online solution, or as mentioned keep part of data (persistent data) on the apps db.

And lol go ahead and copy it, the progress right now is pretty basic stuff
And have a look and the project's main page to see that agency list isn't fixed, this is gonna be usable for all services that use Google Maps for transit times.

Great!! I can use this for the project I have been trying to work on!! YAY!!

edit: lol, added license block and text file , basically it means now you have to share the whole source code if you derive a program out of my code also you can't sell it mwahahhaha (it's been licensed since the day I made the project, although I included the text just now)

I just need the basic structure, not the entire app. Also if you are making an offline app, then I am going for the online one.

 

[eViLrAcEr]

Dunno how much experience you have with Android, but pretty much every one uses online features
You said yourself that the data is enormous, and that its about 30MB, for now!!
Why would anyone want to download it. Its much more feasible to have an online solution, or as mentioned keep part of data (persistent data) on the apps db.
Great!! I can use this for the project I have been trying to work on!! YAY!!
I just need the basic structure, not the entire app. Also if you are making an offline app, then I am going for the online one.

Don't forget to credit me

I'll gladly link you to the tutorials I followed and you can use them if you want though

I have a total experience of 4 days of coding in Android ...otherwise I've been doing a lot of other things with my phone. I know a lot of people use online features and this app does too but to put unnecessary load on some server is the last thing I want to do

Secondly, 30mb is really nothing compared to people who want offline MAPS on their phone.
And is it 30mb? For a mid-sized city, it's only 15 I think.
I will use some of the online features probably (map) but not at the moment. Maybe also use GTFS data exchange to download/update the internal db.

the thing is when you can't find a wifi signal or don't have a data plan, then how will you find the bus stop times? That's what the purpose of this app is.

@bold: what exactly are you making? add me on msn maybe, I want an Android dev on my list. Maybe we can collaborate and create an offline + online app?

I'm looking to switch to git...dunno why. nah maybe not.

 

[eViLrAcEr]

alright now i've run into another problem

due to my lack of sqlite knowledge. I can't figure it out by myself.

Now, I have one Agency table right...I need to create stop tables for all agencies in the agency table. And either:

1) name the stop tables according to agency names (or their ids in the agency table)
2) name each stop table uniquely and add a column named agency id to identify which agency they're for
3) one huge ass stop table with ALL of the agencies' stops and a column to identify which stop is for which agency

personally I think 3) would be quite taxing on the hardware but I dunno about this so please suggest and help

thanks

 

[deffury]

Do you have a pic of the relations you have made with your current system's DB?

 

[eViLrAcEr]

Do you have a pic of the relations you have made with your current system's DB?

I haven't made any relations yet. I've decided to put all the stops in one table with agency.stop_id being the index (thank's to #android-dev)

btw, I'm looking at options on database on memory card otherwise I might go for the online app myself.

If you're in Pakistan, don't even bother with it since it won't be worth your time

 

[eViLrAcEr]

yeah, you were right.

Importing 125MB of data from a text file is not viable on a device plus even as an sqlite db, it's gonna take a huge amount of space (as I found when i converted it on my pc).

For now, I'm just gonna give the user an option to add bus times I guess, will need to deviate from the gtfs columns that I was using sadly.

Maybe later Google will add transit directions/times to its map API, otherwise I might need to parse a whole page like this one:
http://maps.google.com/maps/place?q=type:transit_station:"STERLING+at+HADDON"

or convert it to XML and then parse the xml on device.

 

[Rapchik Killer]

for importing 125 mb of text.. import it into sqlite on your desktop, and get the sq lite file it makes ( im not sure but there should be some serialization option) and as that is going to be mainly simple text compress it and pack it with the app.. then extract on device and load the data as you wish..

 

[eViLrAcEr]

for importing 125 mb of text.. import it into sqlite on your desktop, and get the sq lite file it makes ( im not sure but there should be some serialization option) and as that is going to be mainly simple text compress it and pack it with the app.. then extract on device and load the data as you wish..

I made an sqlite db using some csv to sqlite converter and it was almost the same size plus more, i'll see if it can be compressed but afaik, an sqlite db is not simple text

Sent from my awesome GT-i5700 on FroYo

 

[deffury]

Dude what happened to your thingy??

 

[eViLrAcEr]

It's still going...although exams are slowing me down.

check the updates to see how much progress has been made.

Now that the database is on the sdcard, I can really go ahead with the offline database thing

I don't think it'll be good to convert the csv to sqlite on device, so might provide a separate pc app for that part or something. I'll look into online options later. But it each transit service data is ~150mb then I'll have to get a HUGE space to hold that data online for a lot of services which is even more unfeasible.