[postgis-users] Need some help with storing line segments

[Stephen Woodbridge]

Ehud Shabtai wrote:
> On 7/19/05, Stephen Woodbridge <woodbri at swoodbridge.com> wrote:
> 
>>I'm not sure you can do this in postGIS, but the idea would be to try
>>something like:
>>
>>Table points: pid, point_geom, attributes, ...
>>Table lines: pid_from, pid_to, attributes, ...
>>View: select makeline(
>>          (select point_geom from points a, lines b where
>>a.pid=b.pid_from) as p1),
>>          (select point_geom from points a, lines b where
>>a.pid=b.pid_to) as p2)) as line_geom, attributes from lines;
> 
> 
> This solution seems to require creating a line for every two points.
> My lines may have any number of points.

You are right but the idea still works, there should be a function that 
takes a list of points and makes a line from it. Assuming that a point 
can only belong to a single line then you need a track_id for all the 
points and a sequence field to order them. Then something like 
makeline((select point_geom from points where track_id=<track_id> order 
by seq) as points_collection)) should do the creation.

>>If the view trick doesn't work then you might want to add the line_geom
>>directly to the lines table and use a trigger on the points table to
>>update the line_geom if the points_geom gets changes based on the pid field.
> 
> 
> This is a great idea!
> 
> The only question is how can I change a specific point of a line (as
> it may contain many points)? As suggested earlier, I could use type-m
> points in the line, M being the id of the point. Can I select a
> specific point inside a line using the M value? Can I change a point
> (update its coordinates) inside a line without creating the whole line
> and replacing the old one?

You don't change the point, you just recreate the line as above. So if a 
point changes, the trigger just recreates it using the new data. The 
track_id tells you what line needs to be recreated.

-Steve

> Thanks a lot for the help,
> 
> Ehud.
> 
> 
>>Ehud Shabtai wrote:
>>
>>>Hi,
>>>
>>>I'm new to postgis and this is my first usage of it. I read the
>>>documentation, but I could not find a way to store the data I want (in
>>>the way I want it).
>>>
>>>Here's the story:
>>>
>>>I got some GPS tracks which I want to store in postgis. Before I store
>>>them, I do some processing and build line segments from the separated
>>>GPS points. Now, I'd like to store these lines in postgis, but I'd
>>>also like to keep attributes for each node of the line (node id, time,
>>>direction for next node, etc').
>>>
>>>The only way I see it is to store each point twice, first as a point
>>>with some attributes and then as a line. The problem with this
>>>approach is that I'd like to be able to update the coordinates of a
>>>specific point, which will result in searching for the line consisting
>>>this point and then updating the line.
>>>
>>>Is there any better approach to sore my data?
>>>Can I update a specific point of a line in postgis, or the only way is
>>>to get all the points of a line and create a new one?
>>>
>>>Thanks,
>>>
>>>Ehud.
>>>_______________________________________________
>>>postgis-users mailing list
>>>postgis-users at postgis.refractions.net
>>>http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>
>>
>