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?

1 Answer 1


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 | 
    "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)


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
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;

    -- 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
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;
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;

ALTER FUNCTION public.calculate_accumulated_track(IN integer) OWNER TO geodba;

Maybe that will help you get started.


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.