[postgis-users] Need help constructing a query

[Tom van Tilburg]

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