Answer link
https://gis.stackexchange.com/a/307204/103524
Three algorithms using different approaches.
Git Repo: https://github.com/imran-5/Postgis-Custom
- Here is a simple and best approach, using the actual distance of coordinates from the x and y direction.
The internal algorithm use the WGS 1984 (4326) and result transform
to inserted SRID.
Function ===================================================================
CREATE OR REPLACE FUNCTION public.I_Grid_Point_Distance(geom public.geometry, x_side decimal, y_side decimal)
RETURNS public.geometry AS $BODY$
DECLARE
x_min decimal;
x_max decimal;
y_max decimal;
x decimal;
y decimal;
returnGeom public.geometry[];
i integer := -1;
srid integer := 4326;
input_srid integer;
BEGIN
CASE st_srid(geom) WHEN 0 THEN
geom := ST_SetSRID(geom, srid);
----RAISE NOTICE 'No SRID Found.';
ELSE
----RAISE NOTICE 'SRID Found.';
END CASE;
input_srid:=st_srid(geom);
geom := st_transform(geom, srid);
x_min := ST_XMin(geom);
x_max := ST_XMax(geom);
y_max := ST_YMax(geom);
y := ST_YMin(geom);
x := x_min;
i := i + 1;
returnGeom[i] := st_setsrid(ST_MakePoint(x, y), srid);
<<yloop>>
LOOP
IF (y > y_max) THEN
EXIT;
END IF;
CASE i WHEN 0 THEN
y := ST_Y(returnGeom[0]);
ELSE
y := ST_Y(ST_Project(st_setsrid(ST_MakePoint(x, y), srid), y_side, radians(0))::geometry);
END CASE;
x := x_min;
<<xloop>>
LOOP
IF (x > x_max) THEN
EXIT;
END IF;
i := i + 1;
returnGeom[i] := st_setsrid(ST_MakePoint(x, y), srid);
x := ST_X(ST_Project(st_setsrid(ST_MakePoint(x, y), srid), x_side, radians(90))::geometry);
END LOOP xloop;
END LOOP yloop;
RETURN
ST_CollectionExtract(st_transform(ST_Intersection(st_collect(returnGeom), geom), input_srid), 1);
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;
Use the function with a simple query, geometry must be valid and polygon, multi-polygons, or envelope
SELECT I_Grid_Point_Distance(geom, 50, 61) from polygons limit 1;
Result======================================================================

Second function based on Nicklas Avén algorithm. Have tried to handle any SRID.
I have applied the following changes in the algorithm.
- Separate variable for x and y direction for pixel size,
- New variable for calculates the distance in spheroid or ellipsoid.
- Input any SRID, function transform Geom to the working environment of
Spheroid or Ellipsoid Datum, then apply the distance to each side, get
the result and transform to input SRID.
Function ===================================================================
CREATE OR REPLACE FUNCTION I_Grid_Point(geom geometry, x_side decimal, y_side decimal, spheroid boolean default false)
RETURNS SETOF geometry AS $BODY$
DECLARE
x_max decimal;
y_max decimal;
x_min decimal;
y_min decimal;
srid integer := 4326;
input_srid integer;
BEGIN
CASE st_srid(geom) WHEN 0 THEN
geom := ST_SetSRID(geom, srid);
RAISE NOTICE 'SRID Not Found.';
ELSE
RAISE NOTICE 'SRID Found.';
END CASE;
CASE spheroid WHEN false THEN
RAISE NOTICE 'Spheroid False';
srid := 4326;
x_side := x_side / 100000;
y_side := y_side / 100000;
else
srid := 900913;
RAISE NOTICE 'Spheroid True';
END CASE;
input_srid:=st_srid(geom);
geom := st_transform(geom, srid);
x_max := ST_XMax(geom);
y_max := ST_YMax(geom);
x_min := ST_XMin(geom);
y_min := ST_YMin(geom);
RETURN QUERY
WITH res as (SELECT ST_SetSRID(ST_MakePoint(x, y), srid) point FROM
generate_series(x_min, x_max, x_side) as x,
generate_series(y_min, y_max, y_side) as y
WHERE st_intersects(geom, ST_SetSRID(ST_MakePoint(x, y), srid))
) select ST_TRANSFORM(ST_COLLECT(point), input_srid) from res;
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE STRICT;
Use it with a simple query.
SELECT I_Grid_Point(geom, 22, 15, false) from polygons;
Result===================================================================
- Function based on the series generator.
Function==================================================================
CREATE OR REPLACE FUNCTION I_Grid_Point_Series(geom geometry, x_side decimal, y_side decimal, spheroid boolean default false)
RETURNS SETOF geometry AS $BODY$
DECLARE
x_max decimal;
y_max decimal;
x_min decimal;
y_min decimal;
srid integer := 4326;
input_srid integer;
x_series DECIMAL;
y_series DECIMAL;
BEGIN
CASE st_srid(geom) WHEN 0 THEN
geom := ST_SetSRID(geom, srid);
RAISE NOTICE 'SRID Not Found.';
ELSE
RAISE NOTICE 'SRID Found.';
END CASE;
CASE spheroid WHEN false THEN
RAISE NOTICE 'Spheroid False';
else
srid := 900913;
RAISE NOTICE 'Spheroid True';
END CASE;
input_srid:=st_srid(geom);
geom := st_transform(geom, srid);
x_max := ST_XMax(geom);
y_max := ST_YMax(geom);
x_min := ST_XMin(geom);
y_min := ST_YMin(geom);
x_series := CEIL ( @( x_max - x_min ) / x_side);
y_series := CEIL ( @( y_max - y_min ) / y_side );
RETURN QUERY
SELECT st_collect(st_setsrid(ST_MakePoint(x * x_side + x_min, y*y_side + y_min), srid)) FROM
generate_series(0, x_series) as x,
generate_series(0, y_series) as y
WHERE st_intersects(st_setsrid(ST_MakePoint(x*x_side + x_min, y*y_side + y_min), srid), geom);
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE STRICT;
Use it with a simple query.
SELECT I_Grid_Point_Series(geom, 22, 15, false) from polygons;
Result==========================================================================
