Spatialite Query examples

Introduction

These are examples of spatialite queries

Details

TOUCHES EXAMPLE

This is an example of using a query to identify all the features that touch a feature. It uses a table called states_Dissolve that is imported from a shapefile of all the states. I created a view of this table including the PK, state name, and geometry.

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;

The result is a table that lists all the features that touch a feature.

Modification (Insert Into another table)

INSERT INTO Touches_Tbl(stateID, touchesState) SELECT STATES.PK_UID, states_Dissolve.PK_UID FROM STATES, states_Dissolve WHERE Touches(STATES.Geometry, states_Dissolve.Geometry) = 1;

This example includes the shared boundary length:

INSERT INTO Touches_Tbl(stateID, touchesState,sharedBoundary) SELECT STATES.PK_UID, states_Dissolve.PK_UID, Length(Intersection(STATES.Geometry, states_Dissolve.Geometry)) As Length FROM STATES, states_Dissolve WHERE Touches(STATES.Geometry, states_Dissolve.Geometry) = 1;

This example includes the shared boundary length:

Easy mistake to make. It shouldn't be Length but GLength...

INSERT INTO Touches_Tbl(stateID, touchesState,sharedBoundary) SELECT STATES.PK_UID, states_Dissolve.PK_UID, GLength(Intersection(STATES.Geometry, states_Dissolve.Geometry)) As Length FROM STATES, states_Dissolve WHERE Touches(STATES.Geometry, states_Dissolve.Geometry) = 1;

Touches Simplified

I didn't need to create a view but give the table two aliases. This also skips over features with the same ID:

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

Thanks to Sandro http://groups.google.com/group/spatialite-users/browse_thread/thread/45929c58d024f972

Touches Results Deleting Duplicates

This results in a table that has duplicates. For example if state 1 touches state 10 then there is a row for 1 to 10 and a row for 10 to 1.

To delete these duplicates you can use the following. First to see all the duplicates you can use a query similar to this.

'SELECT FROM Touches_Tbl as t1 WHERE EXISTS (SELECT FROM Touches_Tbl as t2 WHERE t2.touchID < t1.touchID AND t2.stateID = t1.touchesState AND t2.touchesState = t1.stateID)'

To actually DELETE the duplicate ROWS:

DELETE FROM Touches_Tbl WHERE EXISTS (SELECT FROM Touches_Tbl as t2 WHERE t2.touchID < Touches_Tbl.touchID AND t2.stateID = Touches_Tbl.touchesState AND t2.touchesState = Touches_Tbl.stateID)