Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question

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:

SELECT p1.ROWID, p1.ZCTA5CE10, p2.ZCTA5CE10 
FROM "US_zcta5_2010" p1, "US_zcta5_2010" p2 
WHERE Touches (p1.Geometry, p2.Geometry)=1;
share|improve this answer
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 '12 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/… –  HeyOverThere Dec 31 '12 at 2:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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