[postgis-users] Can I turn off MapServer bounding box check for postgis query?
ben at remoteinformation.com.au
Wed Jan 21 17:25:22 PST 2009
I thought this might be a MapServer List question, but have received
no replies so apologise for the cross posting.
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).
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.
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
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
FROM nlis n
JOIN tailtagarea o ON n.sourceshire = o.shortcode
JOIN tailtagarea d ON n.destinationshire = d.shortcode
WHERE sourceshire is not null AND sourceshire not like ' '
AND destinationshire is not null AND destinationshire not like ' '
AND transferdate >= '13/06/2008' AND transferdate <= '15/06/2008'
GROUP BY o.gid, d.gid, o.area_name, d.area_name,
) AS s
) AS foo
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.
However, when I run it in MapServer, the following statement is added :
WHERE route && setSRID('BOX3D(105.204213080645
-37.0327214193548,143.604244080645 -9.31123074193548)'::BOX3D, 4326 )
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.
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).
Additionally, it didn't stop the final bbox statement being added,
which I thought it was meant to?
So can I just trick MapServer into not adding the BBOX?
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?
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
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
I would welcome any suggestions... any!!
t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome WA 6725
ben at remoteinformation.com.au
Out here, it pays to know...
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users