No such column ROWID in my spatial index query
ckgoo...@gmail.com
Hi,
I have a select statement that uses a spatial index that works okay but when I then expand the statement to join with another table I get an error "No such colum 'ROWID'".
This is the one that works:
SELECT l.pk_uid
FROM spt_LandPolygons AS l
WHERE
ROWID IN (
SELECT ROWID FROM SpatialIndex WHERE(( f_table_name = 'spt_LandPolygons' )
AND ( search_frame = MakePoint( -2, 52 ) ))
AND ST_Contains( l.lpolygon , MakePoint( -2,52) )
);
And then I get my coordinate from another table and is where I get the error:
SELECT l.pk_uid
FROM spt_LandPolygons AS l
JOIN spt_Polygons AS p
WHERE
p.id = 6666
AND ROWID IN (
SELECT ROWID FROM SpatialIndex WHERE(( f_table_name = 'spt_LandPolygons' )
AND ( search_frame = ST_Centroid( p.polygon ) ))
AND ST_Contains( l.lpolygon , ST_Centroid( p.polygon ))
);
Is there a way to qualify the table ROWID is from?
The two tables of spt_LandPolygons and spt_Polygons are actually in two different .db files but I've attached the two databases. I don't think this is the problem but please let me know if you think it might be.
Any other thoughts most welcome.
Best, Chris
a.fu...@lqt.it
> Is there a way to qualify the table ROWID is from?
>
There are two different types of tables in SQLite: those
with ROWID and those without.
The difference lies entirely in the way the table is created:
you must always explicitly require that a table does not have
a ROWID, otherwise each table always has its own ROWID.
CREATE TABLE tbl (....) WITHOUT ROWID;
If in the table there is a primary key composed of just a
single column of the INTEGER type, then ROWID simply is an
alias-name for that column.
If, however, a PK with those characteristics does not exist,
then SQLite automatically manages a sort of ghost column
named ROWID (unless it's a WITHOUT ROWID table).
Of course, just as with any other column name, it's
possible to qualify the column name by specifying the
table prefix as in a.ROWID, b.ROWID and so on.
> Any other thoughts most welcome.
>
because it will slow down your query terribly.
ST_Centroid() is a computationally heavy function,
and in this way it will be unnecessarily called
again and again, a real performance killer.
In all these situations it's always advisable to
add a further column containing the pre-calculated
centroid to maintain brilliant performance.
best regards,
Sandro
ckgoo...@gmail.com
What is the table name to qualify the ROWID with when searching a spatial index?
Best, Chris
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 view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/948e290dd9f75ccf9a408fd1e7e3c4c9%40lqt.it.
a.fu...@lqt.it
> Hi Sandro,
> What is the table name to qualify the ROWID with when searching a
> spatial index?
>
the ROWID of the SpatialIndex never needs to be qualified because
it's always referenced from within an inner sub query context;
this certainly cannot be the reason why your query gives
errors.
after rereading better your first email I notice that
you are using an ATTACHED DB
Are you aware that for querying the SpatialIndex on
a DB other than MAIN it's necessary to use a special
syntax?
suggested reading:
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatialIndex
bye Sandro
ckgoo...@gmail.com
Success! I just had to write "WHERE l.ROWID" to qualify it. The use of an attached database didn't affect it though that was a useful page to read.
I've also created a precalculated centroid column. So I think I have a workable method now of trimming my polygons.
But that is for tomorrow and I will see if my approach works.
Many thanks for your help.
-----Original Message-----
From: spatiali...@googlegroups.com <spatiali...@googlegroups.com> On Behalf Of 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-use...@googlegroups.com.