0

How to add to my postgres/postgis table the following columns

X coordinate | Y coordinate | count (number of points in same location)

from an existing table populated with geometries type POINT

Improve this question
4
  • 3
    The answer is a SQL join, but you really need to make an attempt at coding the query before we can help you finish it. At a minimum you'd need to include the names of the tables and the column(s) on which they are joined.
    – Vince
    Commented May 27, 2016 at 11:12
  • @AndreSilva I didn't have any example because like I said, it's my first time using the thing. Thankfully a good lad in stack overflow managed to help me merge the tables together. I still don't know how to produce the geometry columns though so if you could help me it would be great
    – Luffydude
    Commented May 27, 2016 at 15:36
  • 1
    I was waiting that the moderators will unhold your question after the edit but still nothing so here is a possible answer, if your question is unhold again I'll post it as an answer later: Here to add the columns of X and Y : alter table your_Table add column x double precision; alter table your_Table add column y double precision; update your_Table as a set x=(select st_x(b.your_GeometryColumn) from your_Table as b where a.id=b.id); update your_Table as a set y=(select st_y(b.your_GeometryColumn) from your_Table as b where a.id=b.id);
    – Hicham Zouarhi
    Commented May 27, 2016 at 18:12
  • 1
    and here to add the count of overlapping points : alter table your_Table add column overlapping_pts integer; update roads_vertices_pgr as a set overlapping_pts=(select count(id) from roads_vertices_pgr as b where a.x=b.x and a.y=b.y);
    – Hicham Zouarhi
    Commented May 27, 2016 at 18:12

1 Answer 1

Reset to default
4

The "same location" part of your question is problematic, since locations are stored as floating point values, and due to computer operating system rounding errors you will get two points that you consider at the same location with very slightly different X,Y.

The correct way to do this is to define a buffer distance within which you consider two points as equal. The PostGIS function that does this is ST_Dwithin(). So to complete Hicham Zouarhi's comment/answer (assuming your table is called "pts" and it has a column "the_geom", and a primary key column "id":

ALTER TABLE pts ADD COLUMN x DOUBLE PRECISION, y DOUBLE PRECISION, cnt INTEGER;
UPDATE pts SET x=ST_X(the_geom), y=ST_Y(the_geom);
UPDATE pts SET cnt=(SELECT count(*) FROM pts AS p1 JOIN pts AS p2 ON ST_Dwithin(p1.the_geom, p2.the_geom,0.001) WHERE p1.id>p2.id);

I chose a distance for ST_Dwithin of 0.001. If your data is in a meter based projection, then the buffer will be 1 mm. However, If your data is in a geographic, long/lat based CRS, then this buffer will be 0.001 degrees, or about 100 meters. So in a degree based CRS, you probably will want a much smaller distance.

The WHERE part of the UPDATE is to insure that you don't get double counts: i.e. point 1 is within 1mm of point 2, and point 2 is within 1 mm of point 1. By comparing the id values, you avoid this duplicate count.

Improve this answer
7
  • Weird behavior but I got error ERROR: syntax error at or near "y" LINE 1: ...TER TABLE abcounts ADD COLUMN x DOUBLE PRECISION, y DOUBLE P... ^ ********** Error ********** ERROR: syntax error at or near "y" SQL state: 42601 Character: 53
    – Luffydude
    Commented Jun 1, 2016 at 11:53
  • Added the columns 1 by 1 and the query is now running, Although it's been running for the last 20minutes.
    – Luffydude
    Commented Jun 1, 2016 at 13:11
  • 2
    Sorry about the syntax error. ADD COLUMN needs to be one at a time. UPDATE can do several columns at once. But 20 minutes is quite long. How many points are there? You might well need to add a spatial index to get the query to complete in a reasonable time.
    – Micha
    Commented Jun 1, 2016 at 13:19
  • 2
    Oh, you will definitely want to create a spatial index on the points layer. Without a spatial index, each of the 5 Mil points must check all 5 Mil other points for ST_Dwithin(). That's 25*10^12 checks! I would suggest you kill the process and restart after the point layer is indexed.
    – Micha
    Commented Jun 1, 2016 at 13:33
  • 1
    You could start here: postgis.net/docs/using_postgis_dbmanagement.html#idp72438624
    – Micha
    Commented Jun 2, 2016 at 19: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.