Pedro, try this. No guarantee that it works on 8.3 though.
If you want this data live, make sure to use views instead of tables and
probably create a valid identifier for showing it from a mapserver.
I see one caveat by the way: if your boat passes the bouy but turns
outside your buffer and then gets into your buffer again, it will be
counted twice. Therefore make your buffer large enough.
--------------------------
DROP TABLE IF EXISTS lines;
CREATE TABLE lines AS
(
SELECT
gps.gps_track As boat, ST_MakeLine(gps.the_geom ORDER BY
gps_time) As geom
FROM gps_points As gps
GROUP BY gps.gps_track
);
DROP TABLE IF EXISTS intersections;
CREATE TABLE intersections AS
(
SELECT boat, (ST_Dump(ST_Intersection(line.geom,
ST_SetSrid(ST_Makepoint(lon,lat),4326)))).geom
FROM lines
);
DROP TABLE IF EXISTS bouy_passes;
CREATE TABLE bouy_passes As
(
SELECT
boat, count(boat) As number_of_passes
FROM intersections
WHERE ST_GeometryType(geom) = 'ST_LineString'
GROUP BY boat
);
--------------------------
On 20-7-2012 13:08, Pedro Doria Meunier wrote:
Pedro Doria Meunier
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
