3

I have a point dataset with many overlapping points. For the locations where I have overlap, I want to increase the x and/or y location for 1 of the points by 0.1 meters. I need to do this consecutively until there are no points that overlap but are "neighborly". I cannot google this question well enough to get an answer. Thoughts?

0

I figured it out with a bit of brain power. If this can be improved upon, please do:

CREATE TABLE p797.points2 AS 
SELECT * 
FROM p797.points 
WHERE gid NOT IN (
        SELECT gid
        FROM (
            SELECT gid, count(gid) count
            FROM p797.points 
            GROUP BY gid
              ) a
        WHERE count > 1
        );

INSERT INTO p797.points2 (gid, geom)
SELECT gid, ST_SetSRID(ST_MakePoint(x + ((rownumber - 1)/10), y +((rownumber - 1)/10)),3400)
FROM
    (
    SELECT  gid, ST_X(geom) x, ST_Y(geom) y, row_number() OVER (PARTITION BY gid) rownumber
    FROM p797.points 
    WHERE gid IN (
            SELECT gid
            FROM (
                SELECT gid, count(gid) count, status 
                FROM p797.points 
                GROUP BY gid, status
                  ) a
            WHERE count > 1
             )
    ) c 

Your Answer

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

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