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

Paul Ramsey pramsey at cleverelephant.ca
Fri Aug 7 17:25:00 EDT 2009 

Yes, http://trac.osgeo.org/mapserver/ticket/3069

On Fri, Aug 7, 2009 at 2:16 PM, Luigi Castro
Cardeles<luigi.cardeles at gmail.com> wrote:
> Thanks Paul :D,
>
> is there any ticket already open about that modification?
>
> best regards,
> Luigi Castro Cardeles
>
>
> 2009/8/7 Paul Ramsey <pramsey at cleverelephant.ca>
>>
>> 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
>> >
>> >
>
>

More information about the mapserver-users mailing list