How do I save and load entire sql tables

Question

I have a maps app on android that tracks your route via location data and stores the LatLng points in an SQL database and draws a line on the map with Polyline.

I would like to go further with that and allow the user to be able to save the route and load past routes. Is there a way that I can store/delete the entire table data into a file so that a fresh route can be created, then be able to load that file into the database again if the user chooses?

Looking at the Geometry Data type. Introduced in SQL Server 2012. — Peter_James, Dec 18 '15 at 12:18

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

1: use objects to store your datas, and keep them as a Collection for example (Set, Map, ...)

2: use serialization, it is direct

What does Serializable mean?

edited May 23 '17 at 10:28

Community

1
1

answered Dec 18 '15 at 12:16

guillaume girod-vitouchkina

3,061
1
10
26

KoenVE · Answer 2 · 2015-12-18T12:21:23.427

0

You could try making a Route class, containing all information you want to store in your database, like so:

public class Coordinate {
    double longitude;
    double latitude;

    public Coordinate(double x, double y){
    this.longitude = x;
    this.latitude = y;
    }
}

public class Route {
    private List<Coordinate> coordinates;

    public Route() {
        coordinates = new ArrayList<>();
    }
}

Using Google Gson, you can convert routes to a Json file. You can store these files in the local storage, and use them when the user wants to use a past route.

edited Dec 18 '15 at 12:21

answered Dec 18 '15 at 12:18

KoenVE

301
2
11

Be careful not to fall off the edge of the flat Earth. – Peter_James Dec 18 '15 at 12:19
My example could've been clearer indeed, I edited it and made it more clear (obviously, I meant long and lat) – KoenVE Dec 18 '15 at 12:22
Okay, I will check this out. So by putting everything into an ArrayList is there even a need for a database at all? – Jared Allen Dec 18 '15 at 12:30
@JaredAllen: that depends what your intentions are: if you want to save all their routes in the cloud, a database might be the easiest option. If it's just so the user can save routes, saving them as a json file on the internal/external storage works perfect too – KoenVE Dec 18 '15 at 12:32

Peter_James · Answer 3 · 2015-12-18T12:30:26.290

0

A fantastic Guide to get you started:

http://developer.android.com/training/location/receive-location-updates.html

A tutorial, probably exactly what you are looking for:

http://javapapers.com/android/draw-path-on-google-maps-android-api/

Further Reading:

I would look at Google's Android Location API:

https://developers.google.com/android/reference/com/google/android/gms/location/package-summary

It also has a listener inbuilt, so that when the users location changes it will update you automatically.

You could then keep a history, like you suggest in your Database and when required. Then when required using the API to rebuild or monitor the users current path.

Location Listener: https://developers.google.com/android/reference/com/google/android/gms/location/LocationListener

Your Data, like you also suggest should translate to/from the API stored 'Location' format of: http://developer.android.com/reference/android/location/Location.html

edited Dec 18 '15 at 12:30

answered Dec 18 '15 at 12:25

Peter_James

647
4
14

Yeah i've already done all that stuff. I have successfully drawn a route based on location and stored all the Lat and Lng variables to a SQL database. What I want to do now is be able to Take that whole route in the database and store it to clear up the table for new routes, and be able to load it back into the map as well. – Jared Allen Dec 18 '15 at 12:38
@JaredAllen So you just need to clear the current map and rebuild the current map? Do you have an API Call for these yet? Are you just unsure which objects to call/refresh? – Peter_James Dec 18 '15 at 12:41
I know how to rebuild a route from the database on the map using the Cursor method. I just want to know how to save/export an entire table of data, and be able to load/import that table, so that I have the data to put on the map. – Jared Allen Dec 18 '15 at 12:47
@JaredAllen The Database table would store a Route ID. So no data is imported/exported. You can then query the database using this route ID, build up an array and then pass this back to the view to rebuild the route. Beyond this I am not sure we can help you progress. – Peter_James Dec 18 '15 at 12:51
I think I might not be seeing this right. I understand that the rows in the database has _id's. The columns are _ID, Lat, and Lng. So how do I make it so the whole table has an ID like you say? And how would I be able to load different tables based on their _id's? – Jared Allen Dec 18 '15 at 13:11
@JaredAllen You can approach this two ways. Create a new table with columns ID (PK), RouteID, _ID (From your current table). Then every time a new location is added to this route - Update this table. Alternatively, create a new column in that table called 'RouteID'. – Peter_James Dec 18 '15 at 13:14
Okay, I will try this as soon as I get home from work. This makes sense now! Thanks. – Jared Allen Dec 18 '15 at 13:17
@JaredAllen No problem. The query to then get the route information, to load back in the map would be. SELECT * FROM routes WHERE RouteID = 4 – Peter_James Dec 18 '15 at 13:17

score 0 · Answer 4 · answered Dec 18 '15 at 13:55

0

Well lets think clever here.
A route contains many possible locations.So a good idea is to create two sql tables.One table will be for the locations and one for routes
So you will make a search and you will find all locations that exist in a route.It depends on what kind of implementation you will make for your database.

answered Dec 18 '15 at 13:55

PL13

393
2
5
13

So essentially you're saying, make a table of routes, each of which contains a table of location points? Can you nest tables like that though? – Jared Allen Dec 18 '15 at 14:19
As I said it depends on what kind of implementation you will make.In this case you will need a Junction Table....Junction table is a table which contains a pair of values.For more info read this article: https://en.wikipedia.org/wiki/Junction_table . In your Junction table you will have records of this form: – PL13 Dec 18 '15 at 14:58
So I've been reading about it and I have a good idea of how it's gonna work. Thanks for showing me something new and powerful. I'll work on it when I get home from work and I'll let you know how it goes! – Jared Allen Dec 18 '15 at 15:59

How do I save and load entire sql tables

4 Answers4