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.