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
b...@remoteinformation.com.au
Out here, it pays to
know...
_______________________________________________
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
