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

Brent Wood pcreso at pcreso.com
Tue Jul 19 03:11:11 PDT 2005



--- Ehud Shabtai <eshabtai at gmail.com> 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.

I have undertaken a similar exercise, this may not be the best solution, but it
worked for me. It does require storing your points twice, but the conversion
into line segments is data driven & managed automatically.

Store your points as points, with an attribute specifying a sequence (along the
line segment, either explicit, or implicit, such as a timestamp) and a suitable
line segment ID attribute.

I used a scheduled (cron) job to generate a line feature from this table for
each line segement ID, using makeline. From memory it was along the lines of:

drop/recreate line table (ie: empty it) line_id=primary key
add geometry attribute (line geometry type)
then:

for each line_id=$ID
  insert into <lines table> 
  select line_id, 
         makeline(select the_geom from <point table> 
                  where line_ID='$ID' 
                  order by seq_id)
         where line_id='$ID';


then create spatial index & run isvalid query to identify any possible errors,
ouput to logfile

So any changes in the underlying point table are picked up by the next rebuild
of the lines table.


Hope this helps...

  Brent Wood




More information about the postgis-users mailing list