SELECT arc_id
FROM road_table
ORDER BY ST_Distance(road_table.geom, 'gps-point in wkt')
LIMIT 1;
Since PostGIS 1.5 you also have the option of using ST_ClosestPoint.
Some discussions of how to use it can be found here:
http://blog.jordogskog.no/2010/02/07/how-to-use-the-new-distance-related-functions-in-postgis-part1/
About ST_ClosestPoint you can read more in the PostGIS documentation:
http://postgis.net/docs/ST_ClosestPoint.html
As I understand your query correct you don't have any line ready but a list of points in a table.
What you need to do then is writing a query that creates lines from those points and use ST_ClosestPoint on those lines. You do it all in one query. To do that you can design your query in several ways and approaches depending on in more detail what you want to get. If it is just the distance you want, then forget about ST_ClosestPoint then use ST_Distance to get the distance. If you just want the closest Point on the edge and/or the distance, then you can build the whole linestring with ST_MakeLine
If you also want to find the points defining the edge that is closest it is probably easiest to make a self join that builds lines from each opint pair in the table instead and use ST_Distance and /or ST_ClosestPoint on that two point lines.
This I guess looks quite messy, but that is because there is a lot of possibilities, what you actually want to get.
I think edge is a more correct word than arc.
Ok, from your comment I guess that what you want is a query that self-joins to create edges and returns the closest edge and distance.
You will need a integer field to make it work like this with integer values ordered as the gps-points and without holes in the series. I call id gid here.
Then you can run something like:
SELECT dist, the_edge FROM
(SELECT ST_Distance(e.the_edge, 'PUT YOUR GPS_POINT AS WKT HERE') as dist, the_edge FROM
(SELECT ST_MakeLine(a.geom, b.geom) AS the_edge FROM
point_table a INNER JOIN point_table b ON b.gid=(a.gid+1)) e) s
ORDER BY dist LIMIT 1;
a.geom references geom in the table that I have put the alias a on. I shouldn't have left out the "AS" key-word, then it looks like this:
SELECT dist, the_edge FROM
(SELECT ST_Distance(e.the_edge, 'PUT YOUR GPS_POINT AS WKT HERE') as dist, the_edge FROM
(SELECT ST_MakeLine(a.geom, b.geom) AS the_edge FROM
point_table AS a INNER JOIN point_table AS b ON b.gid=(a.gid+1)) e) s
ORDER BY dist LIMIT 1;
To get a short intro to sql in general you can test the tutorial on PostGISonline
To see some spatial examples you can try:
mixed examples
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.
Best Answer
hallo
If your original table is called gps_p, your timestamp field is called ts and the points is called th_geom:
What it does is that it builds lines between the points and use st_segmentize to divide the line in 5 segments.
If it is not exactly 5 seconds between your original points it will not work. Then you can just add an id field with a sequence and use that to selfjoin the table with id1+1 = id2 instead.
HTH
/Nicklas