PostGIS – Extract Points from LINE/MULTILINE-STRING

linestringpointpolyline-creationpostgisqgis

I'd like to create a vectorLayer in QGIS to display all the points that form a LINESTRING or a MULTILINESTRING stored in a PostGIS DB.

I think that I need to transform all points of LINESTRING (or MULTILINESTRING) to POINT.

First Question:

Is there any PostGIS function to do that?

Second Question:

If I want to exclude duplicate points from the result POINT table, how can I do that?

Best Answer

To get point in order and link to orginal geometry use

SELECT (ST_DumpPoints(the_geom)).path as path, id, (ST_DumpPoints(the_geom)).geom FROM linestrings)

and remove duplicates from http://wiki.postgresql.org/wiki/Deleting_duplicates

Remember that you need to have one unique id for duplicate removing. If you don't have one you need to create it.

Related Solutions

[GIS] How to Blink a line on a map similarly like a point blinking on map through lat lon points

You can try using rules to select the needed lines, and then use symbolizer: {display:'none' } to hide matching lines, and "normal" symbolizer to show it on again.

and then using javascript timing (http://www.w3schools.com/js/js_timing.asp) that switches Rule objects that turn lines on and off.

[GIS] From trajectory of GPS points to multiline segment

The best solution up till now implements everything in SQL with postgres. It uses moving averages (windowing function) to smoothen out the trajectory plus some simplification of the computed path. Here's the SQL code:

  select 
  --    st_simplify(
            st_curvetoline(st_linetocurve(st_makeline(the_geom)),1000)
  --    ,0.00001)
  from (
     select orderid, st_makepoint(avg(x) over w, avg(y) over w) as the_geom
     from (
        select orderid, x(the_geom) as x, y(the_geom) as y
        from tracepoints
     ) as moving_averages
  window w as (order by orderid rows between 1 preceding and 1 following)
  ) as make_the_line

uncomment some lines to remove simplification. One may also decide to drop the curvetoline and linetocurve functions

Best Answer

Related Solutions

[GIS] How to Blink a line on a map similarly like a point blinking on map through lat lon points

[GIS] From trajectory of GPS points to multiline segment

Related Question