PostGIS Points – Disperse Points with Same Coordinates Within a Radius Using PostGIS

pointpostgispostgresql

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.

Best Answer

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

Related Solutions

[GIS] Finding points in Kilometre radius using PostGIS

Like so,

CREATE INDEX mytable_gix ON mytable USING GIST (Geography(ST_MakePoint(lon, lat)));

SELECT * FROM mytable 
  WHERE ST_DWithin(
    Geography(ST_MakePoint(lon, lat)),
    Geography(ST_MakePoint($qlon, $qlat)),
    $radius_meters
  );

If you have your data already in geometry points, but want to do a geography-style query:

CREATE INDEX mytable_gix ON mytable USING GIST (Geography(geom));

SELECT * FROM mytable 
  WHERE ST_DWithin(
    Geography(geom),
    Geography(ST_MakePoint($qlon, $qlat)),
    $radius_meters
  );

[GIS] Find number of points within certain radius of a set of points

There is simple query for this case.

SELECT a.id AS store,count(b.*) AS customer_count FROM stores a, customers b 
    WHERE ST_DWithin(a.geom::geography,b.geom::geography,5000) GROUP BY a.id

I have stored my sample data in the projection WGS 84 (4326). When you want to use a metric system, you have to convert the geometries to geography format.

enter image description here

The result of my query:

store   customer_count
1       2
2       4
3       2

EDIT To get the percentage of all customers and customers within a certain area you can use a WITH query (not tested!):

WITH all_customers AS (
    SELECT count(*) AS all_customers 
    FROM customers
    )
        SELECT 
            a.id AS store,
            count(b.*) AS customer_count,
            count(b.*)/c.all_customers*100 AS percentage
        FROM stores a, customers b, all_customers c
            WHERE ST_DWithin(a.geom::geography,b.geom::geography,5000)
                GROUP BY a.id

Best Answer

Related Solutions

[GIS] Finding points in Kilometre radius using PostGIS

[GIS] Find number of points within certain radius of a set of points

Related Question