I have set of census tracts spaced out across a city and I would like to randomly choose an adjacent census tract to explore nearby demographic data. I'm not sure if there is an easy process for getting adjacent census tracts for selected census tracts.
Building off the other answer, you could aggregate the answers into an array, and then use the PostgreSQL random function multiplied by the array_length to get a random index for that array. The + 1 at the end of that is because array indexes start at 1 in PostgreSQL
WITH arrayed_data AS(SELECT ARRAY_AGG(a.tile, ',' ORDER BY a.tile) AS tiles FROM cuy_contour_tiles AS p, cuy_contour_tiles AS a WHERE ST_Touches(p.geom, a.geom) AND p.tile = 'MyCensusTractOfInterest' GROUP BY p.tile ORDER BY p.tile) SELECT tiles[ ( (array_length(tiles,1) - 1) *random())::int + 1] FROM arrayed_data
One of my new favorite tricks is string_agg(). If You can get your data into a modern database, then you have code like this
SELECT STRING_AGG(a.tile, ',' ORDER BY a.tile) As tiles FROM cuy_contour_tiles AS p, cuy_contour_tiles AS a WHERE ST_Touches(p.geom, a.geom) AND p.tile = 'MyCensusTractOfInterest' GROUP BY p.tile ORDER BY p.tile;
The code is from Steve Mather's blog post with the addition of an
AND clause. The Boston GIS people go into great detail. I know from experience that Oracle limits the aggregation to 4,000 characters = max size of VARCHAR2() database column type.
If the list aggregation function results are a problem, then drop both the
GROUP BY p.tile clause. Replace
STRING_AGG() .. AS tiles line with
p.tile AS selected_tile, a.tile AS adjacent_tiles.
In either case, you now have a list of tiles to figure out how you will randomly make your final selection.