Geek asks and answers

Postgis reordering line mix linestring / max_segment_length (linestring)?

I imported ~ 1000 paths from somewhere to my postgis database in the linestring field.

( EDIT ) My table is similar to

+---------+---------------+------------------+
| id(int) | name(varchar) | path(LINESTRING) |
+---------+---------------+------------------+
| 123     | foo           | 000002...        |
| 124     | bar           | 000002...        |

I had a problem that each of the paths was divided into pieces, and in some cases these pieces were involved.

Suppose a line with a line separated at points 50 and 70:

  • Chunk A: Points 1-50
  • Part B: Points 51-70
  • Chunk C: points 71-100

When I transferred this to my database, they were mixed, so the given linestring could end up as follows:

  • Chunk A: Points 1-50
  • Chunk C: points 71-100
  • Part B: Points 51-70

Thus, a jump from 50 to 71 is obtained, and the other from 100 to 51

(EDIT). , , , , , , linestrings , .

, (), SQL-, , , ( , openlayers) .

SQL , , ( , 5% )

EDIT3. , script , . , SQL, , , .

, max ?

: a bad way

, a good (fixed) way

EDIT4. EDIT3, , , ST_NPoints() ST_PointN(), . , , , . , .

SQL :

                                             |ordered by this|
+---------+---------------+------------------+---------------+
| id(int) | name(varchar) | path(LINESTRING) |  msbtcp(int)  |
+---------+---------------+------------------+---------------+
| 123     | foo           | 000002...        | 1000          |
| 124     | bar           | 000002...        | 800           |

* msbtcp : max_separation_between_two_consecutive_points(path)

+3
2

, , :

CREATE OR REPLACE FUNCTION max_distance_in_linestring(line geometry) RETURNS float as $BODY$
DECLARE
    i integer;
    n integer;
    d float;
    m float;
BEGIN
    d := 0;
    n := ST_NPoints(line);
    i := 2;
    LOOP
        EXIT WHEN i >= n;
        m := ST_Distance(ST_PointN(line,i-1),ST_PointN(line,i));
        -- use for lon,lats:
        -- m := ST_Distance(ST_PointN(line,i-1)::geography,ST_PointN(line,i)::geography);
        IF m > d THEN
            d := m;
        END IF;
        i := i + 1;
    END LOOP;
    RETURN d;
END;
$BODY$
LANGUAGE plpgsql;

SELECT max_distance_in_linestring('LINESTRING(0 0, 1 1, 2 2)'::geometry);
SELECT max_distance_in_linestring('LINESTRING(0 0, 4 3, 2 2)'::geometry);

ST_PointN :: .

SQL :

SELECT
  name, path 
FROM
  paths
ORDER BY
  max_distance_in_linestring(path) DESC
+3

, , , :

SELECT foo.gid as segment_a, bar.gid as segment_b
    FROM 
        segments AS foo, 
        (SELECT the_geom, gid FROM segments) AS bar
    WHERE 
        bar.gid != foo.gid AND ( -- avoid same segments  
        ST_DWithin( 
                ST_GeometryN(foo.the_geom,  ST_NumGeometries(foo.the_geom)) , -- last from foo
                ST_GeometryN(bar.the_geom,  ST_NumGeometries(bar.the_geom)) , -- last from bar
                0.00005 ) OR -- precision, depends of your SRID
        ST_DWithin( 
                ST_GeometryN(foo.the_geom,  1) , -- first from foo (start = 1 index)
                ST_GeometryN(bar.the_geom,  ST_NumGeometries(bar.the_geom)) , -- last from bar
                0.00005 ) OR -- precision, depends of your SRID
        ST_DWithin( 
                ST_GeometryN(foo.the_geom,  ST_NumGeometries(foo.the_geom)) , -- last from foo
                ST_GeometryN(bar.the_geom,  1) , -- first from bar
                0.00005 ) OR -- precision, depends of your SRID
        ST_DWithin( 
                ST_GeometryN(foo.the_geom,  1) , -- first from foo 
                ST_GeometryN(bar.the_geom,  1) , -- first from bar
                0.00005 ) ) -- precision, depends of your SRID

voilà...! ST_Union.

0

More articles:


All Articles