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

[P Kishor]

On Tue, Jul 6, 2010 at 3:58 AM, Jan Hartmann <j.l.h.hartmann at uva.nl> wrote:
> Hi Puneet,
>
> Would a scripting language, like Pl/Pgsql, be something to tackle your
> problem? It lets you build dynamic commands:
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN.
>
> I have also used PHP to build dynamic SQL queries, but I don't know if you
> can use that in your environment. I access those PHP-scripts in QGis by
> writing out the mapping results as WMS services
> (http://mapserver.org/ogc/mapscript.html).
>
> You could also try Pl/Python from  within Postgresql
> (http://www.postgresql.org/docs/8.4/interactive/plpython.html)l. I've never
> used it, but it's syntactically certainly more flexible than Pl/Pgsql. Or
> even Pl/Perl, if your inclinations lie in that direction :-)

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.


>
> Jan
>
> On 07/06/10 06:39, P Kishor wrote:
>>
>> This started of as a QGIS question, but I am finding it impossible to
>> register for the QGIS forums, so I am asking on the MapServer list, as
>> this is going to evolve into a MapServer question as well.
>>
>> I want to query a PostGIS db with arbitrary queries -- views that join
>> spatial and non-spatial tables. However, the permutations of these
>> queries are too many to predefine them as views. As far as I can tell,
>> QGIS does not allow me to create views/queries on the fly. It can only
>> connect to an already defined view, which I can then further filter,
>> but it can't actually let me create a view.
>>
>> So, what is the best way to go about doing this with MapServer? Oh,
>> and if someone could kindly shed light on QGIS's capabilities in this
>> regard, well, that would be gravy.
>>
>> Many thanks,
>>
>>
>



-- 
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
=======================================================================