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

Fri Aug 7 12:57:32 EDT 2009

It's not a bug, it's a feature!

What you are seeing is the mapserver query life cycle. It is designed
to be run on top of shape files, so when used on databases, it is very
redundant. First it gathers the id's of all the features that match
the criteria. Then it goes back and iterates through, getting each
feature one at a time.

The upcoming 5.6 release of mapserver will replace this query life
cycle with one that is much more efficient for databases.

P.

On Fri, Aug 7, 2009 at 4:52 AM, Luigi Castro
Cardeles<luigi.cardeles at gmail.com> wrote:
> 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.id
> FROM 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.id
> FROM 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.id
> FROM 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.id
> FROM 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.id
> FROM 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.id
> FROM 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
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
>