3

I am working on wind rose data. I have some points on lines (streets) in my Postgres/PostGIS DB. The sample data scenario is:

A point on line (street)

First, I computed street orientation of this line using PostGIS commands ST_StartPoint(), ST_EndPoint() and ST_Azimuth() like this:

Select
-- line_dir = line direction (orientation) in degrees
(degrees(ST_Azimuth(line_end_pt, line_start_pt)))::int As line_dir,
-- assign the line direction as the lower bound of sector 1 (of 30 degree)
(degrees(ST_Azimuth(line_end_pt, line_start_pt)))::int As   lower_bound_sector1,
-- add 30 degree to lower bound to get the upper bound as each sector is of 30 degree max.
(degrees(ST_Azimuth(line_end_pt, line_start_pt)))::int + 30 As upper_bound

from
(
Select
     pt_on_line.geom as pt,
     line.geom As line,
     ST_StartPoint(ST_LineMerge(line.geom)) As line_start_pt,
     ST_EndPoint(ST_LineMerge(line.geom)) As line_end_pt
from pt_on_line
Left join line on ST_DWithin(pt_on_line.geom, line.geom, 0.1)
)
As foo;

Now starting with this street orientation, I need to generate a wind rose of 12 sectors (each of 30 degree having its lower bound and upper bounds) as geometry around this point on line in a radius of 50 meter. In the above code, I have assigned street direction as lower bound of sector_1 and added 30 degree to the lower bound. This approach might be unrealistic though. The expected output could be something similar to this.

expected output

How can I draw these 12 wind sectors (geometry) around this point starting from line's orientation?


Using the answer of @MichalZimmermann, here is the screen shot of wind rose generation using PostGIS and visualized in QGIS.

Wind rose using PostGIS

4
  • Postgres is a RDBMS, not a drawing tool. You'll need to use something (e.g. D3) with an interface to your database in order to draw something like this. If you have computed the azimuth, you've already done most of the work PostGIS is going to do for you. – alphabetasoup Aug 30 '16 at 1:29
  • You definitely can do this with PostGIS and have any desktop GIS to show it to you. Seems like a pretty task for a blog post to me. – Michal Zimmermann Aug 30 '16 at 5:28
  • @RichardLaw: Of course I am aware that Postgres/PostGIS is not a drawing tool but QGIS (connected to Postgres DB) provides a good interface for visualization of PostGIS queries and that's what I have been doing. – khajlk Aug 30 '16 at 7:59
  • @MichalZimmermann: I am also of the opinion that this is possible with PostGIS/Postgres. Perhaps, I need to make use of ST_Rotate() etc. until full 360 degree starting from street direction but being a newbie I don't know how to do it – khajlk Aug 30 '16 at 8:03
9
CREATE OR REPLACE FUNCTION ST_WindRose(
    line geometry, 
    directions int, 
    radius numeric
)
RETURNS TABLE (
    id integer, 
    geom geometry(LINESTRING)
)
AS $ST_WindRose$
BEGIN
    IF directions % 2 <> 0 THEN
        RAISE EXCEPTION 'Odd number of directions found, please provide even number of directions instead.';
    END IF;

    IF radius > ST_Length(line) THEN
        RAISE EXCEPTION 'Inner circle radius is bigger than the wind rose diameter, please make it smaller.';
    END IF;

    RETURN QUERY 
    WITH rose AS (
        SELECT
            ST_Rotate(_line, radians(360) / directions * dirs.id, ST_Centroid(_line)) _line
        FROM (
            SELECT line _line
        ) a
        CROSS JOIN (
            SELECT generate_series(1, directions / 2) id
        ) dirs
    )
    SELECT
        row_number() OVER ()::integer id,
        _line geom
    FROM (
        SELECT _line FROM rose
        UNION ALL
        SELECT ST_ExteriorRing(ST_Buffer(ST_Centroid(line), radius, 30)) -- inner circle
        UNION ALL
        SELECT ST_ExteriorRing(ST_Buffer(ST_Centroid(line), ST_Length(line)/2, 30)) -- outer circle
    ) a;
END
$ST_WindRose$
LANGUAGE PLPGSQL;

Use it with

SELECT * FROM ST_WindRose(ST_MakeLine(ST_MakePoint(0,0), ST_MakePoint(0,1)), 12, 0.01);

Note it's half past 9pm here, it might not be the best solution ever, but it works.

3
  • Woah!! You are fantastically awesome dude! I just had to modify the function call for my line geometry input and it worked flawlessly! I have added "update" to my question with screen shot of output. One question, even though I see 12 segments in QGIS visualization but why only 8 rows are returned (instead of 12 rows for sectors) in Postgres and QGIS SQL window when I call this function with or without your input?? – khajlk Aug 30 '16 at 20:05
  • Half of the number are those lines going through the center, the other one is the inner circle and the last one is the outer circle. – Michal Zimmermann Aug 31 '16 at 7:02
  • Nice!! I just want to add that I came here from a link the blog entry mentioned by @MichalZimmermann , so I think it would be good to see the link back to it: zimmi.cz/posts/2016/postgis-custom-function-to-create-wind-rose – abu Mar 13 '18 at 12:08

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.