I have a table with all US states geometrical data:


state   | The_geom (here filling in random value)

AL      | 00012312 
NM      | 000234234
NJ      | 0000234534
NY      | 00005465
VA      | 0000023434456

I want to compute number of neighboring states for each state. I know st_touches() has to be used but I am not able to form the exact query. I think it should be something similar to below:

select count(*) as neighbors, st_touches(s.the_geom, r.the_geom) 
from USstates as s, USstates as r 
GROUP BY USsates.state;

I am not sure if this is right. Please let me know how to correct this.

closed as too broad by nmtoken, whyzar, Dan C, user2856, tinlyx Apr 4 '18 at 21:21

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 5
    Did you try anything before posting your latest homework question? – John Powell Nov 13 '14 at 7:07
  • 1
    Not sure why you removed the rest of your question, where you appear to show an attempted solution. It might have been ok then. – Martin F Nov 13 '14 at 14:18

It should be something like this:

select s.state, count (r.state) as neighbors
  from USstates as s, USstates as r
 where st_touches (s.the_geom, r.the_geom)
   and s.state != r.state
group by s.state
  • @Rdvsm: Did it work without any changes? I didn't have any way of testing it. – Martin F Nov 13 '14 at 5:48
  • thanks Martin, I dont have the table to test. I was trying to get an idea how I can use st_touches to find the negihbours. Hope it works. I am trying to get states data online. If I find it I will try it and let you know.Thanks – Force.comBat Nov 13 '14 at 5:53
  • 1
    @martinf. Be aware that this is homework. Your answer is perfect, but there has been a flood of questions the last few days, here and on SO, asking questions like this, with 0 effort being shown, often by different users asking identical questions within minutes of each other. – John Powell Nov 13 '14 at 7:06
  • @JohnBarça: This one did appear to have shown effort, but it's been edited away. – Martin F Nov 13 '14 at 14:20
  • @martinf, sorry I didn't realize. I don't have enough rep to see edits yet on here. – John Powell Nov 13 '14 at 14:22

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