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
Touches (p1.Geometry, p2.Geometry)=1;

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

1 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:

FROM "US_zcta5_2010" p1, "US_zcta5_2010" p2 
WHERE Touches (p1.Geometry, p2.Geometry)=1;
  • 1
    Thanks. That seems to work but it takes a looong time to run (I'm not even sure it is still running). Since I have thousands of zip codes is there a more efficient way of doing this? Thanks again!!
    – Ana
    Dec 31, 2012 at 2:06
  • 2
    I forgot that Spatialite doesn't use a spatial index by default, you'll have to manually join to the index table. Here's a link from the Spatialite manual on how to use spatial indexes: gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/… Dec 31, 2012 at 2:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.