Spatial view

[gilles.s...@gmail.com]

Hello

Probably a basic question from an absolute beginner...

I'm trying to create a very simple spatial view based on the examples from the cookbook.
I use the example database http://www.gaia-gis.it/spatialite-2.3.1/test-2.3.zip

CREATE VIEW  v_BigTowns AS
SELECT *
FROM Towns
WHERE Peoples > 200000
ORDER BY Peoples DESC;

INSERT INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name, f_geometry_column)
VALUES ('v_BigTowns', 'Geometry', 'PK_UID', 'Towns', 'Geometry');

The view is created, the views_geometry_columns table is also updated but the views still seems to be a simple view without properly recognized geometries (unable to load it into QGIS, icon into Spatialite GUI like the geom_col_ref_sys table not like the other spatial layers).
What am I missing ??? Maybe the values I use are not correct ?

I'm using spatialite 3.1.0-RC2 , Ubuntu 12.04
I also tried to find the query composer from Spatialite GUI (1.2.1) as shown in the cookbook but I never found this query composer ...

Thanks
Gilles

[a.fu...@lqt.it]

Hi Gilles,

here you'll find some useful documentation (and practical examples)
about Spatial Views:

http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/sp-view.html

please note; you are using very obsolete versions of spatialite:
the current library version is 4.0.0 (corresponding to spatialite_gui
1.6.0)

https://www.gaia-gis.it/fossil/libspatialite/index
https://www.gaia-gis.it/fossil/spatialite_gui/index

bye Sandro

--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.

[Gilles San Martin]

Hello and thanks for your quick reply

This is precisely the page I based myself on to try this...
I have read it again and again but I still can't find what is wrong in my SQL ...
The solution is probably evident but I don't see it.

The spatialite version is the one available in the UbuntuGis ppa. I suppose that they didn't updated it yet to avoid compatibility problems with other packages from the ppa (???).

Thanks
Gilles

2013/5/21 <a.fu...@lqt.it>

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/D9A_xn-p7i8/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to spatialite-users+unsubscribe@googlegroups.com.
To post to this group, send email to spatialite-users@googlegroups.com.
Visit this group at http://groups.google.com/group/spatialite-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[Micha Silver]

On 05/21/2013 05:32 PM, Gilles San Martin wrote:

Hello and thanks for your quick reply

This is precisely the page I based myself on to try this...
I have read it again and again but I still can't find what is wrong in my SQL ...
The solution is probably evident but I don't see it.

I believe that QGIS needs a column called ROWID (in addition to the integer primary key). Try to alter your CREATE VIEW statement as:

CREATE VIEW  v_BigTowns AS
SELECT ROWID as ROWID, *

FROM Towns
WHERE Peoples > 200000
ORDER BY Peoples DESC;

then double check views_geometry_columns to make sure the view appears there.
HTH,
Micha

The spatialite version is the one available in the UbuntuGis ppa. I suppose that they didn't updated it yet to avoid compatibility problems with other packages from the ppa (???).

Thanks
Gilles

2013/5/21 <a.fu...@lqt.it>

Hi Gilles,

here you'll find some useful documentation (and practical examples)
about Spatial Views:

http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/sp-view.html

please note; you are using very obsolete versions of spatialite:
the current library version is 4.0.0 (corresponding to spatialite_gui 1.6.0)

https://www.gaia-gis.it/fossil/libspatialite/index
https://www.gaia-gis.it/fossil/spatialite_gui/index

bye Sandro

--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/D9A_xn-p7i8/unsubscribe?hl=en.

To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.

Visit this group at http://groups.google.com/group/spatialite-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To post to this group, send email to spatiali...@googlegroups.com.

[a.fu...@lqt.it]

> I believe that QGIS needs a column called ROWID (in addition to the
> integer primary key).
>

surely yes

> Try to alter your CREATE VIEW statement as:
>
> CREATE VIEW v_BigTowns AS
> SELECT ROWID as ROWID, *
> FROM Towns
> WHERE Peoples > 200000
> ORDER BY Peoples DESC;
>

as a general rule, using the infamous * wildcard should be
carefully avoided in order to get a properly working spatial
view.

explicitly enumerating each single column *and* setting some
appropriate alias name could be probably a boring task, but
actually is the safest way to get a nice Spatial View. e.g.

CREATE VIEW v_BigTowns AS
SELECT ROWID as ROWID, name AS name,
population AS population, country AS country,
elevation AS elevation, geometry AS geometry
FROM Towns
WHERE Peoples > 200000;

(carefully follow the "hand-writing" example given in the
CookBook)

[gilles.s...@gmail.com]

Ok it works ! Many thanks to both of you.

The problem was indeed from QGIS. I can read correctly the view as I first wrote it with ogr (from R).
But Adding a ROWID column allow you to visualize the view from QGIS.
I also also spent 1 hour of unsuccessful trials just because I added "towns" (lowercase) instead of "Towns" to the f_table_name column in the views_geometry_columns table...
It was ok when I used ogr but not with QGIS


But I have now an other closely related problem :
I create a view from a buffer around these points. I can see it in QGIS as a view with a geometry column but it is recognized as points (instead of polygon) and qgis do not display it. Is there a way to handle this (without creating a table) ?
Maybe this is more a question for a QGIS mailing list ? Again I can read/visualize this view correcly as polygons with ogr from R.

CREATE VIEW  v_Towns_buffer AS
            SELECT PK_UID as ROWID, Name as Name, Peoples as Peoples, LocalCounc as LocalCounc,
 County as County, Region as Region, ST_Buffer(Geometry, 30000) AS Geometry

            FROM Towns
            WHERE Peoples > 200000
            ORDER BY Peoples DESC;

INSERT INTO views_geometry_columns
            (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column)

            VALUES ('v_Towns_buffer', 'Geometry', 'ROWID', 'Towns', 'Geometry');

Thanks
Gilles

[a.fu...@lqt.it]

Hi Gilles,

> I also also spent 1 hour of unsuccessful trials just because I added
> "towns" (lowercase) instead of "Towns" to the f_table_name column in
> the views_geometry_columns table...
> It was ok when I used ogr but not with QGIS
>

SQLite always applies case-sensitive string comparisons; so "ABCD"
and "abcd" are considered as two completely different values ;-)

Earlier versions of libspatialite were prone to this kind of mishap;
starting since v.4.0.0:
- both "f_table_name" and "f_geometry_column" are required to always
contain lowercase names (triggers actively enforce such constraints).
- all internal SQL queries accessing "geometry_columns" (and friends)
are now always implemented as case-less comparisons, e.g.:
... WHERE Lower(f_table_name) = Lower('my_table') AND
Lower(f_geometry_column) = Lower('geom')

the same is true for the most recent version of the QGIS-splatialite
data-provider as well; so you can reasonably expect that such issues
will be completely resolved once for all when the newest versions of
libspatialite and QGIS will become widely adopted.

> But I have now an other closely related problem :
> I create a view from a buffer around these points. I can see it in
> QGIS as a view with a geometry column but it is recognized as points
> (instead of polygon) and qgis do not display it. Is there a way to
> handle this (without creating a table) ?
>

Sorry, no.

Just a quick rationale: QGIS (as many others GIS desktop apps)
absolutely requires that each layer should be fully qualified
by specifying the appropriate Srid, geometry-type and dimensions.
If Geometries actually returned from the datasource does not
match the layer declaration they'll be simply discarded.

In the very special case of Spatial Views the layer's definitions
are directly taken from the corresponding input Geometry; BTW this
fully explains why we absolutely need to register Spatial Views in
"view_geometry_columns", so to keep full trace of the real
origin of Geometries returned by the View itself.

But when you introduce some Spatial Function in your View (e.g. Buffer,
Transform and alike) geometry-type, srid or dimensions could change,
and consequently the returned Geometries will not match any longer
the expected values declared when creating the layer.

There is absolutely no way allowing to "guess" on the fly the exact
type of Geometries returned by an arbitrary View internally using
Spatial SQL Functions.
In this case the unique reasonable strategy should be the one to
query the whole View in advance then attempting to "sniff" the best
fit layer declaration; but such an approach will obviously be deadly
slow for a big-sized input View returning many million rows (and may
be,
internally inoking some very complex and intrinsically slow SQL Spatial
Operator), so doesn't seems to be a reasonable approach.

[Gilles San Martin]

Thanks a lot for your complete explanations. Very usefull !

I keep all this in mind.

Best regards

Gilles

2013/5/24 <a.fu...@lqt.it>

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/D9A_xn-p7i8/unsubscribe?hl=en.

To unsubscribe from this group and all its topics, send an email to spatialite-users+unsubscribe@googlegroups.com.
To post to this group, send email to spatialite-users@googlegroups.com.

[Stefan Keller]

Hi Sandro

You answered:

>> But I have now an other closely related problem :

>> I create a view from a buffer around these points. I can see it in

>> QGIS as a view with a geometry column but it is recognized as points

>> (instead of polygon) and qgis do not display it. Is there a way to

>> handle this (without creating a table) ?

>> 

> Sorry, no.

I can't follow your reasoning why it should'nt be possible to have views with differing geometry type as in the original table.

Geometry functions like ST_Centroid(<polygon>) have a defined output geometry type. So there's no need guessing.

If you refer to the fact that every geometry of a row could have a different type, then the same would apply to the original geometry type and a constraint check could enforce it.

I quickly tested the above Towns view example with PostGIS - it works there as expected: 

-- Create view with geometry type point (from ST_Centroid) 

create view v_towns as

select PK_UID as ROWID, "Name" as "Name", ST_Centroid(Geometry) as Geometry

from Towns;

-- (Registering SQL command ommited...)

-- ST_X only works on Points (so Geometry is of geometry type Point)!

select ST_X(Geometry) from Towns;

--> ERROR:  Argument to X() must be a point

select rowid, ST_X(Geometry) from v_towns;

--> OK

select distinct geometrytype(Geometry) from v_towns;

--> All of geometry type Point!

--Stefan

2013/5/24 <a.fu...@lqt.it>

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-users+unsubscribe@googlegroups.com.
To post to this group, send email to spatialite-users@googlegroups.com.

[a.fu...@lqt.it]

On Wed, 13 Nov 2013 02:48:27 +0100, Stefan Keller wrote:
> I can't follow your reasoning why it should'nt be possible to have
> views with differing geometry type as in the original table.
>

Hi Stefan,

I wasn't saying at all that it's not possible to create a plain normal
View returning a different geometry; it's obviously possible, even
using SQLite + SpatiaLite.

What I was exactly saying was that in such circumstances it's not
possible registering a "Spatial View"; and consequently visualizing
(e.g. on QGIS) a View applying any kind of modification of the
original Geometry is not a currently supported option.


> Geometry functions like ST_Centroid() have a defined output geometry

> type. So there's no need guessing.
>

absolutely true, I fully agree with you.

> I quickly tested the above Towns view example with PostGIS - it works
> there as expected:
>
> -- Create view with geometry type point (from ST_Centroid)
>
> create view v_towns as
> select PK_UID as ROWID, "Name" as "Name", ST_Centroid(Geometry) as
> Geometry
> from Towns;
> -- (Registering SQL command ommited...)
>
> -- ST_X only works on Points (so Geometry is of geometry type Point)!
> select ST_X(Geometry) from Towns;
>
> --> ERROR: Argument to X() must be a point
> select rowid, ST_X(Geometry) from v_towns;
>
> --> OK
>
> select distinct geometrytype(Geometry) from v_towns;
>
> --> All of geometry type Point!
>

certainly yes; and exactly the same will happen using SpatiaLite.

This one is not a problem directly related to SQL as such; it's a
problem arising from the QGIS data provider current implementation.

When an user connects any generic vector datasource to QGIS, the
data provider is expected to immediately report to the main app:
- a precisely identified Geometry Type
- a SRID
- a full extent Bounding Box
- if the datasource is read-only or read-write
- if there is any supporting Spatial Index

If the data source is directly supported by some kind of metadata
this is an obviously trivial task just requiring micro-seconds.

If no metadata are available, then the unique viable approach is
the one to perform a full table scan directly exploring the whole
datasource from start to end.
If the generic View to be preliminary checked does actually
return some million features (may be requiring to compute some
computationally heavy SQL function such as ST_Transform,
ST_Buffer or ST_Union, or possibly a mix of all them), then
you'll probably discover that any single connection attempt
will probably require many long minutes, leaving QGIS completely
frozen in the meanwhile.
It doesn't look to be a reasonable design approach.

Using a "registered View" surely has its limitations (you cannot
change the original input geometry in any way), but at least has
the strong advantage that it allows to visualize on QGIS any Spatial
View exactly as it was a read-only Table, this including supporting
an eventually available Spatial Index (inherited from the parent
Geometry).

It certainly isn't a perfect solution covering any possible case,
but it's a reasonable solution useful in many ordinary cases.

bye Sandro

[Stefan Keller]

Hi Sandro

Many thanks for your instant help. I have to check your explanations.

I've had a similar problem with attribute types in Views where the source attribute was text and I wished to get a number (which happens often when source is CSV from OGR). I concluded that this was a problem of SQlite, not QGIS, since the QGIS driver just didn't get the right right attribut type from the SQlite schema.

-- Stefan

2013/11/13 <a.fu...@lqt.it>

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-users+unsubscribe@googlegroups.com.
To post to this group, send email to spatialite-users@googlegroups.com.

Visit this group at http://groups.google.com/group/spatialite-users.

[a.fu...@lqt.it]

> I've had a similar problem with attribute types in Views where the
> source attribute was text and I wished to get a number (which happens
> often when source is CSV from OGR). I concluded that this was a
> problem of SQlite, not QGIS, since the QGIS driver just didn't get
> the
> right right attribut type from the SQlite schema.
>

Hi Stafan,

this one is a very common misconception / misunderstanding about
SQLite.

please note well: SQLite has a very strong specific difference from
any other DBMS; it has no data-types at all :-D
and it's not a "defect"; it's am explicit design choice.

more precisely: each individual cell (note: cell, not column) could
freely contain INTEGER, FLOAT, TEXT, BLOB or NULL values.
the unique exceptions are INTEGER PRIMARY KEY columns: in this case
a strong data-type check is always enforced.
standard SQL type-names declarations such as INTEGER, DOUBLE, VARCHAR
will simply leverage an implicit type conversion whenever possible;
but SQLite will duly accept even the crazier data-type declaration
without raising the slightest complaint.

Direct consequence:
any column data-type identified by examining the SQLite schema
mainly is "aesthetic / cosmetic", and not necessarily has any
real physical meaning.
In the case of Views the SQLite schema will simply attempt to
match the corresponding input column declared data-type; but if
some column corresponds to an expression or function then no
data-type at all will be reported.

just few SQL snippets to understand better what reality is:

CREATE TABLE test (
one HUMPTY_DUMPTY,
two HOCUS_POCUS,
three ABRACADABRA);

INSERT INTO test (one, two, three) VALUES (1, 'alpha', 1.1);
INSERT INTO test (one, two, three) VALUES ('beta', 2.2, 2);

SELECT * FROM test;
one|two|three
-------------
1|alpha|1.100000
beta|2.200000|2

PRAGMA table_info(test);
name|type|notnull|dflt_value|pk
-------------------------------
one|HUMPTY_DUMPTY|0|NULL|0
two|HOCUS_POCUS|0|NULL|0
three|ABRACADABRA|0|NULL|0

CREATE VIEW test_view AS
SELECT one AS one, two AS two, three AS three,
10 * two AS expr, Length(one) AS fnct
FROM test;

SELECT * FROM test_view;
one|two|three|expr|fnct
-----------------------
1|two|1.100000|0|1
alpha|2.200000|3|22.000000|5

PRAGMA table_info(test_view);
name|type|notnull|dflt_value|pk
-------------------------------
one|HUMPTY_DUMPTY|0|NULL|0
two|HOCUS_POCUS|0|NULL|0
three|ABRACADABRA|0|NULL|0
expr| |0|NULL|0
fnct| |0|NULL|0

bye Sandro

[Stefan Keller]

Yes, I have to be reminded again and again that this weak typing behaviour of SQLite is "not an error but a feature" ;-)

But in the case of the geometry type there's the helper table views_geometry_columns, where "we" have control the geometry type metadata.

Could'nt we tweak the views_geometry_columns table? I think I have seen a version where you could indicate a geometry type like POINT explicitely when registering.

--Stefan

2013/11/13 <a.fu...@lqt.it>

bye Sandro