Create triangle fishnet

Question

I am looking to create a fishnet of triangles with a specific x,y start location and angle. I essentially want my fishnet to follow the boundaries of a hexagon grid that I have. I found the Jennes repeat shape, but there is no option to anchor it in any way. I would also prefer a method in PostgreSQL. I have been unable to find an example of this using Google.

http://www.jennessent.com/arcgis/repeat_shapes.htm

Following is an image of the pattern that I am looking to repeat. Not only do I want to repeat this pattern, but I need the grid to have a specific anchor point and angle.

Answer 1

Here is the simple function for the above question can be used for multiple inputs of Geometry.

GitHub repo https://github.com/imran-5/Postgis-Custom

Function

CREATE OR REPLACE FUNCTION public.i_grid_triangle_shape2(
geom geometry,
distance numeric,
angle numeric DEFAULT 0)
RETURNS SETOF geometry 
LANGUAGE 'plpgsql'
COST 100
VOLATILE 
ROWS 1000
AS $BODY$
DECLARE
srid INTEGER := 4326;
input_srid INTEGER;
x_max DECIMAL;
y_max DECIMAL;
x_min DECIMAL;
y_min DECIMAL;
x_series DECIMAL; --absolute value
y_series DECIMAL; --absolute value
geom_rotate geometry;
geom_tri GEOMETRY := ST_GeomFromText(FORMAT('MULTIPOINT(%s %s, %s %s, %s %s, %s %s, %s %s)',
                                        (-distance*.5), (0), (distance*.5), (0), (distance*.5), (0),
                                        (-distance), (distance*.75), (0), (distance*.75)), 4326);
BEGIN
CASE ST_SRID(geom) WHEN 0 THEN
geom := ST_SetSRID(geom, 4326);
RAISE NOTICE 'SRID Not Found.';
ELSE
RAISE NOTICE 'SRID Found.';
END CASE;
input_srid:=st_srid(geom);
geom := st_transform(geom, srid);
geom_rotate := ST_Rotate(geom, angle, ST_Centroid(geom));
x_max := ST_XMax(geom_rotate);
y_max := ST_YMax(geom_rotate);
x_min := ST_XMin(geom_rotate);
y_min := ST_YMin(geom_rotate);
x_series := CEIL( @( x_max - x_min ) / distance)*.5;
y_series := CEIL( @( y_max - y_min ) / distance)*.75;
RETURN QUERY with foo as(
SELECT ST_Rotate(ST_Translate (geom_tri, x * (distance*2) + x_min, y * (distance*1.5) + y_min)
             , angle, ST_Centroid(geom)) grid
FROM
    generate_series ( 0, x_series, 1) AS x,
    generate_series ( 0, y_series, 1) AS y)
SELECT ST_Collect(ST_CollectionExtract(grid, 3))
FROM (SELECT ST_Intersection(grid, geom) grid
FROM (SELECT (st_dump(ST_DelaunayTriangles(st_collect(grid)))).geom as grid
FROM foo) AS bar
WHERE ST_intersects((grid), geom))as foo2;
END;
$BODY$;
ALTER FUNCTION public.I_Grid_Triangle_Shape2(geometry, numeric, numeric, boolean)
OWNER TO postgres;

Query. The first input is geometry, the second cell size and the third is an angle for rotation.

SELECT I_Grid_Triangle_Shape2(geom, .1, 14) from (SELECT ST_MakeEnvelope(10, 10, 11, 11, 4326) geom) as foo;

Result.

Function with a different shape.

-- FUNCTION: public.I_Grid_Triangle(geometry, numeric, numeric, boolean)
-- DROP FUNCTION public.I_Grid_Triangle(geometry, numeric, numeric, boolean);
CREATE OR REPLACE FUNCTION public.I_Grid_Triangle(
geom geometry,
distance numeric,
angle numeric default 0,
fit_envlope boolean default true)
RETURNS SETOF geometry
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
DECLARE
srid INTEGER := 4326;
input_srid INTEGER;
x_max DECIMAL;
y_max DECIMAL;
x_min DECIMAL;
y_min DECIMAL;
x_series DECIMAL; --absolute value
y_series DECIMAL; --absolute value
geom_rotate geometry;
geom_tri GEOMETRY := ST_GeomFromText(FORMAT('POLYGON((0 0, %s %s, %s %s, 0 0))',(distance * .5), (distance), (-distance * .5), (distance) ), srid);
BEGIN
CASE st_srid(geom) WHEN 0 THEN
geom := ST_SetSRID(geom, 4326);
RAISE NOTICE 'SRID Not Found.';
ELSE
RAISE NOTICE 'SRID Found.';
END CASE;
input_srid:=st_srid(geom);
geom := st_transform(geom, srid);
geom_rotate := ST_Rotate(geom, angle, ST_Centroid(geom));
x_max := ST_XMax(geom_rotate);
y_max := ST_YMax(geom_rotate);
x_min := ST_XMin(geom_rotate);
y_min := ST_YMin(geom_rotate);
x_series := CEIL( @( x_max - x_min ) / distance);
y_series := CEIL( @( y_max - y_min ) / distance);
RETURN QUERY
with foo as(SELECT
    ST_Rotate(ST_Translate (cell, x * distance + x_min, y * distance + y_min), angle, ST_Centroid(geom)) AS grid
FROM
    generate_series ( 0, x_series, 1) AS x,
    generate_series ( 0, y_series, 1) AS y,
    (
    SELECT geom_tri AS cell
        union
    SELECT st_rotate(geom_tri, pi(), distance * .25, distance * .5)  as cell
    ) as foo
)
SELECT CASE WHEN fit_envlope THEN
ST_CollectionExtract(ST_transform(st_collect(st_intersection(grid, geom)), input_srid), 3)
ELSE
ST_transform(st_collect(grid), input_srid) END FROM foo WHERE CASE WHEN
fit_envlope THEN st_intersects(grid, geom) ELSE st_intersects(grid, geom)
END;
END;
$BODY$;
ALTER FUNCTION public.I_Grid_Triangle(geometry, numeric, numeric, boolean)
OWNER TO postgres;

1. Query with envelope.

The first input is geometry, the second cell size, the third is an angle for rotation, and the fourth is to fit the envelope.

SELECT I_Grid_TriAngle(ST_MakeEnvelope(10, 10, 11, 11, 4326), .1, 100, true);

Result.

2. Query with any geom and fit envelope is false.

   SELECT I_Grid_TriAngle(geom, .0001, 0, false) from polygons

3. Query for a simple envelope.

   SELECT I_Grid_TriAngle(ST_Envelope(geom), .0001, 0, false) from polygons

4. Query for specific anchor point by making an envelope.

   SELECT I_Grid_TriAngle(ST_MakeEnvelope(10, 10, 11, 11, 4326), .01, 0, false)

Query 2 and 3 results.

Answer 2

My cartographic approach consists of three points:

1) Create a hex grid for your source polygon in a convenient way for you and name a new table, for example hex_grid.

link1 https://medium.com/@goldrydigital/hex-grid-algorithm-for-postgis-4ac45f61d093

link2 https://gist.github.com/alexanno/0cb7207bc4c6ca90bc75

link3 Create hex grids over a layer

link4 https://gist.github.com/grischard/f644d467fbe5052c1ca8f97f5008c4f8

link5 https://github.com/minus34/postgis-scripts/tree/master/hex-grid

2) Create on its basis a grid of equilateral triangles create table hex_grid_triangle as SELECT ST_Boundary((ST_Dump(ST_DelaunayTriangles(ST_Collect(ST_Centroid(geom))))).geom) geom from hex_grid; 3) Rotate your grid of equilateral triangles at a certain angle clockwise (in my example it is an angle of 45 degrees) relative to a user-defined point, for example, the coordinates of the lower left corner, create table hex_grid_triangle_rotate as select (ST_Rotate (ST_Collect(geom), -pi() / 4, ST_MakePoint(-124.796, 25.604))) as geom from hex_grid_triangle GROUP BY geom; see drawing

P.S. if it goes on like this, then we will become competitors Jeff Jenness and ArcGIS, I think him not please : -)...