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?

  • The standard way to do this is using random in conjunction with the range of the primary key column (ID) and generate_series to produce the number of rows you need. See this Stack Overflow answer. Nov 30, 2015 at 6:39

2 Answers 2


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

FROM table_name
  ST_SetSRID(ST_Point(center_lng, center_lat), 3857),
  ST_Transform(table_geometry, 3857),
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?

  • 3
    I think you want to add ORDER BY random() above your LIMIT clause.
    – dbaston
    Nov 28, 2015 at 20:51

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

SELECT ST_RandomPoints(
                ST_Point(long, lat),
       ) geom
FROM yourpointtable;

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


  • St_RandomPoints generates completely new points, rather than selecting a random subset of a set of points. Source: geospatialelucubrations.blogspot.co.at/2013/11/…
    – vpgcloud
    Dec 4, 2015 at 16:14
  • Also, the two input geometries for ST_Buffer have to share a SRID that uses meters, not degrees, as a unit.
    – vpgcloud
    Dec 4, 2015 at 16:16
  • @VeronicaLatLng There is no two input geometries for ST_buffer(). Only one. The only point passed to the function is transformed to a projected coordinate system using ST_Tranform()... Mar 18, 2016 at 14:16
  • Input geometries was the wrong word, sorry, I meant the two input parameters. My point remains, however, that you can't just use any SRID, but you have to use one where the unit is meters, as the "500" will turn into "500 degrees" otherwise, which really doesn't make any sense.
    – vpgcloud
    Apr 1, 2016 at 13:11

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.