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

[P Kishor]

On Wed, Jul 7, 2010 at 5:24 AM, Jan Hartmann <j.l.h.hartmann at uva.nl> wrote:
>
>
> 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.
>

Ok. Thanks for your suggestions Jan, and thanks to Rahkonen as well.
Seems like what I want to do is easier to pull off with MapServer, and
that is what I will do. I am not dependent on QGIS, but it is a
convenient, quick desktop viewer, and thus, quite useful. There seems
to be a Pgquery editor plugin for QGIS, but I can't make head or tail
of it, so I am giving up on it.

(Sidenote: the QGIS experience is frustrating. The program can do so
much, but it does so in its own idiosyncratic, sometimes, seemingly
half-baked way. Even registering on the darned forums was an
impossibility. Apparently, I couldn't answer the
thwart-against-the-bots question correctly, and I certainly couldn't
read the almost illegible captcha.)

> Jan
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================