[mapserver-users] Can I turn off bounding box check for postgis query?

[Ben Madin]

G'day all,

This might be a postgis list question, but I thought I'd try here first.

I have a table with approx 4 million rows, each with a date and start  
and end point (and some other). 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  
to link to a geometry table.

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.

EXPLAIN ANALYZE
SELECT  
"bucket"::text,asbinary(force_collection(force_2d(route)),'NDR'),mid::text
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,  
st_centroid(o.the_geom), st_centroid(d.the_geom)
) 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 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?


cheers

Ben



-- 

Ben Madin
REMOTE INFORMATION

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...