Re: [mapserver-users] WFS Filter Encoding and resulting DB-queries
Hello Assefa, Yewondwossen Assefa yass...@dmsolutions.ca, [20110223 - 22:52:33] On 23/02/2011 8:06 AM, Stephan Holl wrote: Hello Assefa, Yewondwossen Assefayass...@dmsolutions.ca, [20110223 - 07:26:34] Hi Stephan, On 22/02/2011 3:44 AM, Stephan Holl wrote: Hello list, while playing with MapServer WFS Filter Encoding capabilities I found some strange things when it comes to form the DB-queries (the requested layer is PostGIS-driven). It seems that there will always be a full table-scan and later on the FE will be applied. Is this done by design? Filters that do include spatial filters end up using the mapserver experssions. 'Simple' filters should end be using traslated into the layer's filter expression and end up being used by postgis as part of the data selection. What does the filter encoding looks like? It is not a spatial-filter we use currently. It is an attribute-based IsLike-Filter like seen here[1], but we use it with POST. So adding a BBOX-related filter as another AND-filter will result in optimized SQL-Query with only the subset fetched from the database? If so, this seems worth to document though. The filter you sent as example should use the layer's filter element and that should be used when retrieving the data at the postgis level. When the layer is in debug mode, MapServer 5.6.x logs in this case shows something like (which indicates that the filter is used at the postgis level) ... msPostGISLayerWhichShapes query: select and ( (NAME like 'Syd%' escape '!') ) yes, I will get the above query-entries. But before of that there is another query stated in the logs: select * from projects_with_location where false limit 0 which pointed me to a (false) conclusion. Thank you for clarifying thouh. Best Stephan -- Stephan Holl stephan.h...@intevation.de | Tel.: +49 (0)541-33 508 3663 Intevation GmbH, Neuer Graben 17, 49074 OS | AG Osnabrück - HR B 18998 Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner signature.asc Description: PGP signature ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] WFS Filter Encoding and resulting DB-queries
Hello Assefa, Yewondwossen Assefa yass...@dmsolutions.ca, [20110223 - 22:52:33] On 23/02/2011 8:06 AM, Stephan Holl wrote: Hello Assefa, Yewondwossen Assefayass...@dmsolutions.ca, [20110223 - 07:26:34] Hi Stephan, On 22/02/2011 3:44 AM, Stephan Holl wrote: Hello list, while playing with MapServer WFS Filter Encoding capabilities I found some strange things when it comes to form the DB-queries (the requested layer is PostGIS-driven). It seems that there will always be a full table-scan and later on the FE will be applied. Is this done by design? Filters that do include spatial filters end up using the mapserver experssions. 'Simple' filters should end be using traslated into the layer's filter expression and end up being used by postgis as part of the data selection. What does the filter encoding looks like? It is not a spatial-filter we use currently. It is an attribute-based IsLike-Filter like seen here[1], but we use it with POST. So adding a BBOX-related filter as another AND-filter will result in optimized SQL-Query with only the subset fetched from the database? If so, this seems worth to document though. The filter you sent as example should use the layer's filter element and that should be used when retrieving the data at the postgis level. When the layer is in debug mode, MapServer 5.6.x logs in this case shows something like (which indicates that the filter is used at the postgis level) ... msPostGISLayerWhichShapes query: select and ( (NAME like 'Syd%' escape '!') ) ... Don't you get similar logs? Wait, I have to recheck with my collegue if he can provide more Information and probably logfiles. I do not have them handy right now. Thanks for your clarification though. Best Stephan -- Stephan Holl stephan.h...@intevation.de | Tel.: +49 (0)541-33 508 3663 Intevation GmbH, Neuer Graben 17, 49074 OS | AG Osnabrück - HR B 18998 Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner signature.asc Description: PGP signature ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] WFS Filter Encoding and resulting DB-queries
Hi Stephan, On 22/02/2011 3:44 AM, Stephan Holl wrote: Hello list, while playing with MapServer WFS Filter Encoding capabilities I found some strange things when it comes to form the DB-queries (the requested layer is PostGIS-driven). It seems that there will always be a full table-scan and later on the FE will be applied. Is this done by design? Filters that do include spatial filters end up using the mapserver experssions. 'Simple' filters should end be using traslated into the layer's filter expression and end up being used by postgis as part of the data selection. What does the filter encoding looks like? Using URL-substitutions of the Search-keyword and apply it to a FILTER-element the resulting SQL-query has the given WHERE-filter set. But this opens the door for SQL-injections AFAIK. Setting the validation pattern is a little prevention. Are there any other ways to realize a keyword-driven search with MapServer beside FE IsLike or FILTER-substitution? I am using MS 5.6.5 on debian stable. TIA Stephan ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users -- Assefa Yewondwossen Software Analyst Email: yass...@dmsolutions.ca http://www.dmsolutions.ca/ Phone: (613) 565-5056 (ext 14) Fax: (613) 565-0925 ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] WFS Filter Encoding and resulting DB-queries
Hello Assefa, Yewondwossen Assefa yass...@dmsolutions.ca, [20110223 - 07:26:34] Hi Stephan, On 22/02/2011 3:44 AM, Stephan Holl wrote: Hello list, while playing with MapServer WFS Filter Encoding capabilities I found some strange things when it comes to form the DB-queries (the requested layer is PostGIS-driven). It seems that there will always be a full table-scan and later on the FE will be applied. Is this done by design? Filters that do include spatial filters end up using the mapserver experssions. 'Simple' filters should end be using traslated into the layer's filter expression and end up being used by postgis as part of the data selection. What does the filter encoding looks like? It is not a spatial-filter we use currently. It is an attribute-based IsLike-Filter like seen here[1], but we use it with POST. So adding a BBOX-related filter as another AND-filter will result in optimized SQL-Query with only the subset fetched from the database? If so, this seems worth to document though. Using URL-substitutions of the Search-keyword and apply it to a FILTER-element the resulting SQL-query has the given WHERE-filter set. But this opens the door for SQL-injections AFAIK. Setting the validation pattern is a little prevention. Are there any other ways to realize a keyword-driven search with MapServer beside FE IsLike or FILTER-substitution? Best Stephan [1] http://demo.mapserver.org/cgi-bin/wfs?VERSION=1.0.0SERVICE=WFSREQUEST=GetFeatureTYPENAME=citiesFilter=%3CFilter%3E%3CPropertyIsLike%20wildcard=%27*%27%20singleChar=%27.%27%20escape=%27!%27%3E%3CPropertyName%3ENAME%3C/PropertyName%3E%3CLiteral%3ESyd*%3C/Literal%3E%3C/PropertyIsLike%3E%3C/Filter%3E -- Stephan Holl stephan.h...@intevation.de | Tel.: +49 (0)541-33 508 3663 Intevation GmbH, Neuer Graben 17, 49074 OS | AG Osnabrück - HR B 18998 Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner signature.asc Description: PGP signature ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
Re: [mapserver-users] WFS Filter Encoding and resulting DB-queries
On 23/02/2011 8:06 AM, Stephan Holl wrote: Hello Assefa, Yewondwossen Assefayass...@dmsolutions.ca, [20110223 - 07:26:34] Hi Stephan, On 22/02/2011 3:44 AM, Stephan Holl wrote: Hello list, while playing with MapServer WFS Filter Encoding capabilities I found some strange things when it comes to form the DB-queries (the requested layer is PostGIS-driven). It seems that there will always be a full table-scan and later on the FE will be applied. Is this done by design? Filters that do include spatial filters end up using the mapserver experssions. 'Simple' filters should end be using traslated into the layer's filter expression and end up being used by postgis as part of the data selection. What does the filter encoding looks like? It is not a spatial-filter we use currently. It is an attribute-based IsLike-Filter like seen here[1], but we use it with POST. So adding a BBOX-related filter as another AND-filter will result in optimized SQL-Query with only the subset fetched from the database? If so, this seems worth to document though. The filter you sent as example should use the layer's filter element and that should be used when retrieving the data at the postgis level. When the layer is in debug mode, MapServer 5.6.x logs in this case shows something like (which indicates that the filter is used at the postgis level) ... msPostGISLayerWhichShapes query: select and ( (NAME like 'Syd%' escape '!') ) ... Don't you get similar logs? Best Stephan [1] http://demo.mapserver.org/cgi-bin/wfs?VERSION=1.0.0SERVICE=WFSREQUEST=GetFeatureTYPENAME=citiesFilter=%3CFilter%3E%3CPropertyIsLike%20wildcard=%27*%27%20singleChar=%27.%27%20escape=%27!%27%3E%3CPropertyName%3ENAME%3C/PropertyName%3E%3CLiteral%3ESyd*%3C/Literal%3E%3C/PropertyIsLike%3E%3C/Filter%3E -- Assefa Yewondwossen Software Analyst Email: yass...@dmsolutions.ca http://www.dmsolutions.ca/ Phone: (613) 565-5056 (ext 14) Fax: (613) 565-0925 ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
[mapserver-users] WFS Filter Encoding and resulting DB-queries
Hello list, while playing with MapServer WFS Filter Encoding capabilities I found some strange things when it comes to form the DB-queries (the requested layer is PostGIS-driven). It seems that there will always be a full table-scan and later on the FE will be applied. Is this done by design? Using URL-substitutions of the Search-keyword and apply it to a FILTER-element the resulting SQL-query has the given WHERE-filter set. But this opens the door for SQL-injections AFAIK. Setting the validation pattern is a little prevention. Are there any other ways to realize a keyword-driven search with MapServer beside FE IsLike or FILTER-substitution? I am using MS 5.6.5 on debian stable. TIA Stephan -- Stephan Holl stephan.h...@intevation.de | Tel.: +49 (0)541-33 508 3663 Intevation GmbH, Neuer Graben 17, 49074 OS | AG Osnabrück - HR B 18998 Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner signature.asc Description: PGP signature ___ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users
