MySQL Spatial – Storing Point Data for Linestring

MySQLspatial

Ok, i am have a need to store data on SOME points of a poly line but not others.

The current way I an doing it is i have 3 tables

line:
lineID INT AUTO INCREMENT
lineType INT
forkFrom INT

linePoint:
pointID INT AUTO INCREMENT
lineID INT
lat DECIMAL
lng DECIMAL

pointMarker:
pointID INT
someDataFields...

but a line, lets say it has 20 points, will take up 20 rows in a table.

I would like to have a line table with a linestring field, removing the need for the linePoints table. but then I am left with no way to reference a specific point in a line.

Example: the line is a water pipe, it has a lot of points as it go's around corners and such, on some of these points, it has a tap or a water meter, how do I record this?

do I use the lat/lng as an index and then in my code somehow check for orphaned points if the line is modified? or is there a better way to do this?

also some lines are "forks" or "branches" from a main one, for this I am currently using the "forkFrom" column to store the ID of the point the "fork" would start from. how would i do this with a lineString? is that what a multiLineString is for?

I am having some problems with my front end code that deals with lines so now is a good time to change the the way the data is stored, as I will likely have to re write a large chunk of code that deals with it anyway. may as well write a few more lines and get the database side of things sorted as well, instead of fixing whats broken now, then throwing everything out the window when I end up changing the database.

Best Answer

GIS

Use GIS and stop doing all this madness.

MySQL Provides

They're easy to use and any program can output Well-known text or Well-known binary to create the lines. They're also indexable, more compact, and stored with SRID.

also some lines are "forks" or "branches" from a main one, for this I am currently using the "forkFrom" column to store the ID of the point the "fork" would start from. how would i do this with a lineString? is that what a multiLineString is for?

A multiLineString can hold forks, yes. Or, you can just do it with another linestring entry in the table. It just depends on what you want to do. When you query it do you want to know what "fork" is returned (separate entries in the table make this easier and faster), or do you only care the pipe somewhere regardless of forks is returned.

Also obligatory statement if you're going to use GIS, you'll probably want PostGIS at some point.

Related Question