Select random sample from SQLite table

I came across this post by underdark about how to select a random sample from a table in PostgreSQL. You can achieve the same (selecting 1000 random features from a table) in SQLite using something like below:

ORDER BY Random()

Great to use with a Spatialite database to select random points from your point data layer or to select random points from your point vector layer in GRASS GIS (assuming that you use SQLite as the database back-end, and you probably should).

And here another example is from a spatialite table where I want to update the value in the column ‘scale’ for a random selection of rows. In this table, the PK_UID column contains unique row identifiers. In case you are wondering, I am using the ‘scale’ column to set the minimum or maximum scale at which label points are shown in QGIS.

UPDATE labelpoints
SET scale = 1000000

A much more extended example is given by Romain (the author of QSpatialite, about which I wrote before). For examples how to do this in other databases, see this webpage.

About these ads

About pvanb

I am a tropical forest ecologist with a focus on spatial and temporal patterns and processes at population and ecosystem level. I am furthermore very interested in issues related to conservation and sustainable use of biodiversity and natural resources under current and future climates. I have worked in the Middle East (Syria and Lebanon) and South America (Brazil) and in Eastern Africa (Kenya).
This entry was posted in Data handling, GIS, Spatialite, SQLite and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s