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

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 inner_radius numeric,
  IN outer_radius numeric,
  IN number_of_spikes integer,
  IN additional_rotation_degrees numeric
) 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:


IF (inner_radius > outer_radius) THEN
  RAISE EXCEPTION 'Inner radius must not be greater than outer radius.';
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);
additional_rotation_angle = radians(additional_rotation_degrees);

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"
additional_rotation_angle numeric;
-- 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),
    ST_Y(rotation_point) + outer_radius)
  ), additional_rotation_angle);
baseline_inner = ST_RotateZ(
  ST_MakeLine(rotation_point, ST_MakePoint(
    ST_X(rotation_point), 
    ST_Y(rotation_point) + inner_radius)
  ), additional_rotation_angle + (angle/2));

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

Overview of the 900 stars inserted

Magnified view of the stars

Magnified view of the stars

A detail showing four stars we just created

A detail showing four stars we just created

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: