2

I had located on my table I have sets of points sharing the same coordinates and they are clustering, is there a way to regenerate their geoms so they get dispersed or redistributed within a certain radius using PostGIS and PostgreSQL?


The data I count with is the lat/long and geom of the points and I just need them to be a couple meters away from each other, I need to work it out in the database, not using external tools such as Arcgis Pro or Qgis.

0

2 Answers 2

2

This creates a new POINT at random from <points> within the given <radius> around the given input <points>.geom (POINT):

SELECT ST_MakePoint(
         ST_X(geom) + rad*SIND(ang),
         ST_Y(geom) + rad*COSD(ang)
       ) AS geom
FROM   (
    SELECT random() * 360.0 AS ang,
           random() * <radius> AS rad,
           geom
    FROM   <points>
) q
1
  • I choose this solution cause with some modifications it gave me more freedom at the time of defining the radio among the axes to spread the points wider horizontaly or vertically. Many thanks @geozelot !!!
    – Kashtanka
    Mar 28, 2022 at 21:54
1

The result of the SQL query creates a new ungrouped pnts layer randomly from (Point-EPSG:4326) preset <radius_bufer> around the given input .geom:

WITH
tbla AS (SELECT count(*) cnt, ST_Buffer(geom, 0.00001) geom FROM <points> GROUP BY geom)
SELECT ST_GeneratePoints(geom, cnt::integer) AS pnts FROM tbla ORDER BY cnt

Try to adjust the desired distance between points, since the random distribution will not be 2m :-)...

Original spatial solutions...

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.