How to generate random `POINTS` within a POSTGIS Polygon?-CodePudding

I have a postgresql table and trying to insert randomized geo locations within the US to the column geo_location. However, my LAT/LONG and are a little off and some points are showing in the oceans. So I went to get the exact polygon of the US and need the points to only show up when they are WITHIN that polygon. Does anyone know how to do this?

INSERT INTO test_dataset
(first_name, last_name, geo_location)
select 
       substr(md5(random()::text), 1, 7),
       substr(md5(random()::text), 1, 10),
       (random() * (47.606209 - 25.427152))   25.427152 || ',' || (random() * (-124.389641 - -69.082237))   -69.082237
FROM generate_series(1, 20);

United States Polygon

  POLYGON((-125.016047790785 48.208785494735764,-124.14089035719684 40.86877099667271,-121.5599275789894 36.06717734117032,-117.28671864846486 32.64978455562844,-113.72654637400285 32.31983439152819,-108.30080609779633 31.38368862238211,-105.8560247727104 31.460910518060302,-103.3967999601722 28.907930814307832,-101.79828909376985 29.931584620558965,-99.88202150341736 28.5312922037797,-97.23709470934983 25.954713168125338,-97.10002896107818 28.035767664456188,-94.14206168386124 29.48328979323645,-89.95286079259995 29.22199958232973,-88.80769857316915 30.441930703667964,-85.20950160725116 30.01610257057618,-83.55763826679038 29.988533620710356,-81.11550982715487 25.210558232017902,-79.82296508270082 26.439779557216667,-81.15323975356083 30.633102305542167,-80.61057729642516 32.67360737502075,-76.42226070031401 35.08303539533796,-76.74443890001484 38.7701053822278,-75.94326749398839 39.537424403566234,-75.85778562947489 37.61762713466268,-73.584262798466 41.05930212093591,-70.25489155819044 41.81899403776205,-70.84147400778302 43.17550270250865,-66.71653189743186 44.621194501738415,-67.98343208253185 47.27220127731093,-69.31075910289138 47.37130972398248,-70.71885174696367 45.18697869058366,-75.17835218911763 44.78963172176475,-77.22343210634047 43.57594231004268,-78.78804499198748 43.38258742737503,-82.22588977068015 41.561458542619334,-83.6165912767997 41.73128368552776,-83.54290001428807 45.0297037216441,-85.31768038061823 45.46571539393534,-86.68244256233366 41.733923340223015,-87.68199084704145 42.03741658905679,-87.39606874849632 45.11317999488102,-90.68977341771625 46.446847850725874,-92.2888738142187 46.476695535973505,-90.4321487639756 48.14018316715507,-96.18714160108408 48.89984262583636,-122.89609755085033 48.93083650796555,-125.016047790785 48.208785494735764))

CodePudding user response：

You can make use of ST_GeneratePoints, which will return random points within a given geometry. Then you would have to transform the multipoints to several single points, and at last to generate the related fields

select 
      substr(md5(random()::text), 1, 7),
      substr(md5(random()::text), 1, 10),
     (st_dump(ST_GeneratePoints('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))',20))).geom;