3

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.

enter image description here

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

enter image description here

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

  • Perfect! Thanks a bunch! Competition keeps us on our toes! – D_C Mar 8 at 16:18
7

Here is the simple function for the above question can be use for multiple input of Geometry.

GitHub repo Link

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, second cell size and 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.

enter image description here

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, second cell size, third is an angle for rotation and forth is to fit the envelope.

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

Result.

enter image description here

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

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

enter image description here

  1. Query for a simple envelope.

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

  2. 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. Query 2 and 3 results.

  • thanks for the response. Definitely on the right track, please see my edited question for update. – D_C Mar 4 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.