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

  • 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 – ThingumaBob Sep 4 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 at 18:58
  • Which qgis version is this? – ndawson Sep 4 at 19:50
  • @ndawson version 3.6 – Jamie C Sep 4 at 20:21

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.

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

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

SELECT <col_1>,
       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.

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

In this situation I would try the following :

Use a PostGres/Postgis Database you have access in (I.e can create table\ import data) You can install a basic PostGres on your computer.

Import your shapefile as a new table using shp2pgsl in this database.

There you will see your data as a PostGresql table and infere your datatype. If it's not a POINTZM or POINTM as it should be,

you will be able to use a ST_MakePoint request to create a dataset which match your needs.

Update your answer and comment if you try it this way, I can help you.

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.