1

I have created a map which allows the user to plot multiple markers with the intention on storing them in a database table. The original plan was to store multiple rows per one map like so:

------------------------------------
| mapentryid | mapid | Long | Lat  | 
------------------------------------
| 1          | 1     | X.XX | X.XX |
| 2          | 1     | X.XX | X.XX |
| 3          | 1     | X.XX | X.XX |
| 4          | 2     | X.XX | X.XX |
| 5          | 2     | X.XX | X.XX |
| 6          | 2     | X.XX | X.XX |
| 7          | 2     | X.XX | X.XX |
------------------------------------

But I have since found out that you are able to store multilinestring's in MySQL which sounds perfect for what I want (I think?)

I can insert the data correctly using the following SQL query format:

INSERT INTO table (LatLng)
VALUES (
         MultiLineString(
            LineString(Point(x),Point(y)),
            LineString(Point(x),Point(y)),LineString(Point(x),Point(y))
       )

This adds the multilinestring OK, although they actually come up as the following:

phpmyadmin screenshot

The question is, is the above OK? If so, how can I convert this data back into something I am able to display on Google Maps?

Prisoner
  • 27,391
  • 11
  • 73
  • 102
  • Why is it better for you to store the coordinates as mulit-line strings? What if you want to be able to retrieve for instance which users that have stored coordinates near each other, or whatever? – Stefan H Singer Jun 20 '11 at 10:30
  • I suggest you use the following definitions of the fields: `latitude decimal(18,7) DEFAULT NULL` and `longitude decimal(18,7) DEFAULT NULL` – JellyBelly Jun 20 '11 at 10:30
  • 1
    @Stefan, I just assumed that I should make use of MySQL's built in functions/datatypes? – Prisoner Jun 20 '11 at 10:33
  • @Jelly, I did think that initially but then found these different datatypes which I thought may be interesting to use and perhaps make my life easier.. – Prisoner Jun 20 '11 at 10:33
  • Prisoner: It'd probably be much easier to just do selects on rows, than on nested data within strings. – Stefan H Singer Jun 20 '11 at 10:36
  • possible duplicate of [What is the ideal data type to use when storing latitude / longitudes in a MySQL database?](http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-to-use-when-storing-latitude-longitudes-in-a-mysql) – Gajus Jun 15 '15 at 19:27

1 Answers1

3

Why not use POINT data?

INSERT INTO geoTable (mapentryid, mapid, coord) VALUES (
    1, 1, GeomFromText('POINT(15 20)')
);

I always use the 'Well-Known Text (WKT) Format' for geospatial enabled queries in MySQL, they are the most compatible with other systems out there.

To query the stored values:

SELECT mapentryid, mapid, X(coord), Y(coord) FROM geoTable;
Jacco
  • 23,534
  • 17
  • 88
  • 105