Finding all neighbors for all polygons in Spatialite

Question

Excuse me if this is a basic question but I'm new to Spatialite...

I have a dataset with zip code polygons and I would like to create a list where for each zip code we have listed all the neighboring (i.e. adjacent) zip codes.

If I do the following it works but only for 1 zip code. What I would like to do is to repeat this automatically for all the zip codes in the data (since I have thousands of codes).

SELECT p1.ROWID, p1.ZCTA5CE10 FROM "US_zcta5_2010" p1, "US_zcta5_2010" p2
WHERE p2.ROWID=1 AND
Touches (p1.Geometry, p2.Geometry)=1;

I'm using the spatialite-gui on a Mac.

Answer 1

Just take out the p2.ROWID=1, you'll end up with a full list. You might want to throw in an ORDER BY to make a nicer list.

Edit: Sorry, you'll need to adjust your output, to something like:

SELECT p1.ROWID, p1.ZCTA5CE10, p2.ZCTA5CE10 
FROM "US_zcta5_2010" p1, "US_zcta5_2010" p2 
WHERE Touches (p1.Geometry, p2.Geometry)=1;