2

I have a table with lots of points which I'd like to update and set a consecutive area_id to every point "group". I define a point "group" as points that are within a 500 meters radius.

The raw table is something like:

point_id    desc_string       lat            lon          gid        geom
A           Glion           25.057939     55.187833       1
B           Glion           25.061244     55.185937       2  
C           Springs         46.433853     6.926215        3
D           Springs         46.431851     6.923763        4
E           Hobby Center    24.221813     55.780285       5
F           Ponte           -12.643624    14.864234       6
G           Ponte           -12.644966    14.866923       7
H           Ponte           -9.093045     17.658906       8

You can now see that there are different pairs or groups of points. With the following SQL I can identify the points which by definition belong to these groups:

SELECT distinct on (point_id) t1.* 
FROM ciss_poi AS t1 JOIN ciss_poi AS t2 ON St_dwithin(t1.geom, t2.geom, ( 0.5 / 111.111 )) 
WHERE t1.desc_string = t2.desc_string AND t1.gid != t2.gid

What I want is to group these pairs and add an area_ or group_id:

point_id    desc_string       lat            lon          gid        geom     group_id
A           Glion           25.057939     55.187833       1                   1
B           Glion           25.061244     55.185937       2                   1
C           Springs         46.433853     6.926215        3                   2
D           Springs         46.431851     6.923763        4                   2
F           Ponte           -12.643624    14.864234       6                   3
G           Ponte           -12.644966    14.866923       7                   3

I have no idea how to write the query. I can identify the groups but I don't know how to add the area_id.

Can someone help me?

  • I don't have a postgis sample to try it (and your sample query has different column names to your sample data), but it looks like you just need to a CREATE SEQUENCE (see, for example, neilconway.org/docs/sequences). I think your problem is still undefined in the presence of > two points where two of the points are within 500m of a third point, but not within 500m of each other (e.g. a Ponte within 500m of F, but not within 500m of G). – BradHards Oct 11 '13 at 7:31
  • Oh, you are right. The undefined problem is true. You've got an idea about solving it? Well, I read about the SEQUENCES but I couldn't find a word about signing the same value to more than one row (like one can see in the group_id column). Do you mind to help me again? Thanks – hoge6b01 Oct 11 '13 at 8:12
  • I don't think I'm helping much, but the lack of definition in your problem means that you probably won't get the right answer (since there is no "right" answer). Consider editing your question to explain why you are trying to do the grouping, which might help us help you. – BradHards Oct 11 '13 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.