[Qgis-user] spatialite (and postgis) queries

[Mike Toews]

If your query is very common, you might want to consider making a
database VIEW, so you can add the dynamic layer whenever you want. To
do this, just tailor your SELECT statement so it shows the right
columns, including geometry and a unique integer column (this can be
tricky).

For PostGIS, I'd suggest adding the VIEW using PgAdmin. You can
optionally add the geometry_column metadata manually (although QGIS
picks this out automagically).

For SpatiaLite, see:
http://www.gaia-gis.it/spatialite-2.4.0/Using-Views-Basic.pdf

-Mike

On 30 September 2010 05:47, Ricardo Filipe Soares Garcia da
<ricardo.garcia.silva at gmail.com> wrote:
> Hi all
>
> Thanks for your positive (and fast!) replies.
> I'll be giving the RT Sql layer plugin a try and I'll also investigate
> whether this functionality is available (using python scripting) with
> spatialite layers.
>
> Jurgen:
>
> thanks for the SQL EXISTS tip. I have to brush up on my SQL ;)
> Still I didn't know that it is possible to use other table names in
> QGIS's Query Builder.
>
>
>
> On Thu, Sep 30, 2010 at 1:27 PM, Jürgen E. <jef at norbit.de> wrote:
>> Hi Carson,
>>
>> On Thu, 30. Sep 2010 at 13:18:46 +0100, Carson Farmer wrote:
>>> http://www.carsonfarmer.com/?p=713
>>> and the RT Sql Layer plugin
>>> > 1 - Is it possible to build a query where two (or more) layers are
>>> > involved? Something like:
>>> >
>>> > SELECT a.*
>>> > FROM a, b
>>> > WHERE Contains(b, a)
>>
>> and that simple case can be expressed as exists expression in the query builder
>> (for postgres and spatialite).  Create a layer from a and use following query
>> string:
>>
>>        exists (select * from b where contains(b.geom,a.geom))
>>
>> Creating views is another option (but you still need a unique 32bit integer
>> primary key for the feature ids, which might be hard to generate in some
>> cases).
>>
>>
>> Jürgen
>>
>> --
>> Jürgen E. Fischer         norBIT GmbH               Tel. +49-4931-918175-20
>> Dipl.-Inf. (FH)           Rheinstraße 13            Fax. +49-4931-918175-50
>> Software Engineer         D-26506 Norden               http://www.norbit.de
>>
>> --
>> norBIT Gesellschaft fuer Unternehmensberatung und Informationssysteme mbH
>> Rheinstrasse 13, 26506 Norden
>> GF: Jelto Buurman, HR: Amtsgericht Emden, HRB 5502
>>
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>
>
>
> --
> ___________________________ ___ __
> Ricardo Garcia Silva
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user
>