[mapserver-users] creating views/queries on the fly

Jan Hartmann j.l.h.hartmann at uva.nl
Wed Jul 7 06:24:57 EDT 2010



On 07/06/10 16:27, P Kishor wrote:
> Yes, my preference is PL/Perl, but I am not sure how that would help?
> What are you suggesting? Are you saying that I created services out of
> the queries I want, and then access those services from within QGIS,
> MapServer?
>
> My problem is that all the potential queries are dynamic, but,
> thankfully they are predictable. In pseudo SQL there are
>
> SELECT g.id, g.the_geom, a.attribute1 attrib
> FROM g JOIN a ON g.id = a.id
> WHERE a.id IN (
>      SELECT g1.id FROM g g1 WHERE<some spatial condition>
> )
>
> There are two things are variable in the above query
> - 'attrib' could be 'a.attribute1' or 'a.attribute2', and so on.
> - The '<some spatial condition>' would change.
>
> So, the user will be able say, "Give me a map of attribute<n>  for
> <some spatial condition>," and get a map back.
>
> Oh, and one more thing.. 'a' table could also change (this part is
> experimental -- more on it in a bit). It could be 'a1 a' or 'a2 a',
> and so on (note, a1, a2 have not corresponding mapping with
> attribute1, attribute2).
>
> The reason the 'a' table would/could change is that I am experimenting
> with table partitioning in Pg. I have a master table from which there
> are many inherited tables. Ideally, Pg is supposed to take care of
> funneling the query to the correct inherited table based on the CHECK
> rules, but if I ascertain that querying the correct table directly
> results in some speed gain, I might go that route.
>    
>
In such a situation, I treat the SQL-query as a string in 
PHP/Perl/Python/PlPGsql and change it with the string operators of those 
languages. The problem is: how do you get that string into the 
PostGresql interpreter. With a PHP-based site, you could create some 
text fields, let the user type in the values of the tables and 
attributes, and combine them into a valid SQL-command that can be 
processed with PHP-functions like pg_query. The query returns a map and 
that map can then be displayed in the browser.

With QGis it's not that easy. AFAIK, you can only define *static* WMS or 
PostGIS layers. Additionally, a PostGIS layer has to be a predefined 
table or view, not a written-out Select-query. And PostGresql does not 
have parametrized views (yet), so you cannot change the parameters of a 
view during run time. I don't think you can do these flexible queries 
with regular QGis, but perhaps a Python front-end could be written.  
It's a matter of letting QGis send a string to the Web-Server. After 
that it's easy.

Are you really dependent on QGIS? Else you could use a browser based 
mapping solution (OpenLayers?) with some text fields to type your query 
parameters in. The server could send back the results in WMS or even WFS 
format and the browser would display those.

Jan


More information about the mapserver-users mailing list