[mapserver-users] Unexpected behavior of wfs layer using postgis
Luigi Castro Cardeles
luigi.cardeles at gmail.com
Fri Aug 7 17:16:18 EDT 2009
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.idAND
> > 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
> >
> >
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20090807/740fc1e2/attachment-0001.html
More information about the mapserver-users
mailing list