PostGIS, PostgreSQL

Selecting a Random Sample From PostgreSQL

Do you need a random sample of features in a Postgres table? Here is an example of how to select 1,000 random features from a table:

SELECT * FROM myTable
WHERE attribute = 'myValue'
ORDER BY random()
LIMIT 1000;

3 comments

Pingback: Select random sample from SQLite table | Ecostudies
Ilya said: 2011-06-1112:13

Thank you for this handy hint. I never heard about it before. There is one limitation with this approach in case of variable spatial density of the features. E.g. road network in the downtown of the city has higher density than in suburbs, so such type of a selection will produce biased towards more dense regions subset of the points: http://www.i-bakery.ru/image/full/agpzY20tYmFrZXJ5cg0LEgVNZWRpYRjp6QkM/screen.png

So, I wonder how to make feature sampling via regular grid or take into account spatial density?
- underdark said: 2011-06-1116:30
  
  Hi Ilya,
  
  That’s an interesting question. Maybe you could ask it on gis.stackexchange.com. There are some really knowledgeable people there.
  
  Best wishes,