How can I generate a specific number of random points in a polygon with PostGIS?

Question

I have a postgis table that I've uploaded a shapefile to, this shapefile contains 200+ polygon rows with several integer columns.

What I'd like to do is similar to the QGIS 'random points in polygon' function except with numbers that are much larger. My initial approach with QGIS crashed upon trying to add ~500,000 random points to each polygon - I need the number of random points to come from the related integer column.

After some research, an indexed postgis database is the way to go. But I'm unsure how to generate random points in a polygon for each polygon in my table, and to have the number of points that get randomly generated, come from a corresponding column.

New to Postgres, and postgis, but familiar with mysql, QGIS, ArcGIS, and geographic concepts.

EDIT:

Here is a picture to better show what I would like to accomplish. Each polygon has an associated PNTSneeded value, I've labelled the polygons in the screenshot with that number (also shown in the attribute table). I need to generate that amount of points, randomly dispersed throughout each polygon row.

I would only need to do this once, so it wouldn't be an on-going process, although I may need to do the same process for different sets of polygons in the future (I'm sure I could recreate the steps).

In the event that this is indeed too many points to generate, I could always run it twice at half the numbers and then just merge the files into one (might add an extra level of randomness). But I would much rather be able to do this in one go.

Answer 1

You should try the 'RandomPointsInPolygon(geom geometry, num_points integer)` function.

Add an additional geometry column, and update it using RandomPointsInPolygon. The first parameter is your existing geometry, and the second is your PNTSneeded column.

There are two question on SO related with RandomPointsInPolygon:

How to create random points in a polygon in PostGIS

Postgis random point inside a polygon

Start with a smaller sample, if possible, using a WHERE clause in the UPDATE statement.

The actual code would be:

-- copy and paste the RandomPointsInPolygon function
CREATE OR REPLACE FUNCTION RandomPointsInPolygon(geom geometry, num_points integer)
  RETURNS SETOF geometry AS
$BODY$DECLARE
  (...)

-- if you use EPSG:4326
SELECT AddGeometryColumn('polygons', 'points', 4326, 'MULTIPOINT', 2);

-- update your table polygons, using existing geom and PNTSneeded
UPDATE polygons
SET points = (SELECT ST_Union(manypoints)
FROM RandomPointsInPolygon("geom", "PNTSneeded") AS manypoints);

Answer 2

The Postgis version 2.3.0 and upper have a new function to generate points into polygon ST_GeneratePoints.