In message <[EMAIL PROTECTED]>
Rainer Spittel <[EMAIL PROTECTED]> wrote:
> I tried to define the layer data-source like this:
>
> <Datasource>
> <Parameter name="type">postgis</Parameter>
> <Parameter name="dbname">mydb</Parameter>
> <Parameter name="host">127.0.0.1</Parameter>
> <Parameter name="user">user</Parameter>
> <Parameter name="password">password</Parameter>
> <Parameter name="estimate_extent">true</Parameter>
> <Parameter name="table">(select * from roadcl order by class_def) as
> foo</Parameter>
> </Datasource>
>
> The SQL query will not use the spatial index anymore because Postgres
> orders the table first (via sequence scan) before applying the spatial
> filter (the extents of the current view).
No, it will almost certainly use the spatial index to find the data
and then do the sort itself. It is almost always better to choose the
index based on the filter rather than the sort - if the data happens
to wind up sorted because of the index then that is a nice bonus.
If you really want to be sure then use explain to check out the
query - you'll need to add a bounding box condition of course. Here's
an example using an OpenStreetMap data set:
gis=> explain select * from planet_osm_line where way &&
setSRID('BOX3D(-4027.889170960444 6755618.644585053,-424.8904607704981
6759221.643295237)'::box3d,900913) order by z_order;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=239.98..240.14 rows=61 width=756)
Sort Key: z_order
-> Bitmap Heap Scan on planet_osm_line (cost=4.88..238.18 rows=61
width=756)
Filter: (way &&
'010300002031BF0D00010000000500000052876A41C777AFC0ABE140A948C5594152876A41C777AFC0C9BF2B69CDC8594191FACA533F8E7AC0C9BF2B69CDC8594191FACA533F8E7AC0ABE140A948C5594152876A41C777AFC0ABE140A948C55941'::geometry)
-> Bitmap Index Scan on planet_osm_line_index (cost=0.00..4.86
rows=61 width=0)
Index Cond: (way &&
'010300002031BF0D00010000000500000052876A41C777AFC0ABE140A948C5594152876A41C777AFC0C9BF2B69CDC8594191FACA533F8E7AC0C9BF2B69CDC8594191FACA533F8E7AC0ABE140A948C5594152876A41C777AFC0ABE140A948C55941'::geometry)
(6 rows)
As you can see, it filters on the spatial index then does a sort on
the rows it finds.
Tom
--
Tom Hughes ([EMAIL PROTECTED])
http://www.compton.nu/
_______________________________________________
Mapnik-users mailing list
[email protected]
https://lists.berlios.de/mailman/listinfo/mapnik-users
