Re: [postgis-users] Need help constructing a query
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 On 19-7-2012 20:03, Pedro Doria Meunier wrote: Hi, Need help/pointers on how to best construct the following: 1 known point in a table. (call it buoy) another table containing the history (with a geom column) of where a particular object (a boat) has been in time/space What I need to construct is a query that gives me the count of how many times that boat has gone past that buoy. (there *will* be more than 1 rows giving proximity of the object to the buoy -- need to get rid of those ;) -- since the boat is slow moving (2-10 knots)) Btw, both tables' geoms srid=4326. Any help highly appreciated ;) TIA, -- Pedro Doria Meunier Telf. +351 291 933 006 GSM +351 917 999 236 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
Re: [postgis-users] Need help constructing a query
You have not just a spatial dataset but a temporal one as well. You need to define how near the buoy you consider is going past it (a pass) You could wrap an SQL around this, but if you were to use a script to carry out the query, the logic would be something like: 1. get the minimum time for the point dataset 2. get the time for first point within the required distance from the buoy where the time is minimum time 3. get the time for the next point where the point is minimum distance (this completes one pass) 4. set the minimum time to this repeat until you run out of points as a very rough shell script (which is how I typically work) off the top of my head untested: # set min dist to establish a pass DIST=0.001 # get MIN time as timestamp TIME=`psql -d db -Atc select min(time) from vessel points;` # specify buoy position BOUY=ST_SetSrid(ST_Makepoint(lon,lat),4326) # inelegant loop... # will not exit at end of data, press CTRL-C when output stops while [ 1 = 1 ] ; do # get the time of the start of the next pass PASS_TIME=`psql -d db -c select min(time) from point_table where ST_Distance($BOUY,point)$DIST and time '$TIME'::timestamp;` # write the time position for this psql -d db -Atc select time, ST_Astext(point) from point_table where time='$PASS_TIME'::timestamp; # get time for end of pass # (= min time for search for start of next pass) TIME=`psql -d db -Atc select min(time) from point_table where time $PASS_TIME and ST_Distance($BOUY,point)$DIST;` done --- On Fri, 7/20/12, Pedro Doria Meunier pdo...@netmadeira.com wrote: From: Pedro Doria Meunier pdo...@netmadeira.com Subject: [postgis-users] Need help constructing a query To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Friday, July 20, 2012, 6:03 AM Hi, Need help/pointers on how to best construct the following: 1 known point in a table. (call it buoy) another table containing the history (with a geom column) of where a particular object (a boat) has been in time/space What I need to construct is a query that gives me the count of how many times that boat has gone past that buoy. (there *will* be more than 1 rows giving proximity of the object to the buoy -- need to get rid of those ;) -- since the boat is slow moving (2-10 knots)) Btw, both tables' geoms srid=4326. Any help highly appreciated ;) TIA, -- Pedro Doria Meunier Telf. +351 291 933 006 GSM +351 917 999 236 Skype: pdoriam -Inline Attachment Follows- ___ 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
Re: [postgis-users] Need help constructing a query
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
Re: [postgis-users] Need help constructing a query
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
Re: [postgis-users] Need help constructing a query
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
Re: [postgis-users] Need help constructing a query
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
Re: [postgis-users] Need help constructing a query
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
Re: [postgis-users] Need help constructing a query
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
