如何在giS后多边形内生成随机``'点了？

发布于 2025-01-25 00:59:21 字数 2316 浏览 2 评论 0原文

我有一个PostgreSQL表，并试图将美国境内随机的地理位置插入列geo_location。但是，我的纬度/延长有点偏离，有些要点在海洋中显示出来。因此，我去获得了美国的确切多边形，并且需要点数才能在该多边形内出现。有人知道该怎么做吗？

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);

美国多边形

  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))

原文

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))

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情愿 2025-02-01 00:59:21

您可以使用 st_generatePoints ，它将在一个内返回随机点给定的几何形状。然后，您必须将多点转换为几个点，最后才能生成相关字段

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;

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;

回复收藏 0 原文

~没有更多了~