[postgis-users] Need help constructing a query

[Pedro Doria Meunier]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120720/b585a8ac/attachment.html>