Hi list,<br><br>I have a wfs layer (data stored at postgis) and that layer have a complex 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 dbname='wfs_server'"<br> DATA "the_geom FROM (select distinct <a href="http://parcel.id">parcel.id</a>,parcel.the_geom from mapserver_parcels parcel where parcel.parcelid in( SELECT distinct <a href="http://col.id">col.id</a> FROM college col WHERE col.date_exclude IS NULL AND ((%LISTCOIID% = <a href="http://col.id">col.id</a>) OR (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag, perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null AND colv.date_exclude is null AND pa.date_exclude is null AND colv.perm_group_FK = pagi.perm_group_fk AND pagi.perm_group_fk = <a href="http://pag.id">pag.id</a> AND pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id">pa.id</a> AND <a href="http://pa.name">pa.name</a> = 'Test1' AND colv.org_interference_fk = %LISTCOIID% AND colv.org_interference_fk_fk = <a href="http://col.id">col.id</a>) > 0))) as foo USING UNIQUE id USING 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 behavior. Mapserver changes the query (the data statement) and refactory in 4(four) other queries:<br>
<br>query 1:<br><br>select encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64') as geom,"id" from (select distinct <a href="http://parcel.id">parcel.id</a>,parcel.the_geom from mapserver_parcels parcel where parcel.parcelid in(SELECT distinct <a href="http://col.id">col.id</a> FROM college col WHERE col.date_exclude IS NULL AND ((25 = <a href="http://col.id">col.id</a>) OR (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag, perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null AND colv.date_exclude is null AND pa.date_exclude is null AND colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id">pag.id</a> AND pag.date_exclude IS NULL AND colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id">pag.id</a> AND pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id">pa.id</a> AND <a href="http://pa.name">pa.name</a> = org_interference_fk AND colv.org_interference_fk = 25 AND colv.org_interference_fk_fk = <a href="http://col.id">col.id</a>) > 0))) as foo where the_geom && GeomFromText('POLYGON((-48.05 -15.77,-48.05 -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">parcel.id</a>,parcel.the_geom from mapserver_parcels parcel where parcel.parcelid in(SELECT distinct <a href="http://col.id">col.id</a> FROM college col WHERE col.date_exclude IS NULL AND ((25 = <a href="http://col.id">col.id</a>) OR (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag, perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null AND colv.date_exclude is null AND pa.date_exclude is null AND colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id">pag.id</a> AND pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id">pa.id</a> AND <a href="http://pa.name">pa.name</a> = org_interference_fk AND colv.org_interference_fk = 25 AND colv.org_interference_fk_fk = <a href="http://col.id">col.id</a>) > 0))) as foo where false limit 0<br>
<br><br>query 3:<br><br>select "id",encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64') as geom,"id" from (select distinct <a href="http://parcel.id">parcel.id</a>,parcel.the_geom from mapserver_parcels parcel where parcel.parcelid in(SELECT distinct <a href="http://col.id">col.id</a> FROM college col WHERE col.date_exclude IS NULL AND ((25 = <a href="http://col.id">col.id</a>) OR (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag, perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null AND colv.date_exclude is null AND pa.date_exclude is null AND colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id">pag.id</a> AND pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id">pa.id</a> AND <a href="http://pa.name">pa.name</a> = org_interference_fk AND colv.org_interference_fk = 25 AND colv.org_interference_fk_fk = <a href="http://col.id">col.id</a>) > 0))) as foo where "id" = 245<br>
<br><br>query 4:<br><br><br>select "id",encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64') as geom,"id" from (select distinct <a href="http://parcel.id">parcel.id</a>,parcel.the_geom from mapserver_parcels parcel where parcel.parcelid in(SELECT distinct <a href="http://col.id">col.id</a> FROM college col WHERE col.date_exclude IS NULL AND ((25 = <a href="http://col.id">col.id</a>) OR (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag, perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null AND colv.date_exclude is null AND pa.date_exclude is null AND colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id">pag.id</a> AND pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id">pa.id</a> AND <a href="http://pa.name">pa.name</a> = org_interference_fk AND colv.org_interference_fk = 25 AND colv.org_interference_fk_fk = <a href="http://col.id">col.id</a>) > 0))) as foo where "id" = 246<br>
<br>query 5<br>select "id",encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64') as geom,"id" from (select distinct <a href="http://parcel.id">parcel.id</a>,parcel.the_geom from mapserver_parcels parcel where parcel.parcelid in(SELECT distinct <a href="http://col.id">col.id</a> FROM college col WHERE col.date_exclude IS NULL AND ((25 = <a href="http://col.id">col.id</a>) OR (SELECT count(*) FROM perm_group_item pagi, visual colv , perm_group pag, perm_acess pa WHERE pagi.date_exclude is null AND pag.date_exclude is null AND colv.date_exclude is null AND pa.date_exclude is null AND colv.perm_group_FK = pagi.perm_group_ AND pagi.perm_group_ = <a href="http://pag.id">pag.id</a> AND pag.date_exclude IS NULL AND pagi.perm_acess_fk = <a href="http://pa.id">pa.id</a> AND <a href="http://pa.name">pa.name</a> = org_interference_fk AND colv.org_interference_fk = 25 AND colv.org_interference_fk_fk = <a href="http://col.id">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 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 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 (query 1 already did that).<br><br>the forth query (query 4) return the second id and the second the_geom values (query 1 already did that).<br>
<br>the fifth query (query 5) return the third id and the third the_geom values (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 uses 8 seconds to bring back the information when should use 3 seconds to 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>