I'm going to be extra-cheeky here ... :D Must be getting old as I can't wrap my head around the 8.3 construct of the query below.. :S
This, as you might imagine, is for a regatta and I need to count the laps made around a single buoy for each boat. This info is going to be overlayed on Google Earth where the boats are being tracked in real-time (a pro bono service ;)). I'll even throw in a "Thanks to Tom van Tilburg" there if you can help me with the 8.3 construct! ;) TIA, Pedro Doria Meunier Telf. +351 291 933 006 GSM +351 915 818 823 Skype: pdoriam On 07/20/2012 10:21 AM, Tom van Tilburg wrote: > Just write the 'withs' as seperate queries that create a table or make > them subqueries. > Rest is for you to figure out ;-) > > P.S. I'd recommend (asking for) upgrading your postgres installation, > 8.3 is getting old..... > > On 20-7-2012 10:59, Pedro Doria Meunier wrote: >> Elegant :) >> >> Turns out I'm stuck with an old server running PG 8.3 and can't use >> WITH clause :D >> Thanks Tom! >> Pedro Doria Meunier >> Telf. +351 291 933 006 >> GSM +351 915 818 823 >> Skype: pdoriam >> On 07/20/2012 09:03 AM, Tom van Tilburg wrote: >>> Hmm, I would still think that, as long as you have a single line >>> per-boat, this would solve your problem. >>> Because you can find out how often this single line crosses the bouy >>> area. The combination of ST_Intersection and ST_Dump would do that >>> for you. >>> >>> Try something like: >>> -------------------- >>> 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 >>> -------------------------------- >>> >>> if your postgres is below 9.0 the gps.tracks might work different. >>> See manual. >>> >>> Cheers, >>> Tom >>> >>> >>> On 20-7-2012 9:38, Pedro Doria Meunier wrote: >>>> On 07/20/2012 07:38 AM, Tom van Tilburg wrote: >>>>> Hi Pedro, >>>>> >>>>> You might try to create lines from the boat tracks (see examples >>>>> in manual for ST_MakeLine) and circles (st_buffer) from the >>>>> proximity areas around the buoy. >>>>> Now do a ST_Intersection(boatline, bouycircle). The result should >>>>> be a set of lines within the bouycircle. Every line stands for one >>>>> pass along the bouy. >>>>> I think ST_Intersection might result in a geometry collection of >>>>> lines and points so you would have to make a dump of the result >>>>> with ST_Dump and then select only the lines. >>>>> >>>>> Cheers, >>>>> Tom >>>>> >>>> >>>> Elegant thinking but that would end up in a *single* line, as I >>>> only have the timestamp and (point)geom to work with. >>>> Thanks for your input though :) >>>> >>>> -- >>>> 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 >>> >>> >>> >>> _______________________________________________ >>> postgis-users mailing list >>> postgis-users@postgis.refractions.net >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >> >> >> _______________________________________________ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
