[mapserver-users] Unexpected behavior of wfs layer using postgis

[Luigi Castro Cardeles]

Hi list,

I have a wfs layer (data stored at postgis) and that layer have a complex
data statement

mapfile
-----------xxxxxxxxxxxx-------
LISTCOIID - cgi param
##################
#LAYER WFS PARCEL
##################
 LAYER
    NAME 'parcels'
    #DEBUG 5
    METADATA
    "wfs_title" "parcels"
    "wfs_request_method" "get"
    "wfs_service" "wfs"
    "gml_featureid" "id"
    "gml_include_items" "all"
    "wfs_encoding" "UTF8"
    END

    TYPE POLYGON
    STATUS ON
    DUMP TRUE
    CONNECTIONTYPE postgis
    CONNECTION "host=localhost user='postgres' password=postgres_password
dbname='wfs_server'"
    DATA "the_geom FROM (select distinct parcel.id,parcel.the_geom from
mapserver_parcels parcel where parcel.parcelid in( SELECT distinct
col.idFROM college col WHERE col.date_exclude IS NULL AND
((%LISTCOIID% =
col.id) 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 =
pag.id AND pag.date_exclude IS NULL AND pagi.perm_acess_fk = pa.id AND
pa.name = 'Test1' AND colv.org_interference_fk = %LISTCOIID% AND
colv.org_interference_fk_fk = col.id) > 0))) as foo USING UNIQUE id USING
SRID=4326"
    #PROCESSING "CLOSE_CONNECTION=DEFER"
    PROJECTION
    'init=epsg:4326'
    #   "+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs"
    END

     CLASS
       NAME 'parcels'
       STYLE
         SYMBOL 0
         SIZE 10
         OUTLINECOLOR 0 0 0
         COLOR 150 123 90
       END
    END
  END
------------xxxxxxxxxxxxx----------------

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:

query 1:

select
encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64') as
geom,"id" from (select distinct parcel.id,parcel.the_geom from
mapserver_parcels parcel where parcel.parcelid in(SELECT distinct
col.idFROM college col WHERE col.date_exclude IS NULL AND ((25 =
col.id) 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_ =
pag.id AND pag.date_exclude IS NULL AND colv.perm_group_FK =
pagi.perm_group_ AND pagi.perm_group_ = pag.id AND pag.date_exclude IS NULL
AND pagi.perm_acess_fk = pa.id AND pa.name = org_interference_fk AND
colv.org_interference_fk = 25 AND colv.org_interference_fk_fk = col.id) >
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)

query 2:

select * from (select distinct parcel.id,parcel.the_geom from
mapserver_parcels parcel where parcel.parcelid in(SELECT distinct
col.idFROM college col WHERE col.date_exclude IS NULL AND ((25 =
col.id) 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_ =
pag.id AND pag.date_exclude IS NULL AND    pagi.perm_acess_fk = pa.id AND
pa.name = org_interference_fk AND colv.org_interference_fk = 25 AND
colv.org_interference_fk_fk = col.id) > 0))) as foo where false limit 0


query 3:

select
"id",encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64')
as geom,"id" from (select distinct parcel.id,parcel.the_geom from
mapserver_parcels parcel where parcel.parcelid in(SELECT distinct
col.idFROM college col WHERE col.date_exclude IS NULL AND ((25 =
col.id) 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_ =
pag.id AND pag.date_exclude IS NULL AND pagi.perm_acess_fk = pa.id AND
pa.name = org_interference_fk AND colv.org_interference_fk = 25 AND
colv.org_interference_fk_fk = col.id) > 0))) as foo where "id" = 245


query 4:


select
"id",encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64')
as geom,"id" from (select distinct parcel.id,parcel.the_geom from
mapserver_parcels parcel where parcel.parcelid in(SELECT distinct
col.idFROM college col WHERE col.date_exclude IS NULL AND ((25 =
col.id) 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_ =
pag.id AND pag.date_exclude IS NULL AND    pagi.perm_acess_fk = pa.id AND
pa.name = org_interference_fk AND colv.org_interference_fk = 25 AND
colv.org_interference_fk_fk = col.id) > 0))) as foo where "id" = 246

query 5
select
"id",encode(AsBinary(force_collection(force_2d("the_geom")),'NDR'),'base64')
as geom,"id" from (select distinct parcel.id,parcel.the_geom from
mapserver_parcels parcel where parcel.parcelid in(SELECT distinct
col.idFROM college col WHERE col.date_exclude IS NULL AND ((25 =
col.id) 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_ =
pag.id AND pag.date_exclude IS NULL AND    pagi.perm_acess_fk = pa.id AND
pa.name = org_interference_fk AND colv.org_interference_fk = 25 AND
colv.org_interference_fk_fk = col.id) > 0))) as foo where "id" = 247
------------xxxxxxxxxxxxxx----------------

the wfs layer are returning 3 geometries (that is the expected result) but i
don't understand why mapserver is generating this four queries.

the first query (query 1) returns the id column (pk on my table) and
the_geom (geometry column).

the second query (query 2) don't return nothing.

the third query (query 3) return the first id and the first the_geom values
(query 1 already did that).

the forth query (query 4) return the second id and the second the_geom
values (query 1 already did that).

the fifth query (query 5) return the third id and the third the_geom values
(query 1 already did that).

-------------------------------------------

Aproximated times:

query 1: 3s
query 2: 18ms
query 3: 2.6s
query 4: 2.5s
query 5: 2.7s
------------------------------------------


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.

This refactory of the queries made by mapserver is alright or this is a bug?

Luigi Castro Cardeles
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20090807/be4d38a0/attachment.htm>