4

I have point data exported from Trimble Insphere as .shp. I am trying to paste the data into our PostGIS database but keep getting:

PostGIS error while adding features: ERROR: Column has M dimension but geometry does not

I have tried exporting to PointZM (edit: export maintains native point 25d geometry), set M, and a few other things with no luck. I don't have admin privileges for the database so I need to work it out on my end if possible. Any ideas?

Edit: Using QGIS 3.6

4
  • it's tedious to debug this without details of your data. check the column definition, and compare with the shapefile field definition. easiest way to manipulate the geometry IMO is via DB Manager (SpatiaLite), and then upload the virtual layer into PG
    – geozelot
    Sep 4, 2019 at 18:33
  • You'll have to excuse my general ignorance as I am still relatively new to QGIS. What details might I be able to include to assist everyone?
    – Jamie C
    Sep 4, 2019 at 18:58
  • Which qgis version is this?
    – ndawson
    Sep 4, 2019 at 19:50
  • @ndawson version 3.6
    – Jamie C
    Sep 4, 2019 at 20:21

2 Answers 2

2

Ideally, you would find the geometry type of the layer you are pasting into. Open the layer properties and click on the information tab. In the 'Information from provider' section, it will say what geometry type the layer is. I suspect it is PointM. You can then right click on your shapefile, save as, and set the SHPT to POINTM.

enter image description here

If, for whatever reason, you cannot convert your point properly, you will have to construct a new shapefile of type pointzm (or whatever type you need). To do this: add 4 new fields - x, y, z, m Calculate x coordinate into the x field, y coordinate into y field, z coordinate into z field, m coordinate to m field.

Here is a field calculator example for m: m($geometry)

I don't have any 25d files to work with, but I have read that QGIS has trouble accessing the z value directly. If this is the case, you will need to convert to text and extract the z value

i.e.: to_real(substr(geom_to_wkt($geometry),-2,1))

Use the 'Create Points Layer From Table' to create you new pointzm shapefile by selecting the appropriate columns (x,y,z,m) from the shapefile you constructed.

3
  • The target database is PointZM, the data is currently Point25D, and any export maintains the 25D geometry.
    – Jamie C
    Sep 4, 2019 at 20:05
  • Any new info should be added to your question using the edit button.
    – jbalk
    Sep 4, 2019 at 22:06
  • This worked perfectly. Thank you.
    – Jamie C
    Sep 9, 2019 at 18:56
1

Use the DB Manager to create a Virtual Layer from your shapefile:

SELECT <col_1>,
       ...,
       <col_n>,
       MakePointZM(ST_X(geometry), ST_Y(geometry), ST_Z(geometry), <defaul_M_float_value> [, <SRID>]) AS geometry
FROM   <loaded_shapefile_name>
;

You can easily change the ordinates to find the correct format.

Create the layer, and export to PG.

2
  • this is the best answer - much better than mine
    – jbalk
    Sep 6, 2019 at 17:58
  • @jbalk just a different approach, you've got two solid solutions using QGIS!
    – geozelot
    Sep 6, 2019 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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