Re: [Qgis-user] Spatialite VIEW in QGIS question
Dear folks,
thanks a lot for all who tried to help.
I have a working solution now, which I will hopefuly be able to forge
into some script, so all steps for many layers will run more automatic.
I maybe had other working solutions before, but I made the mistake and
didn't see that I had selected the one object only selection mode in
QGIS ... so of course it only picked the most up laying polygon and
couldn't show me more info than just one row in the attribute table ...
too bad.
Sandro Furieri was so kind to help me along as well
https://mail.google.com/mail/u/0/?shva=1#inbox/144507c0ecb4c392
The things that were important: not to have a ROWID in the joined table,
but rename it
Creating a table instead of a view. (... SQL VIEWs are rather
extravagant and whimsical objects, and they could
easily introduce many hard-to-be-solved undesired side effects. ...)
So as a sample for one of my layers, it looks like this now:
CREATE VIEW View_PUNKTE AS
SELECT 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_PUNKTE AS a JOIN ask_art AS b USING (id);
INSERT INTO views_geometry_columns (view_name, view_geometry,
view_rowid, f_table_name, f_geometry_column, read_only) VALUES
('view_punkte', 'geometry', 'rowid', 'ask_punkte', 'geometry',1);
CREATE TABLE Abfrage_PUNKTE AS
SELECT rowid AS old_rowid, Geometry, ora_nachweis_id, zahl, jahr, art, sta
FROM View_PUNKTE;
SELECT RecoverGeometryColumn('Abfrage_PUNKTE', 'geometry', 31468,
'MULTIPOINT', 'XY');
SELECT CreateSpatialIndex('Abfrage_PUNKTE', 'geometry');
I have now finally realized/accepted, that there is NO WAY to avoid
duplication of geometries (very very sad) for 1:n relations in QGIS, so
this really bloats the layers ... but thats life.
Maybe I could skip some of this and make a table directly from a select,
but I'm happy with at least one working solution.
Now i will try and put all this into a batch script that will generate
me those layers automagically cause filling in all the separate commands
for several layers is boring, time consuming an error prone.
So thanx mates,
Bernd
Am 18.02.2014 21:29, schrieb Bernd Vogelgesang:
Hi folks,
I'm quite desperate, cause I do not seem to understand what I'm doing
wrong, or if it's just not possible to do.
I have a polygon layer in my spatialite database and a normal table
with bird observations. There are many observation entries for each
item in the polygons.
They share the simple field id.
I created dozens of view, following strictly
http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html
(and i REALLY can't find any other tutorials).
When i query SELECT * FROM test17 in Spatialite GUI, it shows all
the lines with different observation entries for each polygon id, when
i load the VIEW in QGIS, it doesn't but duplicates the first matching
observation for one polygon over and over.
The idea is to quickly identify all observations when selecting a
polygon, and then go to the attribute table to see which species are
there.
Is it possible to create a one-to-many spatial VIEW with Spatialite
GUI ?
if yes
Whats the trick?
if no
Is QGIS just not able to show the views table correctly?
if no
Whats the trick?
Wasted many days on that now, and time is running away.
Please, someone, help
Bernd
___
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 VIEW in QGIS question
Bernd -
I think, that you have misunderstood some of the basic tenets of
relational database technology:
* If you establish a straight 1:n relationships in a relational
database (as SQLite, SpatiaLite or Postgres/PostGis) you'll get at
least n rows in the resulting view; if you want it otherwise you'll
have to use aggregate functions to group and aggregate your results
* If you want to select 1 row in a main table and afterwards have a
look at n rows in a sub-table that's related to the main table by
some common field(s) - that's the responsibility of the
presentation layer meaning Access, Excel or QGIS . But QGIS hasn't
this functionality before ver. 2.2 (which isn't released yet).
If you want the above mentioned function in QGIS, you have to wait for
QGIS 2.2 or download some bleeding edge QGIS like QGIS Weekly
(http://qgis.org/downloads/weekly/) . Look at this article:
http://blog.vitu.ch/10112013-1201/qgis-relations
Regards
Bo Victor Thomsen
Aestas-GIS
Denmark
Den 21-02-2014 18:23, Bernd Vogelgesang skrev:
Dear folks,
thanks a lot for all who tried to help.
I have a working solution now, which I will hopefuly be able to forge
into some script, so all steps for many layers will run more automatic.
I maybe had other working solutions before, but I made the mistake and
didn't see that I had selected the one object only selection mode in
QGIS ... so of course it only picked the most up laying polygon and
couldn't show me more info than just one row in the attribute table
... too bad.
Sandro Furieri was so kind to help me along as well
https://mail.google.com/mail/u/0/?shva=1#inbox/144507c0ecb4c392
The things that were important: not to have a ROWID in the joined
table, but rename it
Creating a table instead of a view. (... SQL VIEWs are rather
extravagant and whimsical objects, and they could
easily introduce many hard-to-be-solved undesired side effects. ...)
So as a sample for one of my layers, it looks like this now:
CREATE VIEW View_PUNKTE AS
SELECT 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_PUNKTE AS a JOIN ask_art AS b USING (id);
INSERT INTO views_geometry_columns (view_name, view_geometry,
view_rowid, f_table_name, f_geometry_column, read_only) VALUES
('view_punkte', 'geometry', 'rowid', 'ask_punkte', 'geometry',1);
CREATE TABLE Abfrage_PUNKTE AS
SELECT rowid AS old_rowid, Geometry, ora_nachweis_id, zahl, jahr, art,
sta
FROM View_PUNKTE;
SELECT RecoverGeometryColumn('Abfrage_PUNKTE', 'geometry', 31468,
'MULTIPOINT', 'XY');
SELECT CreateSpatialIndex('Abfrage_PUNKTE', 'geometry');
I have now finally realized/accepted, that there is NO WAY to avoid
duplication of geometries (very very sad) for 1:n relations in QGIS,
so this really bloats the layers ... but thats life.
Maybe I could skip some of this and make a table directly from a
select, but I'm happy with at least one working solution.
Now i will try and put all this into a batch script that will generate
me those layers automagically cause filling in all the separate
commands for several layers is boring, time consuming an error prone.
So thanx mates,
Bernd
Am 18.02.2014 21:29, schrieb Bernd Vogelgesang:
Hi folks,
I'm quite desperate, cause I do not seem to understand what I'm doing
wrong, or if it's just not possible to do.
I have a polygon layer in my spatialite database and a normal table
with bird observations. There are many observation entries for each
item in the polygons.
They share the simple field id.
I created dozens of view, following strictly
http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html
(and i REALLY can't find any other tutorials).
When i query SELECT * FROM test17 in Spatialite GUI, it shows all
the lines with different observation entries for each polygon id,
when i load the VIEW in QGIS, it doesn't but duplicates the first
matching observation for one polygon over and over.
The idea is to quickly identify all observations when selecting a
polygon, and then go to the attribute table to see which species are
there.
Is it possible to create a one-to-many spatial VIEW with Spatialite
GUI ?
if yes
Whats the trick?
if no
Is QGIS just not able to show the views table correctly?
if no
Whats the trick?
Wasted many days on that now, and time is running away.
Please, someone, help
Bernd
___
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
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Spatialite VIEW in QGIS question
Am 21.02.2014, 22:17 Uhr, schrieb Bo Victor Thomsen bo.victor.thom...@gmail.com:
Bernd -
I think, that you have misunderstood some of the basic tenets of
relational database technology:
If you establish a straight 1:n relationships in a
relational database (as SQLite, SpatiaLite or
Postgres/PostGis) you'll get at least n rows in the resulting
view; if you want it otherwise you'll have to use aggregate
functions to group and aggregate your results
If you want to select 1 row in a main table and afterwards
have a look at n rows in a sub-table that's related to the
main table by some common field(s) - that's the
responsibility of the presentation layer meaning Access, Excel
or QGIS . But QGIS hasn't this functionality before ver. 2.2
(which isn't released yet).
If you want the above mentioned function in QGIS, you have to wait
for QGIS 2.2 or download some bleeding edge QGIS like QGIS Weekly
(http://qgis.org/downloads/weekly/) . Look at this article:
http://blog.vitu.ch/10112013-1201/qgis-relations
Hi Bo,yes, I misunderstood that obviously, or i was "hoping" that those things were already easier in the year of 2014 ;)Anyway, found a working solution my collegue is happy with. I also tested with master yesterday after reading the link about qgis relations, but trying this only crashed the system.But we all are awaiting a bright future with easy to establish 1:n relations, I'm sure of that, and then nothing will stop me ;)ThanxBernd
Regards
Bo Victor Thomsen
Aestas-GIS
Denmark
Den 21-02-2014 18:23, Bernd Vogelgesang skrev:
Dear
folks,
thanks a lot for all who tried to help.
I have a working solution now, which I will hopefuly be able to
forge into some script, so all steps for many layers will run more
automatic.
I maybe had other working solutions before, but I made the mistake
and didn't see that I had selected the "one object only" selection
mode in QGIS ... so of course it only picked the most up laying
polygon and couldn't show me more info than just one row in the
attribute table ... too bad.
Sandro Furieri was so kind to help me along as well
https://mail.google.com/mail/u/0/?shva=1#inbox/144507c0ecb4c392
The things that were important: not to have a ROWID in the joined
table, but rename it
Creating a table instead of a view. ("... SQL VIEWs are rather
extravagant and whimsical objects, and they could
easily introduce many hard-to-be-solved undesired side effects.
...")
So as a sample for one of my layers, it looks like this now:
CREATE VIEW View_PUNKTE AS
SELECT 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_PUNKTE AS a JOIN ask_art AS b USING (id);
INSERT INTO views_geometry_columns (view_name, view_geometry,
view_rowid, f_table_name, f_geometry_column, read_only) VALUES
('view_punkte', 'geometry', 'rowid', 'ask_punkte', 'geometry',1);
CREATE TABLE Abfrage_PUNKTE AS
SELECT rowid AS old_rowid, Geometry, ora_nachweis_id, zahl, jahr,
art, sta
FROM View_PUNKTE;
SELECT RecoverGeometryColumn('Abfrage_PUNKTE', 'geometry', 31468,
'MULTIPOINT', 'XY');
SELECT CreateSpatialIndex('Abfrage_PUNKTE', 'geometry');
I have now finally realized/accepted, that there is NO WAY to
avoid duplication of geometries (very very sad) for 1:n relations
in QGIS, so this really bloats the layers ... but thats life.
Maybe I could skip some of this and make a table directly from a
select, but I'm happy with at least one working solution.
Now i will try and put all this into a batch script that will
generate me those layers automagically cause filling in all the
separate commands for several layers is boring, time consuming an
error prone.
So thanx mates,
Bernd
Am 18.02.2014 21:29, schrieb Bernd Vogelgesang:
Hi folks,
I'm quite desperate, cause I do not seem to understand what I'm
doing wrong, or if it's just not possible to do.
I have a polygon layer in my spatialite database and a normal
table with bird observations. There are many observation entries
for each item in the polygons.
They share the simple field "id".
I created dozens of view,
Re: [Qgis-user] Spatialite VIEW in QGIS question
Am 19.02.2014 20:06, schrieb Alex Mandel: On 02/19/2014 10:43 AM, Bernd Vogelgesang wrote: Am 19.02.2014 16:55, schrieb Alex Mandel: On 02/18/2014 12:29 PM, Bernd Vogelgesang wrote: Hi folks, I'm quite desperate, cause I do not seem to understand what I'm doing wrong, or if it's just not possible to do. I have a polygon layer in my spatialite database and a normal table with bird observations. There are many observation entries for each item in the polygons. They share the simple field id. I created dozens of view, following strictly http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html (and i REALLY can't find any other tutorials). When i query SELECT * FROM test17 in Spatialite GUI, it shows all the lines with different observation entries for each polygon id, when i load the VIEW in QGIS, it doesn't but duplicates the first matching observation for one polygon over and over. The idea is to quickly identify all observations when selecting a polygon, and then go to the attribute table to see which species are there. Is it possible to create a one-to-many spatial VIEW with Spatialite GUI ? if yes Whats the trick? if no Is QGIS just not able to show the views table correctly? if no Whats the trick? Wasted many days on that now, and time is running away. Please, someone, help Bernd I usually cheat, and in making my View I do a left join to the attribute table with the geometries. It ends up duplicating the geometries for each match which can be inefficient on a large dataset but works perfectly reasonable most of the time. Then the regular info and select tools return multiple records. Thank you Alex for your answer. I think this combination was the only one I haven't tried yet. But ... the outcome is as depressing as all my other, or even worse. Instead of 37 features i now have 9725, and the attribute table has the same amount of entries instead of some 700. When loading the attribute table, there are only ERROR in the fields, and it took some minutes till I was able to switch to show only selected. And ... surprise surprise, it again showed just the entry of the first species in all of the rows instead of all the individual observations. Guys, this is a very sad topic and I really can't understand how people can work with 1:n data even on the most basic level under these circumstances ... Or doesn't anyone work with 1:n data? Well, the the world I'm living in is full of that ... After nearly one week of trial and error, I am giving up now. Cheers Bernd I rarely need to visually explore the 1:n without aggregation. What I love about spatial databases is that I can easily query whatever I want including 1:n but I don't need to click on an individual. What attribute about the second table do you need to know? How many, average x, or even just a list of ids? If you want the Birds per polygon check out the group concat from sqlite: CREATE View birdlists as SELECT group_concat(species), count(species) FROM table1 JOIN table2 ON table1.id = table2.id GROUP BY polygon This gives you the list of birds per polygon as a view of the polygon layer. Yes occasionally you want to click and get a list, I've done this in a web page where you take the id, then query the db to return the rows related. Like I mentioned before a python plugin should be able to do this. Thanks, Alex Hi Alex, we need to have the list of simply all birds that have once been observed there in the past. I need the info of each observation what species, how many of that , year of observation and breeding status ... in short again: all lines from the table that match the polygon id. Nothing to calculate, nothing to group. We need to have an overview what has happened in the past in these areas to be able to find interesting species/year/status-combinations to be able to decide about actions. Just tried to build some views manually within the GUI (so without the Query Composer) and these procedures here http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html do not work at all. Seem to be outdated or what, errors over errors and in the end I have geometryless geometries ... perfect. I simply can't believe that none on this planet ever decided to post a simple and working spatialite view creation script for 1:n somewhere in the web as a template. arrghh.. ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Spatialite VIEW in QGIS question
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
Re: [Qgis-user] Spatialite VIEW in QGIS question
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
2014-02-20 16:51 GMT+01:00 Bernd Vogelgesang 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:
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
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
Re: [Qgis-user] Spatialite VIEW in QGIS question
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
Re: [Qgis-user] Spatialite VIEW in QGIS question
Try this -
CREATE VIEW Testview23 AS
SELECT a.PK_UID*1 + 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
Re: [Qgis-user] Spatialite VIEW in QGIS question
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.
OK I misunderstood your problem, I though you had trouble loading the view,
which is not that straightforward...
As Bo Victor said, your view has the problem that it does not have unique
IDs which are necessary to have working spatial views.
But still, it seems your doing something wrong : if the idea is to have all
of the observations as an attribute for each polygon, you must return each
polygon in only one row, and aggregate the related data in that row. If you
don't, you'll end up with duplicated polygons like Bo Victor said, which
will probably be quite unusable...
Maybe you rather need a view looking like :
CREATE VIEW Testview23 AS
SELECT a.ROWID AS ROWID, a.Geometry AS Geometry,
GROUP_CONCAT(b.ora_nachweis_id) AS ora_nachweis_id,
GROUP_CONCAT(b.zahl) AS zahl,
GROUP_CONCAT(b.jahr) AS jahr,
GROUP_CONCAT(b.art) AS art,
GROUP_CONCAT(b.sta) AS sta
FROM ASK_VOEGEL AS a
JOIN ask_art AS b USING (id)
GROUP BY a.ROWID
This way you'll get all of the species (comma separated) as attribute of
the polygon.
Hope it helps this time...
Olivier
2014-02-20 19:34 GMT+01:00 Bernd Vogelgesang bernd.vogelges...@gmx.de:
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:
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:
Am 19.02.2014 19:14, schrieb Steve G:
This does not directly answer your question about spatialite
Re: [Qgis-user] Spatialite VIEW in QGIS question
Hello Bernd, In QGis 1.8 and 1.7, perhaps this will help (exporting *shp* to *csv* with *wkt *geometry, also your observation entries to *csv*, and linking both*; *not very productive, I suppose) http://gis.stackexchange.com/questions/10788/how-to-join-multiple-records-to-single-feature following, http://gis.stackexchange.com/questions/43129/create-a-csv-with-geometry-as-wkt-in-qgis-and-choosing-the-field-delimiter Good luck Pedro Monteiro 2014-02-18 20:29 GMT+00:00 Bernd Vogelgesang bernd.vogelges...@gmx.de: Hi folks, I'm quite desperate, cause I do not seem to understand what I'm doing wrong, or if it's just not possible to do. I have a polygon layer in my spatialite database and a normal table with bird observations. There are many observation entries for each item in the polygons. They share the simple field id. I created dozens of view, following strictly http://www.gaia-gis.it/ spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html (and i REALLY can't find any other tutorials). When i query SELECT * FROM test17 in Spatialite GUI, it shows all the lines with different observation entries for each polygon id, when i load the VIEW in QGIS, it doesn't but duplicates the first matching observation for one polygon over and over. The idea is to quickly identify all observations when selecting a polygon, and then go to the attribute table to see which species are there. Is it possible to create a one-to-many spatial VIEW with Spatialite GUI ? if yes Whats the trick? if no Is QGIS just not able to show the views table correctly? if no Whats the trick? Wasted many days on that now, and time is running away. Please, someone, help Bernd ___ 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 VIEW in QGIS question
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 -- 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 http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Spatialite VIEW in QGIS question
Am 19.02.2014 16:55, schrieb Alex Mandel: On 02/18/2014 12:29 PM, Bernd Vogelgesang wrote: Hi folks, I'm quite desperate, cause I do not seem to understand what I'm doing wrong, or if it's just not possible to do. I have a polygon layer in my spatialite database and a normal table with bird observations. There are many observation entries for each item in the polygons. They share the simple field id. I created dozens of view, following strictly http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html (and i REALLY can't find any other tutorials). When i query SELECT * FROM test17 in Spatialite GUI, it shows all the lines with different observation entries for each polygon id, when i load the VIEW in QGIS, it doesn't but duplicates the first matching observation for one polygon over and over. The idea is to quickly identify all observations when selecting a polygon, and then go to the attribute table to see which species are there. Is it possible to create a one-to-many spatial VIEW with Spatialite GUI ? if yes Whats the trick? if no Is QGIS just not able to show the views table correctly? if no Whats the trick? Wasted many days on that now, and time is running away. Please, someone, help Bernd I usually cheat, and in making my View I do a left join to the attribute table with the geometries. It ends up duplicating the geometries for each match which can be inefficient on a large dataset but works perfectly reasonable most of the time. Then the regular info and select tools return multiple records. Thank you Alex for your answer. I think this combination was the only one I haven't tried yet. But ... the outcome is as depressing as all my other, or even worse. Instead of 37 features i now have 9725, and the attribute table has the same amount of entries instead of some 700. When loading the attribute table, there are only ERROR in the fields, and it took some minutes till I was able to switch to show only selected. And ... surprise surprise, it again showed just the entry of the first species in all of the rows instead of all the individual observations. Guys, this is a very sad topic and I really can't understand how people can work with 1:n data even on the most basic level under these circumstances ... Or doesn't anyone work with 1:n data? Well, the the world I'm living in is full of that ... After nearly one week of trial and error, I am giving up now. Cheers Bernd I do see potential for a plugin that simply executes a filter on one table based on the selection in another table for a pre-defined relationship (maybe there is one already). Thanks, Alex ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Spatialite VIEW in QGIS question
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 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 VIEW in QGIS question
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. Good luck ! Olivier 2014-02-19 19:54 GMT+01:00 Bernd Vogelgesang 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 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
Re: [Qgis-user] Spatialite VIEW in QGIS question
On 02/19/2014 10:43 AM, Bernd Vogelgesang wrote: Am 19.02.2014 16:55, schrieb Alex Mandel: On 02/18/2014 12:29 PM, Bernd Vogelgesang wrote: Hi folks, I'm quite desperate, cause I do not seem to understand what I'm doing wrong, or if it's just not possible to do. I have a polygon layer in my spatialite database and a normal table with bird observations. There are many observation entries for each item in the polygons. They share the simple field id. I created dozens of view, following strictly http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html (and i REALLY can't find any other tutorials). When i query SELECT * FROM test17 in Spatialite GUI, it shows all the lines with different observation entries for each polygon id, when i load the VIEW in QGIS, it doesn't but duplicates the first matching observation for one polygon over and over. The idea is to quickly identify all observations when selecting a polygon, and then go to the attribute table to see which species are there. Is it possible to create a one-to-many spatial VIEW with Spatialite GUI ? if yes Whats the trick? if no Is QGIS just not able to show the views table correctly? if no Whats the trick? Wasted many days on that now, and time is running away. Please, someone, help Bernd I usually cheat, and in making my View I do a left join to the attribute table with the geometries. It ends up duplicating the geometries for each match which can be inefficient on a large dataset but works perfectly reasonable most of the time. Then the regular info and select tools return multiple records. Thank you Alex for your answer. I think this combination was the only one I haven't tried yet. But ... the outcome is as depressing as all my other, or even worse. Instead of 37 features i now have 9725, and the attribute table has the same amount of entries instead of some 700. When loading the attribute table, there are only ERROR in the fields, and it took some minutes till I was able to switch to show only selected. And ... surprise surprise, it again showed just the entry of the first species in all of the rows instead of all the individual observations. Guys, this is a very sad topic and I really can't understand how people can work with 1:n data even on the most basic level under these circumstances ... Or doesn't anyone work with 1:n data? Well, the the world I'm living in is full of that ... After nearly one week of trial and error, I am giving up now. Cheers Bernd I rarely need to visually explore the 1:n without aggregation. What I love about spatial databases is that I can easily query whatever I want including 1:n but I don't need to click on an individual. What attribute about the second table do you need to know? How many, average x, or even just a list of ids? If you want the Birds per polygon check out the group concat from sqlite: CREATE View birdlists as SELECT group_concat(species), count(species) FROM table1 JOIN table2 ON table1.id = table2.id GROUP BY polygon This gives you the list of birds per polygon as a view of the polygon layer. Yes occasionally you want to click and get a list, I've done this in a web page where you take the id, then query the db to return the rows related. Like I mentioned before a python plugin should be able to do this. Thanks, Alex ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Spatialite VIEW in QGIS question
Maybe I'm unclear on the issue. If it's just how to get a view with geometry to show up, I've found you often have register view with spatialite metadata: INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column) VALUES (theview, thegeometry, view_rowid, tablenamethegeomcomesfrom, the_geometry_column); Thanks, Alex On 02/19/2014 11:02 AM, Olivier Dalang wrote: 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. Good luck ! Olivier 2014-02-19 19:54 GMT+01:00 Bernd Vogelgesang 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 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 ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Spatialite VIEW in QGIS question
Bernd Vogelgesang bernd.vogelgesang@... writes: Guys, this is a very sad topic and I really can't understand how people can work with 1:n data even on the most basic level under these circumstances ... Or doesn't anyone work with 1:n data? Well, the the world I'm living in is full of that ... After nearly one week of trial and error, I am giving up now. Hi, GIS it to large extent based on principle one geometry - one set of attributes. The bird observation case would handle easily a situation where each observation has an own point geometry. Your case is rather often handled in two steps: user clicks on a polygon, application gets an ID for the area and sends a query to the database, reads the observations and shows them for the user. I know that BirdLife of Finland has been building some bird observation stuff on top of QGIS and WFS. Maybe you can try to find some contact there. -Jukka Rahkonen- ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Spatialite VIEW in QGIS question
Hi folks, I'm quite desperate, cause I do not seem to understand what I'm doing wrong, or if it's just not possible to do. I have a polygon layer in my spatialite database and a normal table with bird observations. There are many observation entries for each item in the polygons. They share the simple field id. I created dozens of view, following strictly http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/sp-view.html (and i REALLY can't find any other tutorials). When i query SELECT * FROM test17 in Spatialite GUI, it shows all the lines with different observation entries for each polygon id, when i load the VIEW in QGIS, it doesn't but duplicates the first matching observation for one polygon over and over. The idea is to quickly identify all observations when selecting a polygon, and then go to the attribute table to see which species are there. Is it possible to create a one-to-many spatial VIEW with Spatialite GUI ? if yes Whats the trick? if no Is QGIS just not able to show the views table correctly? if no Whats the trick? Wasted many days on that now, and time is running away. Please, someone, help Bernd ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
