One of my favorite functions in PostgreSQL is the generate_series(x,y) function. What this little function does is to generate a
set of numbers from x to y.
Being able to generate a sequence of numbers has so many uses that its really hard to itemize them all.
For this particular entry I thought I would share a particular use of it in generating test data.
Often times you need to generate data for various test cases or you need to generate a lot of data to test out your speed of queries and
for benchmarking. Below is a quick query that uses some postgis functions, generate_series, and the PostgreSQL random() function to create a test table, and populate it with 500 random circles around Boston. Each circle being of various sizes.
This is in longlat projection.
CREATE TABLE testcase(gid serial primary key);
SELECT AddGeometryColumn('public', 'testcase', 'the_geom', 4326, 'POLYGON', 2);
INSERT INTO testcase(the_geom)
SELECT buffer(setsrid(makepoint(-71.0891380310059 + n*random()/500.00, 42.3123226165771 + n*random()/500.00),4326), n*0.0001)
FROM generate_series(1,500) As n;
CREATE INDEX idx_testcase_the_geom ON testcase USING gist(the_geom);
If you wanted to store the data in a different projection - you would do something like this instead. The below example will generate data in MA NAD 83 feet and create circles of radius in increments of 100 feet
CREATE TABLE testcase(gid serial primary key);
SELECT AddGeometryColumn('public', 'testcase', 'the_geom', 2249, 'POLYGON', 2);
INSERT INTO testcase(the_geom)
SELECT buffer(transform(setsrid(makepoint(-71.0891380310059 + n*random()/500.00, 42.3123226165771 + n*random()/500.00),4326),2249), n*100)
FROM generate_series(1,500) As n;
CREATE INDEX idx_testcase_the_geom ON testcase USING gist(the_geom);
Note, it is possible to create elaborate spatial data using generate_series and additional functions like trigonometric functions and other postgis functions to generate more intricate patterns of spatial data fitting certain criteria.
In other databases that lack this functionality I either create it if the database is powerful enough to support set returning functions, or I resort to creating a dummy table that has nothing but numbers in it. The technique comes in especially handy when you need to create something like a sequence of dates.
