5

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 =) – Stefan Nov 20 '13 at 15:32
  • Exactly. Sorry for the misunderstanding. – 528 Nov 22 '13 at 6:49
  • 1
    Did you tried my query already? I'm curious about it. – Stefan 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. – Stefan Dec 13 '13 at 9:34
3

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

ST_NumGeometries and ST_GeometryN.

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.

3

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. – Stefan 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. – Paul Ramsey 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

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.