0

Could someone suggest the best format to store geolocation coordinates into a websql database? Would it be better to grab the values from an array and store in the format: (52.8165972, -2.1174389000000247),(52.8165972, -2.1174389000000247),(52.8165972, -2.1174389000000247), with each bracket representing a set of coordinates (lat,lng), or would it be better to separate lat and lng values into their own separate columns?

Please bear in mind I will be using the coordinates stored within this database to plot a polyline onto Google maps. The reason I currently have the structure stated above, is because Google maps expects the format of the path values to be (lat, lng).

The current concern is outputting from the database column, all the values in that format, so for example if it's possible to output as (52.8165972, -2.1174389000000247), it may be possible to work with it more easily.

Any advice would be highly appreciated!

EDIT: Additional information about the whole project

I am building an application that is intended to track a users location whilst on walks. The mechanics behind the tracking and drawing a polyline in real-time all work perfectly. Upon "finishing" the walk, the user is taken to a second screen where additional details can be added such as walk titles, descriptions. Information such as the polyline path is output from an array onto the map so that the user can see it. Upon "saving" from this page, all the data is collected both from the arrays for that session and any information entered on that page and stored into its own auto incremental row in the table.

There is another page where the users can view all the walks they have undertaken, this will list out all the individual walk details, and also display a map with that particular walk's polyline added.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Matt Meadows
  • 147
  • 2
  • 6
  • 16

1 Answers1

2

You have a range of options.

You could separate them out into individual points (lat/lng pairs) and store each in its own column but this would require you to create as many columns as there are points in your longest polyline. They would be more difficult to query like this.

You could split each pair up into a lat column and a lng column for each point this would be like the previous option but with twice as many columns.

Or you could store the whole polyline in a single column.

I would recommend that last option as it results in a database structure that is more predictable. It is less easy to query to lat/lng data but there are ways around this problem if you can't ignore it.

If you feel the need to query the database by coordinates I suggest you create a few of extra columns that describe a bounding box inside which the polyline fits and/or its centre point.

These would be stored as decimals. See this post for details of the precision you feel is required for your application.

How accurately should I store latitude and longitude?

This will allow you to search for lines or points within a rough area which can be further refined using javascript.

Salomon Zhang
  • 1,553
  • 3
  • 23
  • 41
Bob
  • 523
  • 4
  • 11
  • I am currently storing the whole polyline into a single column as (52.8165972, -2.1174389000000247),(52.8165972, -2.1174389000000247),(52.8165972, -2.1174389000000247) for example, this is using an array thats built using `googleLatLng.push(new google.maps.LatLng(lat, lng));` which will basically store the coordinates into a format that the polyline can use (i think) – Matt Meadows Apr 15 '15 at 17:24
  • If i stored it into the database how i currently have it as stated in the above comment, would it be possible to pull from the database in a certain format? Seeing as i need it to break off after every comma after each pair – Matt Meadows Apr 15 '15 at 17:28
  • That way will certainly save you time writing code that splits up and reassembles the polyline. If you want to search the database for polylines near a given coordinate you will want to at least calculate the approximate centre of each line (calculating the average lat and average lng for the line should work) and store each of these centre lat and centre lng points in its own decimal column. – Bob Apr 15 '15 at 17:33
  • The way I would handle it is to select the whole polyline from the database and use javascript to break in up into points. Any work that can be of loaded from the serve to the clients web browser is usually a benefit. – Bob Apr 15 '15 at 17:36
  • I dont suppose you know of the best functions available in javascript to break it up after each pair of coordinates? – Matt Meadows Apr 15 '15 at 17:39
  • Given a string contain the polyline you describe above in a variable called polystring use `polystring.split("),(")` this will return an array of strings that are the points. All the brackets from the middle of the polyline will be stripped away but be warned that the first will still start with`(` and the last will end with `)` you might want to use `polyline.substr` to trim these of before you split them. You can use split again on each point to break them down into lat and lng if you need. – Bob Apr 15 '15 at 17:52
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/75320/discussion-between-bob-and-matt-meadows). – Bob Apr 15 '15 at 17:55