ST_GeneratePoints implementation

[AliHmaou]

Summary

Requesting the addition of the ST_GeneratePoints function to the DuckDB spatial extension. This function generates a specified number of random points within a given geometry and is available in PostGis for instance.

Motivation

The absence of ST_GeneratePoints in DuckDB requires me to connect to Postgres for this functionality.
Integrating this function directly into DuckDB will streamline spatial data workflows by eliminating this dependency. It will also improve significantly my jobs runtime by eliminating steps.

Example of workaround (SQL)

-- Connecting Postgres to add a point by point column with st_generatepoints, with mandatory geometry handling

ATTACH 'dbname=postgres user=postgres host=127.0.0.1 port=5439' AS postgres_db (TYPE postgres);

drop table tmp_menages_esri50_discretise;
create table tmp_menages_esri50_discretise as
SELECT ST_GeometryN(st_GeneratePoints(st_geomfromtext(geom),1),1) point_individuel, a.*
FROM tmp_menages_esri50 a;

-- back to DuckDb to handle the end of the job
copy (select
st_geomfromhexwkb(point_individuel) geom_point_individuel, st_x(st_geomfromhexwkb(point_individuel)) x_geom_point_individuel, st_y(st_geomfromhexwkb(point_individuel)) y_geom_point_individuel, "type_logt" from postgres_db.tmp_menages_esri50_discretise
limit 10000000
)
to "C:\Users\ali.hmaou__LocalEnv\Projets\POINTILLES\export.geojson"
WITH (FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES')

[Maxxen]

Hello! Thanks for opening this feature request!

I agree that ST_GeneratePoints would be great to have. I've been wanting to implement it to make it easier to produce tests and benchmarks. I'll add it to my TODO list.

[CGenie]

#411

Apparently this function is there, but it's not documented :)

[AliHmaou]

Damn ! And the workaround in the discussion looks fine too. Gonna try it.