<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">G'day all,<div><div><br><blockquote type="cite"></blockquote></div><div><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; ">I thought this might be a MapServer List question, but have received no replies so apologise for the cross posting.</span><br><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; "><br>I have a table with approx 4 million rows, each with a date and start and end point (and some other information). This table would probably increase by around 300 000 rows per month. The table is indexed and clustered by date, and the table has no specific 'geometry' information, just a code which I link to a fairly simple geometry table (about 150 polygons).<br><br>To produce a useful sense of the activity, I am collecting all the start and end points in a given time frame, and making line segments, then aggregating by start and end point, and counting the total number of each line.<br><br>EXPLAIN ANALYZE<br>SELECT "bucket"::text,asbinary(force_collection(force_2d(route)),'NDR'),<a href="mid::text">mid::text</a><br>from ( SELECT (ogid*1000 + dgid)::int as mid, origtta, desttta, ST_MakeLine(s.orig, s.dest) as route, total_head, ceiling(log(total_head))::int as bucket<br>FROM ( SELECT o.gid as ogid, d.gid as dgid, o.area_name as origtta, d.area_name as desttta, st_centroid(o.the_geom) as orig, st_centroid(d.the_geom) as dest, count(n.id) as total_head<br>FROM nlis n<br>JOIN tailtagarea o ON n.sourceshire = o.shortcode<br>JOIN tailtagarea d ON n.destinationshire = d.shortcode<br>WHERE sourceshire is not null AND sourceshire not like ' '<br>AND destinationshire is not null AND destinationshire not like ' '<br>AND transferdate >= '13/06/2008' AND transferdate <= '15/06/2008'<br>GROUP BY o.gid, d.gid, o.area_name, d.area_name, st_centroid(o.the_geom), st_centroid(d.the_geom)<br>) AS s<br>) AS foo<br><br>in the terminal, this runs in either about 4 or 9 seconds depending on the date range - if it becomes more than about 3 days of reports the query analyzer notes moving to disk based sorts etc.<br><br>However, when I run it in MapServer, the following statement is added :<br><br>WHERE route && setSRID('BOX3D(105.204213080645 -37.0327214193548,143.604244080645 -9.31123074193548)'::BOX3D, 4326 )<br><br>and if there is two days of data (maybe 110 aggregate rows) it works OK, but if there is 3 or more days (maybe 130 - 140 aggregate lines) it never completes, and neither does it if I take the final statement and run it directly in psql.<br><br>So, I read the documentation, and read about moving the BBOX statement using !BOX!, which I did (using st_within on both the joins, but in fact, this is not the behaviour I want, as it stops any lines being created if they start or end outside the window ( I want to see these ones as well).<br><br>Additionally, it didn't stop the final bbox statement being added, which I thought it was meant to?<br><br>So can I just trick MapServer into not adding the BBOX?<br><br>After aggregation, there is unlikely to be more than about 200 lines to be rendered, so whether or not they are in the map window shouldn't damage rendering performance too much - should it?</span></div><div><br></div><div><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; ">Failing that, my option appears to be create a 'lookup' table when new data arrives, which includes the geometry I'm currently joining in the table, and then create the indexes again. Given that the table could be receiving data 24 hours a day, this would seem like a large amount of overhead. </span></div><div><br></div><div>I am trying to pull about the output from explain, but as the query appears to never complete, it's a bit hard to figure when it isn't finishing.</div><div><br></div><div><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; ">I would welcome any suggestions... any!!</span></div><div><br></div><div><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; ">cheers</span></div><div><br></div><div><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; ">Ben</span></div><div><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; "><br><br></span></div><br><div> <span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><div style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; "><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">-- </div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Ben Madin</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">REMOTE INFORMATION</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">t : +61 8 9192 5455</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">f : +61 8 9192 5535</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">m : 0448 887 220</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Broome WA 6725</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><a href="mailto:ben@remoteinformation.com.au">ben@remoteinformation.com.au</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span class="Apple-tab-span" style="white-space:pre"><span class="Apple-style-span" style="white-space: pre; "> </span></span>Out here, it pays to know...</div></div><br class="Apple-interchange-newline"></span> </div><br></div></body></html>