Is there an efficient way to approximate ST_GeneratePoints in SpatiaLite?
Here's one approach I can think of: get the bounding box of a geometry, then generate random points within the box using the standard random() function, and finally find the random points that intersect with the original geometry.
That would probably need to be implemented recursively to ensure that you actually got the desired number of points.
First create a table with many random points across the layers extent:
create table random_points_temp as
with bounds as (
select Min(MbrMinX(geometry)) AS min_x, Max(MbrMaxX(geometry)) AS max_x, Min(MbrMinY(geometry)) AS min_y, Max(MbrMaxY(geometry)) AS max_y
from polygon_a)
SELECT row_number() over() as id, MakePoint(ABS(RANDOM()) % (max_x - min_x) + min_x, ABS(RANDOM()) % (max_y - min_y) + min_y, 3006) as geometry
from bounds
cross join polygon_a
cross join polygon_a
cross join polygon_a
cross join polygon_a
cross join polygon_a
cross join polygon_a;
SELECT RecoverGeometryColumn('random_points_temp', 'geometry', 3006, 'POINT', 'XY');
The multiple cross joining is to generate many random points. For some reason generate series doesnt work on my computer, it would be nicer.
Then join the polygon table with the random points and select n points for each polygon id:
create table random_points as
select * from (
select a.pkuid, row_number() over(partition by a.pkuid) as rnum, b.geometry
from polygon_a as a
left join random_points_temp as b
on st_intersects(a.geometry, b.geometry))
where rnum<51; --50 points per polygon
SELECT RecoverGeometryColumn('random_points', 'geometry', 3006, 'POINT', 'XY');
