How to calculate the azimuth of a line of a multilinestring in PostGIS?

I have two spatially enabled tables in PostgreSQL with a geometry in each: First table is with POINT type, second is with MULTILINESTRING.

I need to calculate the azimuth of the LINESTRING part of a MULTILINESTRING, which is closest to the POINT.

So the question is: how do I extract the LINESTRING in question, and compute the azimuth?

• Understanding your question correct, you want to calculate the azimuth of a linestring that is part of a multilinestring? Like the azimuth of a stick that is closest to you when you play mikado =) Nov 20 '13 at 15:32
• Exactly. Sorry for the misunderstanding.
– 528
Nov 22 '13 at 6:49
• Did you tried my query already? I'm curious about it. Nov 30 '13 at 13:20
• Thank you, Stefan. The query (the shorter, edited one) breaks after a long time of execution, because it runs out of memory. (ERROR: could not write block 2456902 of temporary file: No space left on device HINT: Perhaps out of disk space?). I guess that it'd work if the dataset was smaller. Excuses for the delay in the reply, I had a hiatus from office.
– 528
Dec 13 '13 at 8:17
• Did you query your data with the first query as well? Maybe the ST_Dump function uses too much memory, handling with big data. Dec 13 '13 at 9:34

At first, have a look at the following PostGIS functions:

So, selecting your single objects and calculating the azimuth could be like this (I checked it with some sample data on my PC)

``````WITH multiobject AS
(SELECT
lnumber,
your_multilinestring_column AS geom,
generate_series(1,100) AS n
FROM your_multilinestring_table),
objects AS
(SELECT
lnumber,
n,
ST_GeometryN(geom, n) AS geometries
FROM  multiobject
WHERE n <= ST_NumGeometries(geom))

SELECT DISTINCT ON
(ST_Distance(your_point_column, geometries))
ST_Azimuth(ST_Startpoint(geometries), ST_Endpoint(geometries))
FROM objects, your_point_table
GROUP BY geometries, your_point_column
ORDER BY ST_Distance(your_point_column, geometries)
LIMIT 1;
``````

Let's explain this a little bit.

In the first part your MULTILINESTRING is selected and something like a sequence is build up with `generate_series` (maximum a geometry collection with 100 objects; n=100). You can change n when you have more than 100 objects in your multilinestring column.

With `ST_GeometryN(geom, n)` you select the single objects and you set the limit by using `n <= ST_NumGeometries(geom)`. So your query result is as big as the objects in your multilinestring table.

In the last part (maybe a bit tricky) you select the distance between your point data and the objects (`geometries`) in your multilinestring column. With `SELECT DISTINCT ON` you can remove duplicate rows from your result set. By ordering with `ST_Distance(your_point_column, geometries)` and limiting the result set by 1 you have the nearest "extracted" linestring on top.

The result in this query is the azimuth of a linestring that is nearest to all points from a table. Maybe you have to set some WHERE clauses with your IDs in your geometry tables (like gids or something).

EDIT

Keep it simple with ST_Dump. I think this is much easier.

``````WITH objects AS
(SELECT
(ST_Dump(your_multilinestring_column)).geom AS geometries
FROM your_multilinestring_table)

SELECT DISTINCT ON
(ST_Distance(your_point_column, geometries))
ST_Azimuth(ST_Startpoint(geometries), ST_Endpoint(geometries))
FROM objects, your_point_table
GROUP BY geometries, your_point_column
ORDER BY ST_Distance(your_point_column, geometries)
LIMIT 1;
``````

From the PostGIS refractions:

``````ST_Dump — Returns a set of geometry_dump (geom,path) rows, that make up a geometry
``````

The advantage of the first query is maybe, that you can select a distinct object with `n`. I don't know how to select single objects with `ST_Dump` using the `path`.

Am I misunderstanding? Isn't this just:

``````SELECT
ST_Asimuth(p.geom, ST_ClosestPoint(l.geom,p.geom)) AS azimuth
FROM line l, point p
``````

If you have more than one item in the line and point tables, the question of what condition you join them on becomes important, but as you described it, it's just one item in each.

• Isn't this the azimuth of an "imaginary" line between the point and the closest point from the linestring(multilinestring)? I understand it in that way, that he wants to know the azimuth of the linestring, that is closest to the point. That's what my query does. Nov 20 '13 at 15:35
• Azimuth of the closest segment, or azimuth to the closest segment? The former is stated, but seems like an odd thing to want to calculate. Nov 20 '13 at 20:30
• I am looking to calculate the azimuth of the closest segment. Sorry for the misunderstanding.
– 528
Nov 22 '13 at 6:49