Re: [Qgis-user] spatialite (and postgis) queries
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 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. 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@lists.osgeo.org >> http://lists.osgeo.org/mailman/listinfo/qgis-user >> > > > > -- > ___ ___ __ > Ricardo Garcia Silva > ___ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/qgis-user > ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] spatialite (and postgis) queries
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. 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@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/qgis-user > -- ___ ___ __ Ricardo Garcia Silva ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] spatialite (and postgis) queries
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 13Fax. +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@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] spatialite (and postgis) queries
Hi Ricardo, On Thu, Sep 30, 2010 at 2:15 PM, Ricardo Filipe Soares Garcia da < ricardo.garcia.si...@gmail.com> wrote: > Some questions on building querys with spatialite and postgis: You can use the RT Sql Layer plugin, but for postgis layers only. In this moment there is no way to do the same thing with spatialite layers. Cheers. > > 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 get a visual result in the main Qgis display? I know I can do such > a query using the spatialite manager, but the return value is > visualized only as tabular data. > > 2 - Is it possible to dynamically create new geometries? For example, > write a custom query that creates a buffer around a point and store it > in a temporary layer? > > >From what I have been investigating it seems that these things are not > possible. Am I wrong? This kind of functionality would be really > awesome. > > -- > ___ ___ __ > Ricardo Garcia Silva > ___ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/qgis-user > -- Giuseppe Sucameli ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] spatialite (and postgis) queries
Have a look at this: http://www.carsonfarmer.com/?p=713 and the RT Sql Layer plugin Carson On 30 September 2010 13:15, Ricardo Filipe Soares Garcia da wrote: > Hi list > Some questions on building querys with spatialite and postgis: > > 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 get a visual result in the main Qgis display? I know I can do such > a query using the spatialite manager, but the return value is > visualized only as tabular data. > > 2 - Is it possible to dynamically create new geometries? For example, > write a custom query that creates a buffer around a point and store it > in a temporary layer? > > >From what I have been investigating it seems that these things are not > possible. Am I wrong? This kind of functionality would be really > awesome. > > -- > ___ ___ __ > Ricardo Garcia Silva > ___ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/qgis-user > -- Carson J. Q. Farmer ISSP Doctoral Fellow National Centre for Geocomputation National University of Ireland, Maynooth, http://www.carsonfarmer.com/ ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
