## Developing fun: ST_StarAtPoint for PostGIS

As christmas is coming closer, I want to share a small PostGIS-function that generates a star at a given point with some other specified characteristics. This function has not been built with a special use-case in mind, but just for fun. Yet, I hope someone finds this functionality interesting.

OK, so what is a star? When I am talking about a star I mean a polygon consisting of at least 3 spikes which are equally distributed along a circle. The following picture describes best what I want I understand as a “star”: Anatomy of the stars created by the function

With Postgis I want to create stars that look like the the green polygon above. When one defines the center coordinate (the black spot in the center of the star), the number of spikes, and the length of both inner and outer radius (red lines), and an additional offset (in degrees, see the black arrow on top of the star), we are able to create all needed corners of the polygon (marke yellow above).

I will show two ways to compute everything we need, a readable, “processual” one and a more “geeky” one with only one line of SQL.

Let’s create an empty dummy function with all arguments we need, the logic will be inserted later:

``````
CREATE OR REPLACE FUNCTION ST_StarAtPoint(
IN point geometry,
IN number_of_spikes integer,
) RETURNS GEOMETRY AS
\$body\$
DECLARE
BEGIN
-- For the moment we'll always return a simple point geometry
RETURN 'POINT(0 0)'::geometry;
END;
\$body\$
LANGUAGE plpgsql;
``````

We are now able to call the above function. This does not make too much sense right now, but to test for typos it should do the trick:

``SELECT ST_AsText(ST_StarAtPoint('POINT(0 0)'::geometry, 1, 5, 8, 0));``

Now let’s add code to check if we have been called with valid parameters:

``````
END IF;
IF (number_of_spikes < 3) THEN
RAISE EXCEPTION 'A star must have at least three spikes.';
END IF;``````

Also, we will calculate the radians of the given degrees

``````angle = radians(360/number_of_corners);

For all the variables we need to have a valid declaration. We’ll add four more variables to the declarations, the declartion section now looks like this:

``````
-- the star geometry variable as WKT
star_geometry_wkt text := '';
-- a loop counter
i integer := 0;
-- the angle defined by 360° / number of spikes
angle numeric;
-- an optional "offset"
-- the baseline we will rotate around for the inner points
baseline_inner geometry;
-- the baseline we will rotate around for the outerpoints
baseline_outer geometry;
-- the point we rotate around
rotation_point geometry := 'POINT(0 0)'::geometry;
``````
``````
-- construction of the lines to rotate
baseline_outer = ST_RotateZ(
ST_MakeLine(rotation_point, ST_MakePoint(
ST_X(rotation_point),
baseline_inner = ST_RotateZ(
ST_MakeLine(rotation_point, ST_MakePoint(
ST_X(rotation_point),
``````

Now baseline_outer should contain a linestring from Point(0 0) to Point(0 radius_outer) (think twelve o’ clock) and slightly rotated counter-clockwise. The same is true for baseline_inner, but this line is roitated half the general angle agin counter-clockwise, so that inner points always lie exactly in the middle between two outer points.

We are now iterating through all needed spikes and add WKT-strings (not so elegant, I know) two our star_geometry_wkt-variable:

``````
WHILE (i < number_of_corners) LOOP
-- add point to polygon for outer-spike
-- note that we are adding the appropriate coordiantes from the input-geometry
star_geometry_wkt = star_geometry_wkt
|| (ST_X(ST_EndPoint(ST_RotateZ(baseline_outer, angle * i))) + ST_X(point))
|| ' '
|| ST_Y(ST_EndPoint(ST_RotateZ(baseline_outer, angle * i))) + ST_Y(point)
|| ',';
-- add point to polygon for inner-spike
-- note that we are adding the appropriate coordiantes from the input-geometry
star_geometry_wkt = star_geometry_wkt
|| (ST_X(ST_EndPoint(ST_RotateZ(baseline_inner, angle * i))) + ST_X(point))
|| ' '
|| ST_Y(ST_EndPoint(ST_RotateZ(baseline_inner, angle * i))) + ST_Y(point)
|| ',';
-- increment counter
i = i + 1;
END LOOP;
``````

Finally, let’s finish the WKT string and add the first point again so the linestring is closed.

``````
star_geometry_wkt = star_geometry_wkt ||  (ST_X(ST_EndPoint(baseline_outer)) + ST_X(point))  || ' ' ||  (ST_Y(ST_EndPoint(baseline_outer)) + ST_Y(point));
star_geometry_wkt = 'POLYGON((' || star_geometry_wkt || '))';
RETURN star_geometry_wkt::geometry;
``````

Here is the complete code of the star-function.

Of course it is possible to build the function with SQL only (Heres the source of ST_StarAtPoint with 1 SQL query only).

Now let’s see if any of the function works:

``````
CREATE TABLE star_test AS
SELECT
ST_StarAtPoint(ST_MakePoint(random()*400, random()*400),1, 1 + ii, iii, i) AS the_geom
FROM
generate_series(1,50) i,
generate_series(1,3) ii,
generate_series(5,10) iii;
-- add a primary key for QGIS
ALTER TABLE star_test ADD COLUMN id SERIAL;
ALTER TABLE star_test ADD PRIMARY KEY (id);
``````

When viewed in QGIS, one should see a star-field similar to this one: Overview of the 900 stars inserted Magnified view of the stars A detail showing four stars we just created