virtual elementarygeometries problem

[David Anderson]

For some reason I am having issues with the virtual elementary geometries

What I am trying to do is to generate a square grid for a set of polygons where the squares are clipped to the boundary of the original polygon. 

So far I I have one this

Create the grid

create table square1 as
select rowid as orig_rowid,squaregrid(geometry,150) as geometry
from base_gis
where st_area(geometry)>50000

Recover the geometry

select recovergeometrycolumn('square1','geometry',26912,'MULTIPOLYGON')

This query works, returning a set of rows

select item_no,st_area(geometry) from elementarygeometries
where f_table_name='square1' and origin_rowid=1

This query does not return any rows

select sqr.geometry,eg.geometry
from square1 as sqr
join
ElementaryGeometries as eg
on (eg.origin_rowid=sqr.rowid and eg.f_table_name='square1')

I get this error:

[14:51:23] SQLiteStudio was unable to extract metadata from the query. Results won't be editable.

The documentation here: https://www.gaia-gis.it/fossil/libspatialite/wiki?name=VirtualElementary

is not entirely clear about whether the rowid of the table is being used or if the primary key is being used.

David

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

On Fri, 11 Dec 2015 14:09:29 -0800 (PST), David Anderson wrote:
> This query does not return any rows
>
> select sqr.geometry,eg.geometry
> from square1 as sqr
> join
> ElementaryGeometries as eg
> on (eg.origin_rowid=sqr.rowid and eg.f_table_name='square1')
>

Hi David,

a Virtual Table is not a genuine table as any other; it really
is a driver pretending to simulate a table (that is not exactly
the same). And very ofter a Virtual Table can only support a
very limited set of predefined options in order to properly work.

select sqr.geometry,eg.geometry
from square1 as sqr
join
ElementaryGeometries as eg

on (eg.f_table_name='square1' and eg.origin_rowid=sqr.rowid);

if you write your query in the above form it will nicely work;
if you swap the positions of the f_table_name and origin_rowid
columns it will not work simply because the underlaying driver
will then be unable to correctly retrieve its invocation arguments
following its predefined order.

> The documentation here:
>
> https://www.gaia-gis.it/fossil/libspatialite/wiki?name=VirtualElementary

> [1]

>
> is not entirely clear about whether the rowid of the table is being
> used or if the primary key is being used.
>

accordingly to the general principles of the SQLite architecture,
if the Primary Key has just a single column declared of the
INTEGER type then ROWID will simply be an alias name for the
Primary Key column.
in any other case (a PK formed by a single column but not of the
INTEGER type, or a PK formed by several different columns, or no
PK at all) the ROWID will simply correspond to the physical position
of the row within the table.

the "origin_rowid" term always expects to match an INTEGER; so
as a general rule it's safe to use the ROWID.
in the special case of a table declaring a single-column PK
of the INTEGER type (which always is the best practice on
SQLite) you can indifferently use the name of the PK column
or ROWID as you wish better.

bye Sandro

[David Anderson]

Sandro, Thanks for the response.  Switching the order did the trick.  I knew that I was making a obvious mistake.  I just couldn't figure it out.  I was assuming that the join between the tables worked just like  regular join where argument order is unimportant.  I see it is more like a function call with positional arguments.

And I learned something about SQLite.  I thought ROWID worked like it does in Oracle, where the primary key and rowid are not related.

David