SQL LINESTRING() versus multiple rows

Question

I am storing GPS coordinates of route with LINESTRING() in one row. When I want to work with that data, I need to use another functions, i.e. X(), Y(), ASTEXT(), GEOMFROMTEXT(), etc.

I was thinking, if its not better to have another table, where one GPS coordinate will be stored in one row. So for each route I will have N rows in that table.

My question is, what is your opinion, if it will be faster, or its better to store GPS coordinates of the routes with LINESTRING() in one row per each route?

Answer 1

If I were you I would definitely create a new table with one lat/lon pair per row. This is the classic normalized database format.

Be sure you include a column allowing you to put the points of each route in the proper order. For example,

 route_id int   (fk to route table)
 point_seq int   (counts the points in the route 0,1,2, etc)
 lat float
 lon float

In this design proposal the table's primary key is the compound of (route_id, point_seq)

You may want to create a compound index (lat, lon, route_id, point_seq) to allow efficient coverage of location queries.

LINESTRING and friends are part of the OpenGIS extension to MySQL. There are some mapping systems using that extension. But, it has the key disadvantage (right now) that compound spatial/nonspatial indexes aren't available. That slows lots of spatial querying.