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:

SELECT * FROM MyTable
ORDER BY Random()
LIMIT 10 

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
WHERE PK_UID IN (SELECT PK_UID FROM labelpoints ORDER BY RANDOM() LIMIT 1000)

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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