Comment on page
random
CORE
This module contains functions to generate random geographies.
ST_GENERATEPOINTS(geog, npoints)
Description
Generates randomly placed points inside a polygon and returns them in an array of geographies.
The distribution of the generated points is spherically uniform (i.e. if the coordinates are interpreted as longitude and latitude on a sphere); this means that WGS84 coordinates will be only approximately uniformly distributed, since WGS84 is based on an ellipsoidal model.
warning
It never generates more than the requested number of points, but there is a small chance of generating less points.
geog
:GEOGRAPHY
a polygon; the random points generated will be inside this polygon.npoints
:INT64
number of points to generate.
Return type
ARRAY<GEOGRAPHY>
Example
carto-un
carto-un-eu
carto-os
manual
WITH blocks AS (
SELECT d.total_pop, g.blockgroup_geom
FROM `bigquery-public-data.geo_census_blockgroups.us_blockgroups_national` AS g
INNER JOIN `bigquery-public-data.census_bureau_acs.blockgroup_2018_5yr` AS d ON g.geo_id = d.geo_id
WHERE g.county_name = 'Sonoma County'
),
point_lists AS (
SELECT `carto-un`.carto.ST_GENERATEPOINTS(blockgroup_geom, CAST(total_pop AS INT64)) AS points
FROM blocks
)
SELECT points FROM point_lists CROSS JOIN point_lists.points
WITH blocks AS (
SELECT d.total_pop, g.blockgroup_geom
FROM `bigquery-public-data.geo_census_blockgroups.us_blockgroups_national` AS g
INNER JOIN `bigquery-public-data.census_bureau_acs.blockgroup_2018_5yr` AS d ON g.geo_id = d.geo_id
WHERE g.county_name = 'Sonoma County'
),
point_lists AS (
SELECT `carto-un-eu`.carto.ST_GENERATEPOINTS(blockgroup_geom, CAST(total_pop AS INT64)) AS points
FROM blocks
)
SELECT points FROM point_lists CROSS JOIN point_lists.points
WITH blocks AS (
SELECT d.total_pop, g.blockgroup_geom
FROM `bigquery-public-data.geo_census_blockgroups.us_blockgroups_national` AS g
INNER JOIN `bigquery-public-data.census_bureau_acs.blockgroup_2018_5yr` AS d ON g.geo_id = d.geo_id
WHERE g.county_name = 'Sonoma County'
),
point_lists AS (
SELECT `carto-os`.carto.ST_GENERATEPOINTS(blockgroup_geom, CAST(total_pop AS INT64)) AS points
FROM blocks
)
SELECT points FROM point_lists CROSS JOIN point_lists.points
WITH blocks AS (
SELECT d.total_pop, g.blockgroup_geom
FROM `bigquery-public-data.geo_census_blockgroups.us_blockgroups_national` AS g
INNER JOIN `bigquery-public-data.census_bureau_acs.blockgroup_2018_5yr` AS d ON g.geo_id = d.geo_id
WHERE g.county_name = 'Sonoma County'
),
point_lists AS (
SELECT carto.ST_GENERATEPOINTS(blockgroup_geom, CAST(total_pop AS INT64)) AS points
FROM blocks
)
SELECT points FROM point_lists CROSS JOIN point_lists.points
This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.

Last modified 8mo ago