Ok. followup on progress here
This query gives me the buoy which I want to count the laps with:
SELECT ST_Buffer(transform(geometry,32628),50)
FROM
customer_pois
WHERE
label = 'myBuoy'
This query constructs the line for the boat track:
SELECT
unit_imei As boat, ST_MakeLine(coordinates) As geom
FROM units_history
WHERE
unit_imei = 'theImei' AND
zulu_timestamp >= '2012-07-18' AND zulu_timestamp <= '2012-07-19'
GROUP BY unit_imei
Now I need to transform Tom's query (compatible with PG 8.4+)
*many thanks to Tom for this elegant approach ;)*
/WITH line 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;
)
,intersections AS
(
SELECT boat, (ST_Dump(ST_Intersection(line.geom,
ST_SetSrid(ST_Makepoint(lon,lat),4326)))).geom
)
SELECT
boat, count(boat) As number_of_passes
FROM intersections
WHERE ST_GeometryType(geom) = 'ST_LineString'
GROUP BY boat/
Into one (compatible with PG 8.3 by means of sub-queries) performing an
ST_Intersection for those 2 sets
giving me the number of times that line crosses the buffered point.
TIA,
--
Pedro Doria Meunier
Telf. +351 291 933 006
GSM +351 915 818 823
Skype: pdoriam
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
