Try this -
CREATE VIEW "Testview23" AS
SELECT "a"."PK_UID"*10000 + "b"."PK_UID" AS "NEWID", "a"."ROWID" AS
"ROWID", "a"."Geometry" AS "Geometry",
"b"."ora_nachweis_id" AS "ora_nachweis_id", "b"."zahl" AS "zahl",
"b"."jahr" AS "jahr", "b"."art" AS "art", "b"."sta" AS "sta"
FROM "ASK_VOEGEL" AS "a"
JOIN "ask_art" AS "b" USING ("id")
Afterwards you can do something like:
*select * from Testview23**
*
in the db manager sql window and make the result mapable by choosing
"NEWID" as the unique interger value column and "Geometry" as the
geometry column
Normally QGIS needs to have a column with unique integer values in the
result set. This is done here by making NEWID = a.pk_uid*1000+b.pk_uid
But it still doesn't work if you simply choose the testview23 layer
"using the add spatial layer" menu item. This might be some kind of bug.
Be aware that by joining the polygon table and the observation table you
will "create" a polygon on the map for each and every observation. So if
you have 38 observation inside a polygon, this polygon will be drawn 38
times on the map
Regards
Bo Victor Thomsen
Aestas-GIS
Denmark
Den 20-02-2014 19:34, Bernd Vogelgesang skrev:
Am 20.02.2014 18:13, schrieb Olivier Dalang:
Bernd,
Here's a procedure which works here on QGIS master :
1. Create a spatialite layer "countries" with fields "name" and a
geometry column "geom" of type polygon.
2. Add some features to that layer.
3. In the DB manager, create the view:
CREATE VIEW countries_influence AS
SELECT rowid as rowid, name, Buffer(geom,1) as geom FROM countries
4. In the DB manager, register the view:
INSERT INTO views_geometry_columns VALUES
('countries_influence','geom','rowid','countries','geom',1)
5. Reload the database in the DB manager and right-click : "add to
the canvas".... It does NOT work.
6. From the "layer" menu, choose "add a spatialite layer", navigate
to your db, choose the view, and, tadaaam, it WORKS !
Let me know if it works...
It seems the bugs comes from that "add to the canvas" function, which
sets the source to
path/SpatialiteView.sqlite' table="countries_influence" () sql=
instead of
path/SpatialiteView.sqlite' table="countries_influence" (geom) sql=
Regards,
Olivier
Hi Olivier,
thank you for trying to help,
but, ermm, actually I do not see the point performing this. I have not
really a problem creating views, but more making these functional in
QGIS. Your example, as far as i understand, lacks a join-part
completely, but thats the culprit I fear.
I uploaded my testdb to dropbox.
https://www.dropbox.com/s/8b1g97qu3us15tq/asktest3.sqlite
There is only layer, a view and the table.
Could anyone try it out?
When you query the view with SELECT * FROM "Testview22" , I see the
list of observation I would like also to see in the QGIS attribute
table, but this just doesn't work. It also show 746 rows, but only 37
distinct sets of information.
Cheers
Bernd
2014-02-20 16:51 GMT+01:00 Bernd Vogelgesang
<bernd.vogelges...@gmx.de <mailto:bernd.vogelges...@gmx.de>>:
>
> Am 19.02.2014 20:02, schrieb Olivier Dalang:
>
> Dear Bernd,
>
> I encountered some similar problems with Spatialite views, but in
the end I had it working (sometimes) both in 2.0 and in master... I
used the manual method from the spatialite cookbook though (not the
Spatialite GUI).
>
> I have no time to test now, but from what I recall, this does not
work :
> CREATE VIEW AS SELECT name, geometry FROM table
> while this works :
> CREATE VIEW AS SELECT ROWID, name, geometry FROM table
>
> Maybe you want to give it a last try. I can testify I have some
working spatialite views here. Let us know if this works, if not I
may give it a better look when I have some time.
>
> Another method is running a SELECT query in the DB Manager in QGIS,
and then choosing "Add to map canvas". This is not an actual sqlite
view, but depending on what you need, it could be enough.
>
>
> Hi Olivier,
> added ROWID to the VIEW. Same result in QGIS.
>
> I also performed the select in the DB Manager as you proposed. When
executed, it perfectly show the results i would like to see in the
attribute table, but what "arrives" in the attribute table after
loading as layer is again the duplication of the the first entry for
ROWID of the polygon.
>
> So, this is my select: Anything important missing?
>
> SELECT "a"."ROWID" AS "ROWID", "a"."Geometry" AS "Geometry",
> "b"."zahl" AS "zahl", "b"."jahr" AS "jahr", "b"."art" AS "art",
> "b"."sta" AS "sta"
> FROM "ASK_VOEGEL" AS "a"
> JOIN "ask_art" AS "b" USING ("id")
>
>
> Thanx for your time
> Bernd
>
>
> Good luck !
>
> Olivier
>
>
>
>
>
> 2014-02-19 19:54 GMT+01:00 Bernd Vogelgesang
<bernd.vogelges...@gmx.de <mailto:bernd.vogelges...@gmx.de>>:
>>
>> Am 19.02.2014 19:14, schrieb Steve G:
>>
>>> This does not directly answer your question about spatialite
views, but you
>>> might be able to use relationships directly in QGIS in the near
future (I
>>> think this is a 2.1 update). See:
>>>
>>> http://blog.vitu.ch/10112013-1201/qgis-relations
>>>
>>>
>>> -Steve
>>
>>
>> Hi Steve,
>> Actually I do not care by which means I reach my destination, but
I had the, maybe wrong; impression that using a spatial database
would do the trick.
>> Anyway ...
>> I already installed latest master cause I read about this relation
manager thing a while ago ... but I was left quite clueless about how
to use it.
>> Searched the web for nearly an hour about any info but to no
avail, so many thanks for sharing the link!!
>>
>> Will have a look at it ASAP, maybe thats kind of a solution.
>>
>> Cheers
>> Bernd
>>
>>
>>
>>> --
>>> View this message in context:
http://osgeo-org.1560.x6.nabble.com/Spatialite-VIEW-in-QGIS-question-tp5104565p5104775.html
>>> Sent from the Quantum GIS - User mailing list archive at Nabble.com.
>>> _______________________________________________
>>> Qgis-user mailing list
>>> Qgis-user@lists.osgeo.org <mailto:Qgis-user@lists.osgeo.org>
>>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>>
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user@lists.osgeo.org <mailto: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
_______________________________________________
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user
