Touches

[dsl]

I would like to populate a table by using the touches geometry query
so that for each entity I have a list of all the other entities it
touches.

I've gotten as far as a simple touches query but it only returns the
result for the first row.

SELECT "STATE", "Geometry" as G1
FROM "states_Dissolve"
WHERE Touches(G1, (SELECT "Geometry" as G2 FROM "states_Dissolve")) =
1;

Any help would be appreciated.

Thanks,
David

[dsl]

I tried this, but if there is another methoud I would like to hear
about it. First I created a view called STATES and then ran this
query:
SELECT STATES.PK_UID, STATES.STATE, STATES.Geometry as G1,
states_Dissolve.PK_UID, states_Dissolve.STATE,
states_Dissolve.Geometry as G2
FROM STATES, states_Dissolve
WHERE Touches(G1, G2) = 1;

And that worked.

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

Hi,

the question you posed is a quite interesting one,
and requires some in-depth analysis.

It's not at all related with Spatial functions
as Touches(), but is directly related with
SQL syntax and SQLite's own SQL interpretation.

I tested an SQL query using the first form you used
(for simplicity I used only TEXT and INTEGER columns)

SELECT Name, Peoples
FROM Towns
WHERE Name = (SELECT Name FROM Towns)

As you report, SQLite simply returns only 1 (one) row,
the first matching one.

Then I executed the same identical SQL query, but
this time using PostgreSQL, and I got this error:
"more than one row returned by a subquery used as an expression"

And finally I tested this query on MySQL. Again
I got this error: "Subquery returns more than 1 row"

PostMortem:
===========
standard SQL requires a sub-query used as an expression
to return just a single row [actually, there where about
8,000 rows in my test query].

- MySQL and PostgreSQL raise an error exception, and then
abort any further processing
- SQLite merely processes the first row, and then halts

The SQLite own implementation seems to be a little bit
'original', but I think this one is not a very serious
issue, accordingly to SQLite 'minimalistic' approach.
Anyway, the lesson to learn is:
THIS ONE IS A TRICKY SQL SYNTAX: DON'T USE IT AT ALL

-----------------------------------

As you already discovered by yourself, the 'right'
SQL syntax you have to use in oder to solve your
problem is the second one you tested.
I've simply rewritten it in cleaner way:

SELECT s1.PK_UID, s1.STATE, s1.Geometry as G1,
s2.PK_UID, s2.STATE, s2.Geometry as G2
FROM states_Dissolve AS s1, states_Dissolve AS s2

WHERE Touches(G1, G2) = 1

AND s1.PK_UID <> s2.PK_UID;

a) there is no reason at all to create a second VIEW:
you simply can select two times the same table,
using different alias names.
b) now you are asking SQL to compute the Cartesian
Product between two tables [yea, they always are
the same physical table, but it will be scanned
twice ...]
c) so, Touches() will be evaluated for each entity
against any other entity
d) it seems stupid to compute Touches() for the same
identical entity, so I've added the
s1.PK_UID <> s2.PK_UID
clause in order to avoid this occurrence

Bye, Sandro

[dsl]

Thanks for the thourough explanation. As your example shows then I
wouldn't need to create a view, but just call the table AS something
else. That is much cleaner and simpler.