Timeline for Storing point data for linestring in MySQL
Current License: CC BY-SA 4.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 21, 2018 at 4:05 | vote | accept | mike16889 | ||
| Aug 19, 2018 at 19:12 | answer | added | Evan Carroll | timeline score: 1 | |
| Jul 9, 2018 at 18:45 | answer | added | TurboGraphxBeige | timeline score: 0 | |
| Jul 5, 2018 at 15:39 | comment | added | TurboGraphxBeige | 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. | |
| Jul 3, 2018 at 0:31 | comment | added | mike16889 | 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. | |
| Jul 3, 2018 at 0:23 | comment | added | Michael - sqlbot |
then I am left with no way to reference a specific point in a line. ST_PointN(linestring,index) is the function to reference/extract the nth point in a linestring. A multilinestring is an array of linestrings, so not directly helpful with a fork, I don't think. Fundamentally, though, there's nothing wrong with a line of 20 points requiring 20 rows in a table.
|
|
| Jul 2, 2018 at 7:18 | comment | added | Akina | 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. | |
| Jul 2, 2018 at 7:02 | comment | added | mike16889 | ordered by pointID, so id 1 will be before id 2 and so on. not a great system but i had to throw it together in a hurry. never got around to implementing the ability to modify a line, that's one of the things i would like to implement when re-writing the system. | |
| Jul 2, 2018 at 6:35 | comment | added | Akina | And how you specify the order of points in the line? | |
| Jul 2, 2018 at 5:19 | comment | added | mike16889 | sorry about that, missed that when writing out the tables, the point table has a lineID field. | |
| Jul 2, 2018 at 5:18 | history | edited | mike16889 | CC BY-SA 4.0 |
fixed table structure
|
| Jul 2, 2018 at 5:03 | comment | added | Akina | How points are related to lines? | |
| Jul 2, 2018 at 1:16 | history | asked | mike16889 | CC BY-SA 4.0 |