6

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.

  • 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 5 '15 at 1: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 5 '15 at 4: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 5 '15 at 16:35
  • 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. – Richard Law Sep 5 '15 at 22:53
  • 4
    Duplicated of gis.stackexchange.com/questions/89954/… – Antonio Falciano Sep 7 '15 at 16:16
6
+50

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.