2

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.

Improve this question
8
  • 1
    How points are related to lines?
    – Akina
    Commented Jul 2, 2018 at 5:03
  • sorry about that, missed that when writing out the tables, the point table has a lineID field.
    – mike16889
    Commented Jul 2, 2018 at 5:19
  • And how you specify the order of points in the line?
    – Akina
    Commented Jul 2, 2018 at 6:35
  • 1
    Well, now you have common tree structure (stored in slightly strange structure). The tasks you want to solve seems do not need in spatial type. So I think converting the data store structure to the tree-oriented one may be safe. it has a tap or a water meter, how do I record this? The point where tap or water meter is posessed is a waterpipe tree node which not differs from a start/end/fork pipe node. So tap/start/fork/etc. is the pipe node attribute, not more.
    – Akina
    Commented Jul 2, 2018 at 7:18
  • 1
    what if a line is 200 or 1000 points long, as in the system i am building that is a posibility, also what happens if a user edits a line, do i delete all the points for that line and insert new ones, or do i order them some other way eg, an order field. removing and inserting new would be easier, then i dont have to keep track of point id's in the front end, but then if there are any data for a specific point, (tap or water meter or anything else) then the link would be lost.
    – mike16889
    Commented Jul 3, 2018 at 0:31

2 Answers 2

Reset to default
1

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.

Improve this answer
3
  • @Even what would i store in the POI table? a latLng or an offset from the start of a line? eg an int of 3 would point to the 3rd point in a LineString.
    – mike16889
    Commented Aug 20, 2018 at 1:54
  • you would store the lat long in the poi table.. not an offset.
    – Evan Carroll
    Commented Aug 20, 2018 at 3:17
  • ok, thanks for that, have decided to go with a lines table with linestrings (as apposed to each point in its own row) and a POI table with latLng's. i will just need to work out the mechanics of how to "attach" a poi to a line when editing the line.
    – mike16889
    Commented Aug 21, 2018 at 4:05
0

Do you absolutely need the points to build the line? Do these points are recorded from a GPS in the field?

Ultimately, you seem to be interested in the lines and not the points (except for SOME of them that has useful and complementary information). So people can build their lines in a program like QGIS (FOSS) then push it to the line table. The useful points (like a water meter) can be pushed to the point table.

Then you would either have a simple relationship constraint or a spatial relationship with something like ST_Overlaps. Datum and projection must be the same in both tables.

Improve this answer
2
  • the use of a 3rd party program for building the lines is not desirable. are you sugesting i store the lines in a lines table (potentialy as a linestring) and give it an id, then in a "points of interest" table store a lat/lng, pointID and lineID (as well as other info)? i would then need to sort out how to attach the point to the line when editing a line. (so the dont seporate) . maybe have them snap to points on the line or something.
    – mike16889
    Commented Jul 10, 2018 at 23:11
  • @mike16889 Yes. That's definitely 100% how it's done. You have a POI table, and a pipe/road/highway or whatever table.
    – Evan Carroll
    Commented Aug 19, 2018 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.