Re: [Qgis-user] spatialite with update trigger seems to result in 'unselectable' geometry

[Janneke van Dijk]

Hi Claas,
Indeed that worked Thank you so much. Any idea if it's already in 
the documentation somewhere (in which case I clearly didn't read the 
manual...)? Otherwise I'll include that on my to do list.

Again, thanks,
Janneke

On 08/02/2019 00:47, Claas Leiner wrote:

Hi Janneke

in a similar problem,
it helped to update the spatial index:

-
CREATE TRIGGER update_geom AFTER UPDATE ON watersources BEGIN

UPDATE watersources SET geom = Transform(MakePoint(east, north, 4326), 
32638) WHERE id=1678;

-- I have done this in my trigger
Select recoverSpatialIndex('watersources','geom');
SELECT UpdateLayerStatistics('watersources');

END






___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] spatialite with update trigger seems to result in 'unselectable' geometry

[Claas Leiner]

Hi Janneke

in a similar problem,
it helped to update the spatial index:

-
CREATE TRIGGER update_geom AFTER UPDATE ON watersources BEGIN

UPDATE watersources SET geom = Transform(MakePoint(east, north, 4326), 
32638) WHERE id=1678;

-- I have done this in my trigger
Select recoverSpatialIndex('watersources','geom');
SELECT UpdateLayerStatistics('watersources');

END




--
-
GKG-Kassel - Dr.-Ing. Claas Leiner
QGIS-Support und mehr

Geodatenservice, Kartenwerkstatt &
GIS-Schule Kassel

Wilhelmshöher Allee 304 E
34131 Kassel
Tel. 0561/56013445
claas.lei...@gkg-kassel.de

http://www.gkg-kassel.de

Unterstützen Sie QGIS
QGIS-DE e.V. | http://qgis.de
QGIS Projekt | http://qgis.org/de/site/
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] spatialite layer does not show

[Reginald]

Thanks for the reply,

Jay M Johnson advised me this link:

https://gis.stackexchange.com/questions/131996/shapefile-layers-in-qgis-dont-display-unless-i-zoom-out

Meanwhile I solved the problem by saving the features in a postgresql layer.

Regards,



--
Sent from: http://osgeo-org.1560.x6.nabble.com/QGIS-User-f4125267.html
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] spatialite layer does not show

[Claas Leiner]

Hi Reginald,

Sometimes a invalid spatialIndex is a reason, that spatialite layer does 
not shown.


Open the DB-manager in QGIS
Klick of your database in the tree and and then open the SQL-Windows 
(press F2).


execute this statement in the SQL-Windows of the DB-Manager:
select recoverspatialindex('tablename','geom');

after that execute:
select updatelayerstatistics('tablename');

tablename = name of the spatialite layer
geom= name of the geometry column

Claas


--
-
GKG-Kassel - Dr.-Ing. Claas Leiner
QGIS-Support und mehr

Geodatenservice, Kartenwerkstatt &
GIS-Schule Kassel

Wilhelmshöher Allee 304 E
34131 Kassel
Tel. 0561/56013445
claas.lei...@gkg-kassel.de
http://www.gkg-kassel.de

Unterstützen Sie QGIS
QGIS-DE e.V. | http://qgis.de
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] spatialite layer does not show

[Reginald]

Update,

In the logmessages I found this message:

2018-12-19T13:34:13 WARNINGKon geen stijlen laden uit
F:/QGIS/dev/notarisattest/inlichtingen.sqlite (Query: SELECT styleQML FROM
layer_styles WHERE f_table_schema IS NULL AND f_table_name='uittreksels' AND
f_geometry_column ='geom' ORDER BY CASE WHEN useAsDefault THEN 1 ELSE 2
END,update_time DESC LIMIT 1)

It says it can not load any style from the sqlite database.
Maybe I should save my style in the database?

Any idea?



--
Sent from: http://osgeo-org.1560.x6.nabble.com/QGIS-User-f4125267.html
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] SpatiaLite data rendered slowly

[Dan Lyke]

On Mon, Sep 14, 2015 at 7:55 AM, Árni Geirsson  wrote:
> One observation regarding this problem: Both the shapefile and SpatiaLite
> database are on a NAS box. If I move the SpatiaLite file to a local drive,
> it renders instantly like the shapefile. So the question is: What is it
> about being stored on a NAS box that throttles down the speed of SpatiaLite
> but does not affect shapefiles?

It is likely that in processing your query, SpatiaLite is doing a
*lot* of individual disk I/O, where the Shapefile read is happening as
a few large I/O operations.

If you need the database on a distributed server, you're probably way
better off using PostGIS, where all of the disk I/O can occur on a
device local to the query computing and then get sent back as one
large chunk, rather than that I/O having to occur over a network, with
network semantics (especially if there's any file locking happening).
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] SpatiaLite data rendered slowly

[Árni Geirsson]

One observation regarding this problem: Both the shapefile and SpatiaLite
database are on a NAS box. If I move the SpatiaLite file to a local drive,
it renders instantly like the shapefile. So the question is: What is it
about being stored on a NAS box that throttles down the speed of SpatiaLite
but does not affect shapefiles?

Árni


Árni Geirsson
*Alta ehf* // +354 582 5000 // +354 897 9549
www.alta.is  // Alta á Twitter  // Alta á
Facebook 

On Mon, Sep 14, 2015 at 1:56 PM, Árni Geirsson  wrote:

> Hi
> I have polygon data in a shapefile that is rendered instantly on the
> screen when I zoom and pan. Now I want to use the more advanced
> capabilities of SpatiaLite and I have loaded the data into a SpatiaLite
> database. Rendering the SpatiaLite data on the screen is much slower. The
> polygons appear one by one and refreshing the map takes several seconds.
> There are about 19.000 fairly simple polygons in the table, which should
> not be very demanding.  I have tried loading the data into SpatiaLite
> both with the DB manager and the SpatiaLite-GUI application with the same
> slow result.
> I tried loading the same data into PostGIS on localhost and that is as
> quick as the shapefile.
> I have created a spatial index on the SpatiaLite table but that did not
> improve the slow rendering.
>
> What am I doing wrong?
>
> Thanks
>
> Árni
>
> Árni Geirsson
> *Alta ehf* // +354 582 5000 // +354 897 9549
> www.alta.is  // Alta á Twitter  // Alta á
> Facebook 
>
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite queries return text instead of numeric values

[Alex Mandel]

What happens if you CREATE VIEW with those tests?
Does QGIS read the VIEW differently?

Thanks,
Alex

On 12/10/2014 10:57 AM, Alexandre Neto wrote:
> Hello all,
> 
> To make some tests I have the folllowing table:
> 
> General info
> 
> 
>   Relation type:
> 
> Table
> 
> Rows:
> 
> 3
> 
> SpatiaLite
> 
> 
>   Column:
> 
> geometry
> 
> Geometry:
> 
> MULTIPOLYGON
> 
> Dimension:
> 
> XY
> 
> Spatial ref:
> 
> WGS 84 (4326)
> 
> Extent:
> 
> (unknown) (find out )
> 
> Fields
> 
> 
>   #
> 
> Name
> 
> Type
> 
> Null
> 
> Default
> 
> 0
> 
> pkuid
> 
> integer
> 
> Y
> 
> 
> 
> 1
> 
> coluna1
> 
> text
> 
> Y
> 
> 
> 
> 2
> 
> coluna2
> 
> real
> 
> Y
> 
> 
> 
> 3
> 
> coluna3
> 
> integer
> 
> Y
> 
> 
> 
> 4
> 
> geometry
> 
> MULTIPOLYGON
> 
> Y
> 
> 
> 
> 
> If I do this:
> 
> SELECT
>*,
> coluna2/1.0 AS test_field
> FROM
> testes
> 
> when I open it with DBmanager the test_field return a QSTRING with type
> name TEXT
> 
> http://imgur.com/GHeMyiW
> 
> Using cast:
> 
>  SELECT
>*,
> cast((coluna2/1.0) as real) AS test_field
> FROM
> testes
> 
> I get the same.
> 
> Thanks for the help.
> 
> Alexandre Neto
> 
> 
> On Wed, Dec 10, 2014 at 6:16 PM, Alex Mandel 
> wrote:
> 
>> On 12/10/2014 09:55 AM, Goyo wrote:
>>> 2014-12-10 1:50 GMT+01:00 Alexandre Neto :
 Hello,

 When querying a spatialite database in the database manager, if you
>> create
 some calculations (say add two columns values) the resulting column
>> returns
 as TEXT instead of the expected numeric field.

 Is this intended or a bug?

 Trying to cast it as real, double or float did not work either.
>>>
>>> Calculated columns in SQLite do not have a data type, the actual
>>> values in the column do. If you execute this query:
>>>
>>> SELECT "col1" + "col2" AS "thevalue", typeof("col1" + "col2") as
>>> "thetype" FROM "table"
>>>
>>> You should have "thetype"='real' in every row (provided "col1" and
>>> "col2" always contain real values) but the column "thevalue" does not
>>> have a data type assigned.
>>>
>>> I guess QGIS needs to assign a data type to each column and chooses
>>> the more general type when the column does not have one without
>>> looking at the actual values (which could be expensive and also
>>> misleading if the data change).
>>>
>>> Goyo
>>> ___
>>> Qgis-user mailing list
>>> Qgis-user@lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>>
>>
>> There are two ways in SQLITE to get the numeric type you want, you can
>> do a CAST() or you can force it with a multiplication (*1 or *1.0). It's
>> really critical if you do a division to *1.0 somewhere in the statement
>> otherwise you'll only get an integer.
>>
>> Note this is not a QGIS specific thing, it applies to general use of
>> SQLITE.
>>
>> You said you tried a CAST, can you paste what you tried?
>>
>> Thanks,
>> Alex
>> ___
>> 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 queries return text instead of numeric values

[Alexandre Neto]

Hello all,

To make some tests I have the folllowing table:

General info


  Relation type:

Table

Rows:

3

SpatiaLite


  Column:

geometry

Geometry:

MULTIPOLYGON

Dimension:

XY

Spatial ref:

WGS 84 (4326)

Extent:

(unknown) (find out )

Fields


  #

Name

Type

Null

Default

0

pkuid

integer

Y



1

coluna1

text

Y



2

coluna2

real

Y



3

coluna3

integer

Y



4

geometry

MULTIPOLYGON

Y




If I do this:

SELECT
   *,
coluna2/1.0 AS test_field
FROM
testes

when I open it with DBmanager the test_field return a QSTRING with type
name TEXT

http://imgur.com/GHeMyiW

Using cast:

 SELECT
   *,
cast((coluna2/1.0) as real) AS test_field
FROM
testes

I get the same.

Thanks for the help.

Alexandre Neto


On Wed, Dec 10, 2014 at 6:16 PM, Alex Mandel 
wrote:

> On 12/10/2014 09:55 AM, Goyo wrote:
> > 2014-12-10 1:50 GMT+01:00 Alexandre Neto :
> >> Hello,
> >>
> >> When querying a spatialite database in the database manager, if you
> create
> >> some calculations (say add two columns values) the resulting column
> returns
> >> as TEXT instead of the expected numeric field.
> >>
> >> Is this intended or a bug?
> >>
> >> Trying to cast it as real, double or float did not work either.
> >
> > Calculated columns in SQLite do not have a data type, the actual
> > values in the column do. If you execute this query:
> >
> > SELECT "col1" + "col2" AS "thevalue", typeof("col1" + "col2") as
> > "thetype" FROM "table"
> >
> > You should have "thetype"='real' in every row (provided "col1" and
> > "col2" always contain real values) but the column "thevalue" does not
> > have a data type assigned.
> >
> > I guess QGIS needs to assign a data type to each column and chooses
> > the more general type when the column does not have one without
> > looking at the actual values (which could be expensive and also
> > misleading if the data change).
> >
> > Goyo
> > ___
> > Qgis-user mailing list
> > Qgis-user@lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/qgis-user
> >
>
> There are two ways in SQLITE to get the numeric type you want, you can
> do a CAST() or you can force it with a multiplication (*1 or *1.0). It's
> really critical if you do a division to *1.0 somewhere in the statement
> otherwise you'll only get an integer.
>
> Note this is not a QGIS specific thing, it applies to general use of
> SQLITE.
>
> You said you tried a CAST, can you paste what you tried?
>
> Thanks,
> Alex
> ___
> 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 queries return text instead of numeric values

[Alex Mandel]

On 12/10/2014 09:55 AM, Goyo wrote:
> 2014-12-10 1:50 GMT+01:00 Alexandre Neto :
>> Hello,
>>
>> When querying a spatialite database in the database manager, if you create
>> some calculations (say add two columns values) the resulting column returns
>> as TEXT instead of the expected numeric field.
>>
>> Is this intended or a bug?
>>
>> Trying to cast it as real, double or float did not work either.
> 
> Calculated columns in SQLite do not have a data type, the actual
> values in the column do. If you execute this query:
> 
> SELECT "col1" + "col2" AS "thevalue", typeof("col1" + "col2") as
> "thetype" FROM "table"
> 
> You should have "thetype"='real' in every row (provided "col1" and
> "col2" always contain real values) but the column "thevalue" does not
> have a data type assigned.
> 
> I guess QGIS needs to assign a data type to each column and chooses
> the more general type when the column does not have one without
> looking at the actual values (which could be expensive and also
> misleading if the data change).
> 
> Goyo
> ___
> Qgis-user mailing list
> Qgis-user@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user
> 

There are two ways in SQLITE to get the numeric type you want, you can
do a CAST() or you can force it with a multiplication (*1 or *1.0). It's
really critical if you do a division to *1.0 somewhere in the statement
otherwise you'll only get an integer.

Note this is not a QGIS specific thing, it applies to general use of SQLITE.

You said you tried a CAST, can you paste what you tried?

Thanks,
Alex
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite queries return text instead of numeric values

[Goyo]

2014-12-10 1:50 GMT+01:00 Alexandre Neto :
> Hello,
>
> When querying a spatialite database in the database manager, if you create
> some calculations (say add two columns values) the resulting column returns
> as TEXT instead of the expected numeric field.
>
> Is this intended or a bug?
>
> Trying to cast it as real, double or float did not work either.

Calculated columns in SQLite do not have a data type, the actual
values in the column do. If you execute this query:

SELECT "col1" + "col2" AS "thevalue", typeof("col1" + "col2") as
"thetype" FROM "table"

You should have "thetype"='real' in every row (provided "col1" and
"col2" always contain real values) but the column "thevalue" does not
have a data type assigned.

I guess QGIS needs to assign a data type to each column and chooses
the more general type when the column does not have one without
looking at the actual values (which could be expensive and also
misleading if the data change).

Goyo
___
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]

Am 21.02.2014, 22:17 Uhr, schrieb Bo Victor Thomsen :
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, following strictly
h

Re: [Qgis-user] Spatialite VIEW in QGIS question

[Bo Victor Thomsen]

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

[Bernd Vogelgesang]

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

[Olivier Dalang]

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

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

Re: [Qgis-user] Spatialite VIEW in QGIS question

[Bo Victor Thomsen]

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

Re: [Qgis-user] Spatialite VIEW in QGIS question

[Bernd Vogelgesang]

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

>
> 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 
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 
>>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>>
>> ___
>> Qgis-user mailing list
>> Qgis-user@lists.osgeo.org 
>> http://lists.osgeo.org/mailman/l

Re: [Qgis-user] Spatialite VIEW in QGIS question

[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


2014-02-20 16:51 GMT+01:00 Bernd Vogelgesang :
>
> 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 :
>>
>> 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

[Bernd Vogelgesang]

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


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

[Bernd Vogelgesang]

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

[Jukka Rahkonen]

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

Re: [Qgis-user] Spatialite VIEW in QGIS question

[Alex Mandel]

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

[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




___
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

[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.

Good luck !

Olivier





2014-02-19 19:54 GMT+01:00 Bernd Vogelgesang :

> 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

[Bernd Vogelgesang]

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

[Bernd Vogelgesang]

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

[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



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

[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.

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

[Pedro Machado Monteiro]

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 :

> 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 : problems with spatial views

[Micha Silver]

  
  
Notice that you have duplicate ROWID
  values in your view results. Spatialite (and DB manager) don't
  care about this since the ROWID is more a convenience. But for
  QGIS it's critical that this column be like a unique primary key.
  
  This is happening because you have GROUP BY with two columns in
  your query. I think that what will resolve this is creating a new
  integer column with unique values for each Fortas-rusis pair. 
  
  If you could try this: add to both the siksnosparniai_rusis table
  and the siksnosparniai_patalpa tables  additional integer columns
  with unique values for each Fortas and unique values for each
  rusis. Then in your view, combine these two integers in some way
  to get a new *unique* value to be used as the ROWID. That should
  work out the duplicate error values in QGIS.
  
  Good luck,
  Micha
  
  
  
  
  On 05/02/2014 09:25, giedrius wrote:


  Hello, 

I have a problem with the display of tables add to QGIS from spatialite. 
DB Manager displays the correct data, but uploading to QGIS table entries
changed to false: 
http://i.imgur.com/Pahw13c.png
http://i.imgur.com/9Y1zaq4.png

When loaded data export to shapfile then show the correct data: 
http://i.imgur.com/ZeXk6Xz.png

Is this a bug? Is it my fault as a user? 

I work with Debian

VAM_test_s.sqlite
  



--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Spatialite-problems-with-spatial-views-tp5036088p5101983.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

This mail was received via Mail-SeCure System.






-- 
Micha Silver
GIS Consulting
052-3665918
http://www.surfaces.co.il

  

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite : problems with spatial views

[giedrius]

Hello, 

I have a problem with the display of tables add to QGIS from spatialite. 
DB Manager displays the correct data, but uploading to QGIS table entries
changed to false: 
http://i.imgur.com/Pahw13c.png
http://i.imgur.com/9Y1zaq4.png

When loaded data export to shapfile then show the correct data: 
http://i.imgur.com/ZeXk6Xz.png

Is this a bug? Is it my fault as a user? 

I work with Debian

VAM_test_s.sqlite
  



--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/Spatialite-problems-with-spatial-views-tp5036088p5101983.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 - can't type fields of a view

[Régis Haubourg]

Hi all, 
I posted my question on sqlite lists, and unfortunatly, nothing has changed
in future releases. 
As anyone been dealing with sqlite dev's. I think this issue needs some love
from us.

See the Answer I receive:

> -Message d'origine-
> De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] De la part de HAUBOURG
> Envoyé : lundi 17 juin 2013 09:59
> À : 'General Discussion of SQLite Database'
> Objet : Re: [sqlite] Computed columns in VIEWs return NULL but should be
> able to be typed! Any ideas?
> 
> Thanks Stephan for your feedback. Bad news for me.
> I see two solutions:
> - hire a dev for this. I'm not following enough sqlite lists to estimate
> if this is
> feasible. Any opinion?
> - hire a QGIS dev to hack a autodetection of type using content of fields.
> Ugly, probably slow and prone to errors.. But feasable since I have some
> devs.
> 
> Cheers,
> Régis
> 
> 
> Cordialement,
> Régis Haubourg
> 
> Régis Haubourg

> 
> 
> > -Message d'origine-
> > De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] De la part de Stefan Keller Envoyé : samedi 15
> > juin 2013 23:09 À : General Discussion of SQLite Database Objet : Re:
> > [sqlite] Computed columns in VIEWs return NULL but should be able to
> > be typed! Any ideas?
> >
> > Hi Régis
> >
> > I'd wish to give you a solution but I'm sorry to have new news about
> > that issue.
> >
> > I'd be happy if there are any SQlite devs around to give you a
> > solution in order to make this database more usable (an more SQL
> compatible).
> >
> > Yours, Stefan
> >
> >
> > 2013/6/11 regish :
> > > Hi all,
> > > I'm starting to use SQLITE in GIS use cases. I'm facing this view
> > > typing column issue, which prevent my favourite client from
> > > interpreting correctly numeric data types.  I'm using QGIS, so I
> > > won't be able to map numeric values (they fall back as text values).
> > > Is there anything new since 2010?  If not, should I suggest Qgis
> > > Devs to hack the field type detection for views?
> > > Régis
> > >
> > >
> > >
> > > --
> > > View this message in context:
> > > http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-
> return
> > > -
> > N
> > > ULL-but-should-be-able-to-be-typed-Any-ideas-tp56769p69350.html
> > > Sent from the SQLite mailing list archive at Nabble.com.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-us...@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-us...@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/Spatialite-can-t-type-fields-of-a-view-tp5058436p5060521.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 views

[Antonio Vico]

Hi,

I get problems in spatialite views too. I created views in spatialitegui 
follow the same instructions and I can open the view in qgis but if I 
try to open Attribute table, in all values get "ERROR" (in qgis ubuntu 
12.04 from http://qgis.org/debian-nightly and in osgeo4w (1.8 and 
1.9-dev versions).


I posted this question in a previous post but only Goyo commented, and I 
can't get a solution.


Sorry if this not solve your problem.

On 10/06/13 14:01, Andreas Neumann wrote:

Hi,

I wanted to create a view with spatial data in SpatiaLite. I can create
the view by hand in DB-Manager and I created entries in the
"views_geometry_columns" table.

But when I add the view it is always added as a non-geometry table
containing only attributes but no geometry.

Am I missing additional entries in some metadata table?

I tried to follow the instructions here:
http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/sp-view.html -
but it does not work as described here.

I can add the table from the view but all I get is no geometry and
"Error" content in every cell.

I am using the current QGIS master.

Thank you if you have any idea how to correctly manage views in SpatiaLite.

Andreas
___
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 views

[Goyo]

2013/6/10 Andreas Neumann :
> Hi,
>
> I wanted to create a view with spatial data in SpatiaLite. I can create
> the view by hand in DB-Manager and I created entries in the
> "views_geometry_columns" table.
>
> But when I add the view it is always added as a non-geometry table
> containing only attributes but no geometry.

Try qspatialite plugin. AFAIK it it is the only straightforward way to
add spatial views. Using other plugins (DB manager, spatialite
manager, Fast SQL query) you have to use a query and specify the
primary key and the geometry column again.

Goyo
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite - can't type fields of a view

[Régis Haubourg]

I found that:
http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-NULL-but-should-be-able-to-be-typed-Any-ideas-td56769.html#a56770

  

Its dated from 2010, anyone knows if this has changed ?  Problem occurs with
computed columns. 
Workaround would need to modify provider to let it autodetect column type
based on content (unefficient) :


I must point out that this is a great limitation to usage of spatialite when
trying to have graduated classifications. 

Régis



--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/Spatialite-can-t-type-fields-of-a-view-tp5058436p5058633.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 Version 4.0

[Micha Silver]

  
  
On 4/30/2013 8:11 PM, Alex Mandel
  wrote:

On 04/30/2013 10:00 AM, Goyo wrote:
  
  2013/4/30 Alex Mandel
:


Are you sure it's not already in those
  nightly
  
  builds? QGIS-Dev package in OSGeo4w.
  


The problem is not the provider (which is already there) but the

spatialite version QGIS is built against. It's 3.0.1 in OSGeo4W
(so I

think) and 3.1.0-rc2 in Ubuntu 13.04 and Debian sid.


Goyo

  
  


Yes, I updated some Win machines today, and got qgis Master:

QGIS version    1.9.0-Master    QGIS code revision    6e90ba3


but it's built against: 
SpatiaLite Version    3.0.1

Then it's a packaging issue, that would require
  tickets to be filed with OSGeo4w, UbuntuGIS, and Debian GIS.
  
  


I added :
http://trac.osgeo.org/osgeo4w/ticket/360
but then I noticed that the issue has already been discussed:
http://trac.osgeo.org/osgeo4w/ticket/323

Best,
Micha
Thanks,
  
  Alex
  
  
  ___
  
  Qgis-user mailing list
  
  Qgis-user@lists.osgeo.org
  
  http://lists.osgeo.org/mailman/listinfo/qgis-user
  
  
  This mail was received via Mail-SeCure System.
  
  
  



  

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite Version 4.0

[Alex Mandel]

On 04/30/2013 10:00 AM, Goyo wrote:

2013/4/30 Alex Mandel :


Are you sure it's not already in those nightly
builds? QGIS-Dev package in OSGeo4w.


The problem is not the provider (which is already there) but the
spatialite version QGIS is built against. It's 3.0.1 in OSGeo4W (so I
think) and 3.1.0-rc2 in Ubuntu 13.04 and Debian sid.

Goyo


Then it's a packaging issue, that would require tickets to be filed with 
OSGeo4w, UbuntuGIS, and Debian GIS.


Thanks,
Alex

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite Version 4.0

[Goyo]

2013/4/30 Alex Mandel :

> Are you sure it's not already in those nightly
> builds? QGIS-Dev package in OSGeo4w.

The problem is not the provider (which is already there) but the
spatialite version QGIS is built against. It's 3.0.1 in OSGeo4W (so I
think) and 3.1.0-rc2 in Ubuntu 13.04 and Debian sid.

Goyo
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite Version 4.0

[Alex Mandel]

On 04/30/2013 07:58 AM, Micha Silver wrote:

Hello All:
Spatialite stable is at version 4.0. [1] for some months now.
Sandro Furieri has put the new QGIS data provider into QGIS trunk [2], also a
while back. Any idea when windows builds of either 1.8.xor 1.9.0 will
incorporate that new data provider?


1.8.x unlikely if ever, there are no plans for a 1.8.x release.

1.9.x dev (soon to be 2.0) Are you sure it's not already in those 
nightly builds? QGIS-Dev package in OSGeo4w.


The next stable release that will contain it is 2.0 which is the next 
planned release. We have already entered feature freeze for that 
release. After a few months of bug fixing hopefully it will be ready.


Yes it has been a long time since a stable release, the core development 
team decided to spend effort on 2.0 instead in order to get it done faster.


Thanks,
Alex

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite : problems with spatial views

[Samy Mezani]

le 22/02/2013 13:21, Paolo Cavallini a écrit:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Il 22/02/2013 13:07, Samy Mezani ha scritto:


So Qspatialite should integrate a creation of a rowid to load correctly a 
spatial
view in Qgis.
DB Manager continue to not load the geometry even if a rowid is created.


what happens exactly?


In DB Manager, 'info' and 'table' tabs of the spatial view are OK but no 
preview is available. (And if a spatial index is created, no preview is 
available for a spatialite table)
When adding the spatial view to QGis canvas, and after 30s, a table is 
added as a qgis layer.

In Properties, and 'Metadata' tab : type of geometry... : no geometry
In 'fields' tab, the 'geometry' layer name with 'BLOB' type is present.
But no feature visible on the canvas.

Samy


___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite : problems with spatial views

[Samy Mezani]

le 22/02/2013 13:19, Alexandre Neto a écrit:



On Fri, Feb 22, 2013 at 12:07 PM, Samy Mezani mailto:samy.mez...@wanadoo.fr>> wrote:



2. After adding a rowid, the attributes window show all the values
of features but I have to wait 70s for 44 features and CPU used at 100%.


How long does your query takes to perform outside QGIS?


In Qspatialite, only 3s.

Samy
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite : problems with spatial views

[Paolo Cavallini]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Il 22/02/2013 13:07, Samy Mezani ha scritto:

> So Qspatialite should integrate a creation of a rowid to load correctly a 
> spatial
> view in Qgis.
> DB Manager continue to not load the geometry even if a rowid is created.

what happens exactly?
all the best.
- -- 
Paolo Cavallini - Faunalia
www.faunalia.eu
Full contact details at www.faunalia.eu/pc
Nuovi corsi QGIS e PostGIS: http://www.faunalia.it/calendario
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlEnYrYACgkQ/NedwLUzIr6wBgCfQ3QgUWZ1JfHGjljuaSxlr5T8
FnYAoLHINSc8+Bf407k6ilkzo1xrRRQv
=Mpch
-END PGP SIGNATURE-
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite : problems with spatial views

[Alexandre Neto]

On Fri, Feb 22, 2013 at 12:07 PM, Samy Mezani wrote:

> le 22/02/2013 11:57, Alexandre Neto a écrit:
>
>  There, I found out that, for correctly open a spatialite spatial view in
>> QGIS, I would need to have a column named ROWID.
>>
>
> Thanks Alexandre,
>
> My problems are partially resolved with your workaround :
>
> 1. The spatial index still blocks the load of geometry in QGis. I had to
> remove it before.
>
> 2. After adding a rowid, the attributes window show all the values of
> features but I have to wait 70s for 44 features and CPU used at 100%.
>

How long does your query takes to perform outside QGIS?


>
> 3. After adding a rowid, all labels are created.
>
> So Qspatialite should integrate a creation of a rowid to load correctly a
> spatial view in Qgis.
> DB Manager continue to not load the geometry even if a rowid is created.
>
> Thanks a lot.
>
>
> Samy
> __**_
> 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 : problems with spatial views

[Samy Mezani]

le 22/02/2013 11:57, Alexandre Neto a écrit:

There, I found out that, for correctly open a spatialite spatial view in
QGIS, I would need to have a column named ROWID.


Thanks Alexandre,

My problems are partially resolved with your workaround :

1. The spatial index still blocks the load of geometry in QGis. I had to 
remove it before.


2. After adding a rowid, the attributes window show all the values of 
features but I have to wait 70s for 44 features and CPU used at 100%.


3. After adding a rowid, all labels are created.

So Qspatialite should integrate a creation of a rowid to load correctly 
a spatial view in Qgis.

DB Manager continue to not load the geometry even if a rowid is created.

Thanks a lot.

Samy
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite : problems with spatial views

[Alexandre Neto]

I had the same problem as you, all attributes showed ERROR in the attribute
table. I asked here:

http://gis.stackexchange.com/questions/32592/how-to-open-a-spatialite-non-spatial-view-in-qgis

And was directed to the following page on Spatialite
Cookbook.
There, I found out that, for correctly open a spatialite spatial view in
QGIS, I would need to have a column named ROWID.

See if it helps.

Alexandre Neto

On Fri, Feb 22, 2013 at 10:45 AM, Samy Mezani wrote:

> le 22/02/2013 11:32, Paolo Cavallini a écrit:
>
>  -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> Il 22/02/2013 10:55, Samy Mezani ha scritto:
>>
>>> Hello all,
>>>
>>> When I load a valid spatial view from Qspatialite in QGis, I have some
>>> problems :
>>>
>>
>> have you tried with DB Manager?
>>
>
>
> Yes, but the geometry is not loaded in QGis with DB Manager. There is
> "just" a data table, and the attributes window show "ERROR" too (but it's
> very slow and qgis use 99% of CPU).
>
> Thanks.
>
> Samy
>
> __**_
> 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 : problems with spatial views

[Samy Mezani]

le 22/02/2013 11:32, Paolo Cavallini a écrit:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Il 22/02/2013 10:55, Samy Mezani ha scritto:

Hello all,

When I load a valid spatial view from Qspatialite in QGis, I have some problems 
:


have you tried with DB Manager?



Yes, but the geometry is not loaded in QGis with DB Manager. There is 
"just" a data table, and the attributes window show "ERROR" too (but 
it's very slow and qgis use 99% of CPU).


Thanks.

Samy
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite : problems with spatial views

[Paolo Cavallini]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Il 22/02/2013 10:55, Samy Mezani ha scritto:
> Hello all,
> 
> When I load a valid spatial view from Qspatialite in QGis, I have some 
> problems :

have you tried with DB Manager?
all the best.

- -- 
Paolo Cavallini - Faunalia
www.faunalia.eu
Full contact details at www.faunalia.eu/pc
Nuovi corsi QGIS e PostGIS: http://www.faunalia.it/calendario
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlEnSVEACgkQ/NedwLUzIr6OhQCdHkZvW8qvQN66EetISnQCHx0t
k3MAnRq650PVNqML1N49R+Zi8Xk/kRbH
=pP1K
-END PGP SIGNATURE-
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] SpatiaLite creation problem

[Micha Silver]

  
  
On 27/06/2012 16:59, Julian Bogdani
  wrote:

Hello everybody,
  I'm using the new 1.8 version and maybe I'm missing
something, but I was not able to create a new spatialite archive
with spatial tables in it.
  I'm able to create the database file using Qspatialite
plugin, but i wan not able to create e new table with the
plugin.


Did you try Layer->New->New spatialite layer. This seems to
work quite well now.


  After creating the db file I triead adding to is a spatial
table; I defined the attributes and added a geometry column
(tried polygon and line types), and table creation was
successful, but the table was not recognized, as a spatial table
and I was not able to draw geometries int it once loaded in
QGIS.
  Is this a bug? What is the best procedure to create a
spatialite table?

  



  

  
  -
  Julian Bogdani
  jbogd...@gmail.com
  http://bradypus.net

  

  

  

  


  
  
  
  This mail was received via Mail-SeCure System.
  
  
  
  ___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

This mail was received via Mail-SeCure System.






-- 
Micha Silver
052-3665918

  

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite data portability

[Noli Sicad]

Most of  SQLite editors, if not all could not read...

Noli

On 3/16/12, Noli Sicad  wrote:
> Most of  SQLite editors, if not could not read the spatial aspect of
> the spatailite database file.
>
> Use spatialite-gui, spatialite CLI, and QGIS spatialite plugins, if
> you want to manage the spatial aspect of your spatialite database.
>
> Noli
>
> On 3/16/12, Tyler Mitchell  wrote:
>>>
>>>  "Error while updating cell value; no such function: RTreeAlign".
>>>
>>
>>
>> Sounds like the other SQLite apps you are using can't find the spatial
>> (rtree) libraries.  So, effectively, you are trying to open a db with a
>> different version of SQLite.
>> I'll be curious to know your solution to the problem :)
>>
>> Tyler
>>
>>
>>
>> -
>> Tyler Mitchell, Publisher
>> Locate Press, i...@locatepress.com
>> Open Source "geo" Books
>> http://www.locatepress.com
>>
>>
>>
>>
>>
>>
>
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite data portability

[Noli Sicad]

Most of  SQLite editors, if not could not read the spatial aspect of
the spatailite database file.

Use spatialite-gui, spatialite CLI, and QGIS spatialite plugins, if
you want to manage the spatial aspect of your spatialite database.

Noli

On 3/16/12, Tyler Mitchell  wrote:
>>
>>  "Error while updating cell value; no such function: RTreeAlign".
>>
>
>
> Sounds like the other SQLite apps you are using can't find the spatial
> (rtree) libraries.  So, effectively, you are trying to open a db with a
> different version of SQLite.
> I'll be curious to know your solution to the problem :)
>
> Tyler
>
>
>
> -
> Tyler Mitchell, Publisher
> Locate Press, i...@locatepress.com
> Open Source "geo" Books
> http://www.locatepress.com
>
>
>
>
>
>
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite data portability

[Tyler Mitchell]

> 
>  "Error while updating cell value; no such function: RTreeAlign".
> 


Sounds like the other SQLite apps you are using can't find the spatial (rtree) 
libraries.  So, effectively, you are trying to open a db with a different 
version of SQLite.
I'll be curious to know your solution to the problem :)

Tyler



-
Tyler Mitchell, Publisher
Locate Press, i...@locatepress.com
Open Source "geo" Books
http://www.locatepress.com





___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] Spatialite data portability

[David Fawcett]

I second Andreas.  The Spatialite list is great.

You could also use the spatialite-gui:
https://www.gaia-gis.it/fossil/spatialite_gui/index

David.

On Thu, Mar 15, 2012 at 7:16 AM, Andreas Neumann  wrote:
> Hi Walter,
>
> Unfortunately I can't help. But I suggest that you ask the SpatiaLite cracks
> at their mailinglist:
> http://groups.google.com/group/spatialite-users
>
> I am pretty sure they can help you better than we here.
>
> Good luck!
>
> Andreas
>
>
> On Thu, 15 Mar 2012 12:09:57 +, Walter Ludwick wrote:
>>
>> Following the consensus opinion of all those who kindly answered my
>> inquiry (Re: QGIS Farm Application), i have installed Spatialite and
>> managed to export all the data from a layer to a .SQLITE file, and
>> then view it from several different SQLite browsers, which confirms
>> integrity of data & database structure. This already is a constructive
>> step forward (thanks to all those who helped, too numerous already to
>> mention :).
>>
>> Moreover, i have managed to create a database in LibreOffice based
>> upon that .sqlite file (after (re)learning more than i really wanted
>> to know about ODBC drivers!), which displays all the data as it
>> should. This is another good step forward, because -tho the business
>> of my farm is predicated on geo-located resources (e.g. a paddock on
>> the ground) having certain attributes that are best defined in QGIS
>> (e.g. perimeter, area, etc.)- i will still be working with that data
>> in spreadsheets on a much more dynamic basis.
>>
>> However: When i try to edit those .sqlite files from QGIS in another
>> tool, i'm running into an error message that varies slightly,
>> depending on the editor i use (anything but OO at this point: ODBC is
>> too much pain, until i get these other mechanics worked out), but the
>> message is essentially this (from SQLiteStudio):
>>      "Error while updating cell value; no such function: RTreeAlign".
>>
>> Presuming that what i am trying to do is in fact possible*;  then i
>> am left scratching my head as to why all these SQL tools are having
>> this trouble, and where that problematic function is coming from. Does
>> that error message suggest anything to you?
>>
>> * (Of course, if what i am trying to do with such round-trip editing
>> is in fact not possible, i would sure appreciate if someone could let
>> me know :)
>>
>> /wl
>> ___
>> Qgis-user mailing list
>> Qgis-user@lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>
>
> --
> --
> Andreas Neumann
> Böschacherstrasse 10A
> 8624 Grüt (Gossau ZH)
> Switzerland
>
> ___
> 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 data portability

[Andreas Neumann]

Hi Walter,

Unfortunately I can't help. But I suggest that you ask the SpatiaLite 
cracks at their mailinglist:

http://groups.google.com/group/spatialite-users

I am pretty sure they can help you better than we here.

Good luck!

Andreas

On Thu, 15 Mar 2012 12:09:57 +, Walter Ludwick wrote:

Following the consensus opinion of all those who kindly answered my
inquiry (Re: QGIS Farm Application), i have installed Spatialite and
managed to export all the data from a layer to a .SQLITE file, and
then view it from several different SQLite browsers, which confirms
integrity of data & database structure. This already is a 
constructive

step forward (thanks to all those who helped, too numerous already to
mention :).

Moreover, i have managed to create a database in LibreOffice based
upon that .sqlite file (after (re)learning more than i really wanted
to know about ODBC drivers!), which displays all the data as it
should. This is another good step forward, because -tho the business
of my farm is predicated on geo-located resources (e.g. a paddock on
the ground) having certain attributes that are best defined in QGIS
(e.g. perimeter, area, etc.)- i will still be working with that data
in spreadsheets on a much more dynamic basis.

However: When i try to edit those .sqlite files from QGIS in another
tool, i'm running into an error message that varies slightly,
depending on the editor i use (anything but OO at this point: ODBC is
too much pain, until i get these other mechanics worked out), but the
message is essentially this (from SQLiteStudio):
  "Error while updating cell value; no such function: 
RTreeAlign".


Presuming that what i am trying to do is in fact possible*;  then i
am left scratching my head as to why all these SQL tools are having
this trouble, and where that problematic function is coming from. 
Does

that error message suggest anything to you?

* (Of course, if what i am trying to do with such round-trip editing
is in fact not possible, i would sure appreciate if someone could let
me know :)

/wl
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user


--
--
Andreas Neumann
Böschacherstrasse 10A
8624 Grüt (Gossau ZH)
Switzerland
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [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
 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

[Ricardo Filipe Soares Garcia da]

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

[Jürgen E . Fischer]

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

[Giuseppe Sucameli]

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

[Carson Farmer]

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

Re: [Qgis-user] spatialite and ftools

[Lionel Roubeyrie]

Hi Carson,
sorry for the lack of information. I use QGIS 1.3.0 on ubuntu 9.10, with 
spatialite 1.3.0. I want to retrieve the intersections between the Highways 
layer (lines) and the Regions layer (polygons), but the intersection ftools tool 
doesn't complaint and gives an empty result layer. Lauching qgis in a console 
and calling ftools I get : "Critical: Invalid current SQLite statement" two 
times, nothing else...


Carson Farmer a écrit :

Hi Lionel,

Hi all,
Can't apply some vectors operations via the ftools toolbox (intersect, 
convex hull, ...) on layers imported from a spatialite db (the 
test-2.3.sqlite available at 
http://www.gaia-gis.it/spatialite/resources.html).

Is it normal?
Shouldn't be ;-) Could you please supply an error message or description 
of exactly what is going wrong?


Thanks,

Carson



--
Lionel Roubeyrie - lroubey...@limair.asso.fr
Chargé d'études
LIMAIR - la Surveillance de l'Air en Limousin
http://www.limair.asso.fr



___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] spatialite and ftools

[Carson Farmer]

Hi Lionel,

Hi all,
Can't apply some vectors operations via the ftools toolbox (intersect, 
convex hull, ...) on layers imported from a spatialite db (the 
test-2.3.sqlite available at 
http://www.gaia-gis.it/spatialite/resources.html).

Is it normal?
Shouldn't be ;-) Could you please supply an error message or description 
of exactly what is going wrong?


Thanks,

Carson

--
Carson J. Q. Farmer
ISSP Doctoral Fellow
National Centre for Geocomputation (NCG),
National University of Ireland, Maynooth,
Email: carson.far...@gmail.com
Web:   http://www.carsonfarmer.com/
  http://www.ftools.ca/

___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] spatialite]

[Lionel Roubeyrie]

The original datas are in a ISO-8859-15 encoded shapefile. In the spatialite-gui 
after import, columns containing accentuated characters are replaced by "" with 
no datas.


Paolo Cavallini a écrit :

Lionel Roubeyrie ha scritto:


but after investigation, it seems spatialite has problems with accentuated
attributes names. Changing them to normal names and qgis can correctly
import
the layer. Strange that spatialite-gui doesn't take care of the encoding
for the
names, just for the values.

Paolo Cavallini a écrit :

Lionel Roubeyrie ha scritto:

Hi all,
can't correctly import a simple polygon table from a spatialite db, all
attributes are equal to "ERROR". The polygons are displayed after the

try upgrading to current trunk, a bug has been fixed recently


Quite strange - Spatialite should be UTF-8 ready.
Investigating...


--
Lionel Roubeyrie - lroubey...@limair.asso.fr
Chargé d'études et de maintenance
LIMAIR - la Surveillance de l'Air en Limousin
http://www.limair.asso.fr



___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] spatialite]

[Paolo Cavallini]

Lionel Roubeyrie ha scritto:

> but after investigation, it seems spatialite has problems with accentuated
> attributes names. Changing them to normal names and qgis can correctly
> import
> the layer. Strange that spatialite-gui doesn't take care of the encoding
> for the
> names, just for the values.
> 
> Paolo Cavallini a écrit :
>> Lionel Roubeyrie ha scritto:
>>> Hi all,
>>> can't correctly import a simple polygon table from a spatialite db, all
>>> attributes are equal to "ERROR". The polygons are displayed after the
>>
>> try upgrading to current trunk, a bug has been fixed recently

Quite strange - Spatialite should be UTF-8 ready.
Investigating...
-- 
Paolo Cavallini: http://www.faunalia.it/pc
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] spatialite]

[Lionel Roubeyrie]

Thanks for the reply Paolo,
but after investigation, it seems spatialite has problems with accentuated
attributes names. Changing them to normal names and qgis can correctly import
the layer. Strange that spatialite-gui doesn't take care of the encoding for the
names, just for the values.

Paolo Cavallini a écrit :

Lionel Roubeyrie ha scritto:

Hi all,
can't correctly import a simple polygon table from a spatialite db, all
attributes are equal to "ERROR". The polygons are displayed after the


try upgrading to current trunk, a bug has been fixed recently



--
Lionel Roubeyrie - lroubey...@limair.asso.fr
Chargé d'études et de maintenance
LIMAIR - la Surveillance de l'Air en Limousin
http://www.limair.asso.fr



--
Lionel Roubeyrie - lroubey...@limair.asso.fr
Chargé d'études et de maintenance
LIMAIR - la Surveillance de l'Air en Limousin
http://www.limair.asso.fr



___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] spatialite

[Paolo Cavallini]

Lionel Roubeyrie ha scritto:
> Hi all,
> can't correctly import a simple polygon table from a spatialite db, all
> attributes are equal to "ERROR". The polygons are displayed after the

> 
try upgrading to current trunk, a bug has been fixed recently

-- 
Paolo Cavallini: http://www.faunalia.it/pc
___
Qgis-user mailing list
Qgis-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user