Skip to main content
fixed table structure
Source Link
mike16889
  • 209
  • 1
  • 8

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.

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
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.

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.

Source Link
mike16889
  • 209
  • 1
  • 8

Storing point data for linestring in MySQL

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
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.