5

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.

1

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
0

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 STRING_AGG() and 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.