8

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).

Shows desired points per polygon row

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.

Chewy
  • 583
  • 7
  • 18
  • 8
    500K points is a lot of points, random or otherwise. Times 200+ polygons, that's 100 million points, which is sufficiently unmanageable that you probably need to explain why you need to make your life that difficult. – Vince Sep 05 '15 at 01:37
  • 1
    Raster with 10000x10000 pixels has 100 million points and is manageable. I think I would try to make a dense raster table first and select random pixels within polygons. – user30184 Sep 05 '15 at 04:45
  • It is reasonably straightforward to do what you ask, but not necessarily very efficient. Is this a one off, or are you looking for something that you will run often? Each polygon should have a different number of random points, depending on the value in some integer column you mention? – John Powell Sep 05 '15 at 16:35
  • I've updated the post to show more clearly what I'm looking to do. I hope you're right John and that it is reasonably straightforward. – Chewy Sep 05 '15 at 22:51
  • 3
    I still think you need to add an explanation as to why you are doing this: there may be an alternative to achieve the same outcome. – alphabetasoup Sep 05 '15 at 22:53
  • I have X number of sample points within these boundaries, but I do not know exactly where these points are located, only that there are X amount inside each polygon. I have an updated boundary layer for the same geography and I would like to give as accurate of a count for the new boundary layer - based on the old boundary layer. So I've multiplied my sample points by a large number and hope to generate random points, the next step is to put the new boundary layer on top of the points layer, count the points in polygons, and divide by my original number - my take on a Monte Carlo method. – Chewy Sep 05 '15 at 23:03
  • 4
    Duplicated of http://gis.stackexchange.com/questions/89954/how-to-create-random-points-in-a-polygon-in-postgis – Antonio Falciano Sep 07 '15 at 16:16
  • if you intersect the old and new polygons, then the relative area of each old polygon multiplied with the number of points of each of those polygons will be the expectation of the number of random points that you are looking for (because the probability to have a point somewhere in a polygon is homogeneous). – radouxju Sep 08 '15 at 11:36

2 Answers2

7

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);
Community
  • 1
jgrocha
  • 5,345
  • 25
  • 43
0

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

tres.14159
  • 133
  • 1
  • 4