Thanks Paul :D,<br><br>is there any ticket already open about that modification?<br><br>best regards,<br clear="all">Luigi Castro Cardeles<br>
<br><br><div class="gmail_quote">2009/8/7 Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>></span><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
It's not a bug, it's a feature!<br>
<br>
What you are seeing is the mapserver query life cycle. It is designed<br>
to be run on top of shape files, so when used on databases, it is very<br>
redundant. First it gathers the id's of all the features that match<br>
the criteria. Then it goes back and iterates through, getting each<br>
feature one at a time.<br>
<br>
The upcoming 5.6 release of mapserver will replace this query life<br>
cycle with one that is much more efficient for databases.<br>
<br>
P.<br>
<div><div></div><div class="h5"><br>
On Fri, Aug 7, 2009 at 4:52 AM, Luigi Castro<br>
Cardeles<<a href="mailto:luigi.cardeles@gmail.com">luigi.cardeles@gmail.com</a>> wrote:<br>
> Hi list,<br>
><br>
> I have a wfs layer (data stored at postgis) and that layer have a complex<br>
> data statement<br>
><br>
> mapfile<br>
> -----------xxxxxxxxxxxx-------<br>
> LISTCOIID - cgi param<br>
> ##################<br>
> #LAYER WFS PARCEL<br>
> ##################<br>
> LAYER<br>
> NAME 'parcels'<br>
> #DEBUG 5<br>
> METADATA<br>
> "wfs_title" "parcels"<br>
> "wfs_request_method" "get"<br>
> "wfs_service" "wfs"<br>
> "gml_featureid" "id"<br>
> "gml_include_items" "all"<br>
> "wfs_encoding" "UTF8"<br>
> END<br>
><br>
> TYPE POLYGON<br>
> STATUS ON<br>
> DUMP TRUE<br>
> CONNECTIONTYPE postgis<br>
> CONNECTION "host=localhost user='postgres' password=postgres_password<br>
> dbname='wfs_server'"<br>
> DATA "the_geom FROM (select distinct <a href="http://parcel.id" target="_blank">parcel.id</a>,parcel.the_geom from<br>
> mapserver_parcels parcel where parcel.parcelid in( SELECT distinct <a href="http://col.id" target="_blank">col.id</a><br>
> FROM college col WHERE col.date_exclude IS NULL AND ((%LISTCOIID% = <a href="http://col.id" target="_blank">col.id</a>)<br>
> OR (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag,<br>
> perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null<br>
> AND colv.date_exclude is null AND pa.date_exclude is null AND<br>
> colv.perm_group_FK = pagi.perm_group_fk AND pagi.perm_group_fk = <a href="http://pag.id" target="_blank">pag.id</a> AND<br>
> pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id" target="_blank">pa.id</a> AND <a href="http://pa.name" target="_blank">pa.name</a> =<br>
> 'Test1' AND colv.org_interference_fk = %LISTCOIID% AND<br>
> colv.org_interference_fk_fk = <a href="http://col.id" target="_blank">col.id</a>) > 0))) as foo USING UNIQUE id USING<br>
> SRID=4326"<br>
> #PROCESSING "CLOSE_CONNECTION=DEFER"<br>
> PROJECTION<br>
> 'init=epsg:4326'<br>
> # "+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs"<br>
> END<br>
><br>
> CLASS<br>
> NAME 'parcels'<br>
> STYLE<br>
> SYMBOL 0<br>
> SIZE 10<br>
> OUTLINECOLOR 0 0 0<br>
> COLOR 150 123 90<br>
> END<br>
> END<br>
> END<br>
> ------------xxxxxxxxxxxxx----------------<br>
><br>
> when i activated the postgres log, i notice a strange, don't expected<br>
> behavior. Mapserver changes the query (the data statement) and refactory in<br>
> 4(four) other queries:<br>
><br>
> query 1:<br>
><br>
> select<br>
> encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64') as<br>
> geom,"id" from (select distinct <a href="http://parcel.id" target="_blank">parcel.id</a>,parcel.the_geom from<br>
> mapserver_parcels parcel where parcel.parcelid in(SELECT distinct <a href="http://col.id" target="_blank">col.id</a><br>
> FROM college col WHERE col.date_exclude IS NULL AND ((25 = <a href="http://col.id" target="_blank">col.id</a>) OR<br>
> (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag,<br>
> perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null<br>
> AND colv.date_exclude is null AND pa.date_exclude is null AND<br>
> colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id" target="_blank">pag.id</a> AND<br>
> pag.date_exclude IS NULL AND colv.perm_group_FK = pagi.perm_group_ AND<br>
> pagi.perm_group_ = <a href="http://pag.id" target="_blank">pag.id</a> AND pag.date_exclude IS NULL AND<br>
> pagi.perm_acess_fk = <a href="http://pa.id" target="_blank">pa.id</a> AND <a href="http://pa.name" target="_blank">pa.name</a> = org_interference_fk AND<br>
> colv.org_interference_fk = 25 AND colv.org_interference_fk_fk = <a href="http://col.id" target="_blank">col.id</a>) ><br>
> 0))) as foo where the_geom && GeomFromText('POLYGON((-48.05 -15.77,-48.05<br>
> -15.71,-47.85 -15.71,-47.85 -15.77,-48.05 -15.77))',4326)<br>
><br>
> query 2:<br>
><br>
> select * from (select distinct <a href="http://parcel.id" target="_blank">parcel.id</a>,parcel.the_geom from<br>
> mapserver_parcels parcel where parcel.parcelid in(SELECT distinct <a href="http://col.id" target="_blank">col.id</a><br>
> FROM college col WHERE col.date_exclude IS NULL AND ((25 = <a href="http://col.id" target="_blank">col.id</a>) OR<br>
> (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag,<br>
> perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null<br>
> AND colv.date_exclude is null AND pa.date_exclude is null AND<br>
> colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id" target="_blank">pag.id</a> AND<br>
> pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id" target="_blank">pa.id</a> AND <a href="http://pa.name" target="_blank">pa.name</a> =<br>
> org_interference_fk AND colv.org_interference_fk = 25 AND<br>
> colv.org_interference_fk_fk = <a href="http://col.id" target="_blank">col.id</a>) > 0))) as foo where false limit 0<br>
><br>
><br>
> query 3:<br>
><br>
> select<br>
> "id",encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64')<br>
> as geom,"id" from (select distinct <a href="http://parcel.id" target="_blank">parcel.id</a>,parcel.the_geom from<br>
> mapserver_parcels parcel where parcel.parcelid in(SELECT distinct <a href="http://col.id" target="_blank">col.id</a><br>
> FROM college col WHERE col.date_exclude IS NULL AND ((25 = <a href="http://col.id" target="_blank">col.id</a>) OR<br>
> (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag,<br>
> perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null<br>
> AND colv.date_exclude is null AND pa.date_exclude is null AND<br>
> colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id" target="_blank">pag.id</a> AND<br>
> pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id" target="_blank">pa.id</a> AND <a href="http://pa.name" target="_blank">pa.name</a> =<br>
> org_interference_fk AND colv.org_interference_fk = 25 AND<br>
> colv.org_interference_fk_fk = <a href="http://col.id" target="_blank">col.id</a>) > 0))) as foo where "id" = 245<br>
><br>
><br>
> query 4:<br>
><br>
><br>
> select<br>
> "id",encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64')<br>
> as geom,"id" from (select distinct <a href="http://parcel.id" target="_blank">parcel.id</a>,parcel.the_geom from<br>
> mapserver_parcels parcel where parcel.parcelid in(SELECT distinct <a href="http://col.id" target="_blank">col.id</a><br>
> FROM college col WHERE col.date_exclude IS NULL AND ((25 = <a href="http://col.id" target="_blank">col.id</a>) OR<br>
> (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag,<br>
> perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null<br>
> AND colv.date_exclude is null AND pa.date_exclude is null AND<br>
> colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id" target="_blank">pag.id</a> AND<br>
> pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id" target="_blank">pa.id</a> AND <a href="http://pa.name" target="_blank">pa.name</a> =<br>
> org_interference_fk AND colv.org_interference_fk = 25 AND<br>
> colv.org_interference_fk_fk = <a href="http://col.id" target="_blank">col.id</a>) > 0))) as foo where "id" = 246<br>
><br>
> query 5<br>
> select<br>
> "id",encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64')<br>
> as geom,"id" from (select distinct <a href="http://parcel.id" target="_blank">parcel.id</a>,parcel.the_geom from<br>
> mapserver_parcels parcel where parcel.parcelid in(SELECT distinct <a href="http://col.id" target="_blank">col.id</a><br>
> FROM college col WHERE col.date_exclude IS NULL AND ((25 = <a href="http://col.id" target="_blank">col.id</a>) OR<br>
> (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag,<br>
> perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null<br>
> AND colv.date_exclude is null AND pa.date_exclude is null AND<br>
> colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id" target="_blank">pag.id</a> AND<br>
> pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id" target="_blank">pa.id</a> AND <a href="http://pa.name" target="_blank">pa.name</a> =<br>
> org_interference_fk AND colv.org_interference_fk = 25 AND<br>
> colv.org_interference_fk_fk = <a href="http://col.id" target="_blank">col.id</a>) > 0))) as foo where "id" = 247<br>
> ------------xxxxxxxxxxxxxx----------------<br>
><br>
> the wfs layer are returning 3 geometries (that is the expected result) but i<br>
> don't understand why mapserver is generating this four queries.<br>
><br>
> the first query (query 1) returns the id column (pk on my table) and<br>
> the_geom (geometry column).<br>
><br>
> the second query (query 2) don't return nothing.<br>
><br>
> the third query (query 3) return the first id and the first the_geom values<br>
> (query 1 already did that).<br>
><br>
> the forth query (query 4) return the second id and the second the_geom<br>
> values (query 1 already did that).<br>
><br>
> the fifth query (query 5) return the third id and the third the_geom values<br>
> (query 1 already did that).<br>
><br>
> -------------------------------------------<br>
><br>
> Aproximated times:<br>
><br>
> query 1: 3s<br>
> query 2: 18ms<br>
> query 3: 2.6s<br>
> query 4: 2.5s<br>
> query 5: 2.7s<br>
> ------------------------------------------<br>
><br>
><br>
> The problem with this extra-queries is that they consume time so the query<br>
> uses 8 seconds to bring back the information when should use 3 seconds to<br>
> brinq back information.<br>
><br>
> This refactory of the queries made by mapserver is alright or this is a bug?<br>
><br>
> Luigi Castro Cardeles<br>
><br>
><br>
</div></div>> _______________________________________________<br>
> mapserver-users mailing list<br>
> <a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/mailman/listinfo/mapserver-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
><br>
><br>
</blockquote></div><br>