How to select 200 random points within a 100m radius in Postgis

Question

I'm currently learning to use PostgreSQL and PostGIS. I would like to know how to write the SQL that would allow me to select 200 random points within a radius of 500m from a user defined centre point. Currently I have the database containing all my records, with an individual row for ID, latitude and longitude. The co-ords are in WGS84. The 'points' refer to shops.

Once I have the 200 randomly selected points, I would then like to edit a further two coloumns ("Status" and "Time") to show the selected points are closed and the time which they closed.

What would you suggest is the simplest way to perform this?

Answer 1

To select 200 points within a 500m radius, try this:

SELECT *
FROM table_name
WHERE ST_DWithin(
  ST_SetSRID(ST_Point(center_lng, center_lat), 3857),
  ST_Transform(table_geometry, 3857),
  500
)
LIMIT 200;

As for the "randomization" part, adding ORDER BY NEWID() at the end might work, but I'm not completely sure.

And finally, how exactly would you like to edit the two columns?

Answer 2

Download and install PostGIS Add-ons in your PostGIS database. There is a ST_RandomPoints() function. Use it like this:

SELECT ST_RandomPoints(
          ST_Buffer(
             ST_Transform(
                ST_Point(long, lat),
                yourfavoriteprojectedcoordinatesystemSRID
             ), 
             500), 
          200
       ) geom
FROM yourpointtable;

This will create 200 points for each row in your table.

https://github.com/pedrogit/postgisaddons