Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I am attempting to build an elevation profile using PostGIS-PostgreSQL only. I have imported GPX data in my PostGis database, so now I have a table full of the GPS coordinates of the track and the timestamp of every single GPS point of the track. Now I would like to build a second table that contains, in a single row:

  1. the track id
  2. an array of floats representing the incremental distance from the starting point
  3. an array of floats representing the incremental time from the starting point

Is it possible to do it in a single SQL stored procedure?

Thanks,

Andrea

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

Here's a start (not really tested...) First two assumptions:

  • I guess your tracks table is a PostGIS spatial table, with a geom column? (If not you'll have to run SELECT AddGeometryColumn(...) to set it up using the Lon/Lat values)
  • When you say "incremental distance" I'm assuming you mean accumulated distance?

I made two test tables: tracks for the points and accum for the accumulated distances and times

geodata=# \d ms.tracks
                                        Table "ms.tracks"
    Column    |            Type             |                      Modifiers                      
--------------+-----------------------------+-----------------------------------------------------
 pk           | integer                     | not null default nextval('tracks_pk_seq'::regclass)
 trk_id       | integer                     | 
 lon          | double precision            | 
 lat          | double precision            | 
 geom         | geometry                    | 
 gps_timestmp | timestamp without time zone | 
Indexes:
    "tracks_pkey" PRIMARY KEY, btree (pk)
Check constraints:
    "enforce_dims_geom" CHECK (st_ndims(geom) = 2)
    "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL)
    "enforce_srid_geom" CHECK (st_srid(geom) = 4326)

and

geodata=# \d accum
              Table "ms.accum"
   Column   |        Type        | Modifiers 
------------+--------------------+-----------
 trk_id     | integer            | 
 accum_dist | double precision[] | 
 accum_time | double precision[] | 

Now here's a rough draft of a function that accumulates distances and times, and puts the values into arrays in the accum table. This function is called with the trk_id as a parameter.

CREATE OR REPLACE FUNCTION public.calculate_accumulated_track(IN t_id integer) RETURNS void AS
$BODY$
DECLARE
i integer;
-- first date/time in the track
dt1 timestamp;
-- the date/time of following track points
dt2 timestamp;
num_rows integer;
-- first_row will be the primary key of the 
-- first track point for the track id passed into the function
first_row integer := 1;
-- Accumulated distance and time, to be inserted into accum table
segment float :=0;
accum_t float;
accum_d float;

BEGIN
    -- Initialize a row in the accum table
    INSERT INTO accum VALUES (t_id, NULL, NULL);
    -- Get the primary key of the first row for this track id.
    SELECT pk INTO first_row FROM tracks WHERE trk_id=t_id ORDER BY pk LIMIT 1;
    SELECT count(*) INTO num_rows FROM tracks WHERE trk_id=t_id;
    SELECT gps_timestmp INTO dt1 FROM tracks WHERE trk_id=t_id ORDER BY gps_timestmp LIMIT 1;

    FOR i in 1..num_rows LOOP
        SELECT gps_timestmp INTO dt2 FROM tracks WHERE pk=i+first_row;
        accum_t := dt2 - dt1;
        IF pk==1 THEN
accum_d:=0;
ELSE
SELECT ST_Distance(t1.geom, t2.geom) INTO segment 
                FROM tracks t1, tracks t2
                WHERE t1.pk=i+first_row-1 AND t2.pk=i+first_row;
END IF;
accum_t := accum_d+segment;     


    -- Now UPDATE the accum table
     UPDATE accum SET accum_time[i]=accum_t WHERE trk_id=t_id;
     UPDATE accum SET accum_dist[i]=accum_d WHERE trk_id=t_id;
    END LOOP;

END;$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION public.calculate_accumulated_track(IN integer) OWNER TO geodba;

Maybe that will help you get started.

Cheers, Micha

share|improve this answer
    
That's it! Thank your for the answer, this is exactly was I was trying to accomplish. –  Andrea Cremaschi Jul 5 '12 at 15:40
1  
Looking over the function a second time, I see that the accum_d above is the cartesian distance between a point on the track and the first point. That's not what you wanted. Rather you need the distance along the track. So I added a variable "segment" and changed the calculation of accum_d to reflect the accumulated distance using each track segment. Have a look. –  Micha Jul 6 '12 at 5:13
    
Yeah, in fact that was I meant with "progressive" distance.. Thank you again! –  Andrea Cremaschi Jul 6 '12 at 8:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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