2

I have elevation and measured values in attribute table of points layer.

Is there a way to convert this layer into PointZM (with elevation and measured) and assign elevation and measured values to PointZM geometry in QGIS/PostGIS?

How do I convert PointZM to lineZM geometry by preserving elevation and measured value of each vertex in lineZM in QGIS/PostGIS?

Points attributes

First method

2nd mehod

  • are those points stored in PostGIS already or external and loaded into QGIS? do you want those lines stored in PostGIS? if you expect multiple individual lines as result, what's the identifying attribute? – geozelot Apr 24 '18 at 14:49
  • Thanks ThingumaBob. Please see snapshot of attributes in original message above.Points are in shapefile. If possible I want output PointZM and LineZM in QGIS and PostGIS both and if not possible then in any one. Lines should be created based on road_code field and should have elevation and measured values in geometry.. – Mohsin Apr 24 '18 at 16:58
2

There are multiple ways to get to your LINESTRINGZMs. Two of those are:


ogr2ogr (not in QGIS)

Since I find it most straight forward here, I suggest a one-step solution using ogr2ogr, hoping that you are familiar with the general idea of command line tools.

In the Terminal/Shell of your choice*, run:

ogr2ogr -f "PostgreSQL" PG:"host=<your_host> port=<your_port> user=<user_with_create_rights> password=<your_password> dbname=<your_db_name>" "<path>/<to>/<shapefile>/<file_name>.shp" -nlt LINESTRINGZM -nln <new_table_name> -sql "SELECT road_code, MakeLine(MakePointZM(ST_X(GEOMETRY), ST_Y(GEOMETRY), elevation, distance)) AS geom FROM <file_name> GROUP BY road_code ORDER BY distance" -dialect sqlite

This will read the shapefile from disk, applies the -sql query on the shapefile data and imports the results (LINESTRINGZMs) into PostgreSQL/PostGIS (new table <new_table_name> with columns road_code and wkb_geometry).
This will connect the points by the distance attribute in ascending order, grouped by the road_code attribute.
Don´t forget to fill in all parts in <>. Note especially the use of <file_name> as name of your shapefile and name of the table to select from in the -sql statement.

Simply add that table to your QGIS project afterwards, e.g. via Browser Panel.

* On Windows, the necessary ogr2ogr.exe comes shipped with your QGIS installation and can be found in the '/bin' folder; open a Terminal in that folder and run the command from there.


Virtual Layers (in QGIS)

Create a temporary Virtual Layer with similar SQL and results:

  • Layers -> Add Layer -> Add/Edit Virtual Layer
  • Import -> choose point layer
  • Query -> SELECT road_code, MakeLine(MakePointZM(ST_X(geometry), ST_Y(geometry), elevation, distance)) AS geometry FROM <local_name_of_layer> GROUP BY road_code ORDER BY distance
  • click Test to verify, and if all is good, Ok -> done

Import created layer into PostGIS via Browser Panel or DB Manager or save to file.



Hope that helps; get back with any errors you encounter.

| improve this answer | |
  • I tried to test first method and it gives error "unable to open datasource". It is my points.ship file (see snapshot in my original question). Second method create virtual layer and there are no lines drawn after saving in shp format in QGIS (see snapshot above). May be I am missing some thing. At first step, First, I want to create pointzm shapefile from my existing points.shp file and then to create linezm file. – Mohsin Apr 26 '18 at 0:22
  • @Mohsin to ogr2ogr: look at the spelling there is a space (" C:\..." -> "C:\...")! to the Virtual Layers: my bad, you have to use the name 'geometry' instead of 'geom' in the query to refer to a geometry...I changed that in my answer. – geozelot Apr 26 '18 at 8:30
  • @Mohsin ah, and to your request for having separate shapefiles for both types: I didn't get that from your question; do you actually want those results in PostGIS at all? use SELECT id, Name, road_code, elevation, distance, MakePointZM(ST_X(geometry), ST_Y(geometry), elevation, distance) AS geometry FROM <name_of_layer> as query in the Virtual Layer to simply convert your shape to POINTZMs. you can also use that query (change 'geometry' in MakePointZM(...) to 'GEOMETRY') in ogr2ogr. – geozelot Apr 26 '18 at 8:44
  • Hi ThingnumaBob, thanks for your support. First method now works fine but when I add created layer from PostGIS then there are no any feature drawn and attribute table is also empty. Second method also works fine but no output features and when I open its attribute table attributes are there. Yeah I want output results in PostGIS and to view them in QGIS. – Mohsin Apr 26 '18 at 13:23
  • (Again my scenario: I have simple points shapefile with elevation, measured and other attributes values. First I want to convert this points file to PointZm so its geometry should have elevation and measured value should preserve my attributes also. Then to convert points or Pointzm file to LineZM so this LineZM should have elevation and measured value at each vertex or node. Hopefully you got it). If you say and need further clarification then I can add snapshots. – Mohsin Apr 26 '18 at 13:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.