13

I have a problem with postgresql and QGIS :

  • I can add points/lines/polygons and fillup field

But I can't :

  • delete points/lines/polygons
  • modify points/lines/polygons (move or modify field)

ex : dbname='Europe' host=localhost port=5432 user='postgres' sslmode=disable key='tid' estimatedmetadata=true srid=900913 type=Point table="public"."planet_osm_point" (way) sql=

QGIS is connected as admin level into database. With pdAdmin III I can do all (create, delete, modify...)

The postgresql database is imported from OSM with osm2pgsql.

On other layer, like .shp, I don't have this issue.

Any idea ?

Config : Ubuntu 14.04 / Posgresql 9.4 / QGIS 2.10

  • Hi Mathieu, can you clarify the exact meaning of "I can't". Are the tools deactivated, does it fail on layer save? And most important: is there an error message? Also have a look at the message log panel in the postgis tab. – Matthias Kuhn Aug 8 '15 at 9:25
  • offcourse I'm in editing mode to the concerned layer (yellow pen). Editing tools are all Grey except adding entity icon. the message I Get is : Le service prestataire ne supporte pas l'effacement: Le prestataire de données ne supporte pas l'effacement des objets – Mathieu Aug 8 '15 at 10:05
  • 1
    error message in English : Provider does not support deletion: Data provider does not support deleting features – Mathieu Aug 8 '15 at 10:12
  • 1
    Editing top icon : All Grey EXCEPT Add Feature – Mathieu Aug 8 '15 at 10:15
  • 1
    2015-08-08T12:10:22 1 Primary key is ctid - changing of existing features disabled (way; "public"."planet_osm_point") 2015-08-08T12:10:23 1 Primary key is ctid - changing of existing features disabled (way; "public"."planet_osm_line") 2015-08-08T12:10:23 1 Feature type or srid for way of "public"."planet_osm_polygon" could not be determined or was not requested. – Mathieu Aug 8 '15 at 10:20
11

Fixed. The problem was is no primary key.

In pgAdmin do this request.

ALTER TABLE tableName ADD PRIMARY KEY (id);

Example for planet_osm_line table and setting osm_id column ,as primary key :

ALTER TABLE planet_osm_line ADD PRIMARY KEY (osm_id);

untill osm_id is unique.

  • That's how you do it! – DPSSpatial Aug 26 '15 at 12:38
  • You think is right ? – Mathieu Aug 26 '15 at 13:11
  • I think so! I can test it out at work and report back, but now knowing how you imported the data, that is your workaround. One issue though - why are you editing this data in QGIS locally instead of editing the data directly in an OSM editor and contributing it back? – DPSSpatial Aug 26 '15 at 13:32
  • 1
    About OSM : is because we just need to generate an output map with our design/graphic specifications and adding some other paid data from providers (is not possible to make them public). QGIS is a perfect tool for that. Contributing is an other thing, that we'll do it in time. – Mathieu Aug 27 '15 at 14:49
9

The solution is to create a primary key, as already mentioned.

But by design, osm2pgsql does not garantee that the osm_id is unique. It can produce several rows with the same osm_id.

To edit planet_osm_* tables in QGIS, it is wise the add another id column, for example gid.

To add a unique gid column:

ALTER TABLE planet_osm_point ADD gid serial PRIMARY KEY;
ALTER TABLE planet_osm_line ADD gid serial PRIMARY KEY;
ALTER TABLE planet_osm_polygon ADD gid serial PRIMARY KEY;
ALTER TABLE planet_osm_roads ADD gid serial PRIMARY KEY;

QGIS has problems if the geometry column is just GEOMETRY. Some queries might fail. I found it useful to change the planet_osm_polygon geometry to MultiPolygon with:

ALTER TABLE planet_osm_polygon
    ALTER COLUMN way TYPE geometry(MultiPolygon,3857) USING ST_Multi(way);
3

I think this is a primary key issue. Your table should have one and that is recognized when you view the table through PGAdmin.

I had this same problem, but re-loaded the layer with a primary key field called ID (arbitrary) and all my edit tools are now active on the toolbar.

Using SQL you can add an OID column of 'serial' data type and create a primary key using this statement:

ALTER TABLE schema."Tablename"

ADD ID serial NOT NULL

ALTER TABLE schema."Tablename" ADD PRIMARY KEY (id);

Using a GUI, you can re-load your layer with a primary key that will be recognized by the database and thus PostGIS and QGIS:

  • open DB Manager
  • connect to your database
  • choose 'import layer/file'
  • check the 'Primary Key' box, and enter a name for the 'primary key' column that will store the unique ID's that will enable editing:

enter image description here

From there, go back to PGAdmin, connect to your database, and click on the table you've just re-created - you can now see if the OID column is recognized:

enter image description here

  • Under PGAdmin no primary key. How do I re-load the layer ? – Mathieu Aug 22 '15 at 6:06
  • @Mathieu I would use the DBManager plugin in QGIS and click the 'primary key' option and name the field OBJECTID or something similar. That should allow you to have editable features... if you have an existing unique ID field you want to use as your primary key, you can always re-set that later... – DPSSpatial Aug 22 '15 at 14:27
  • I'm certainly dumb but under DBmanager I didn't find where to set the primary key. Actually I have for "planetOSM polygon" (and others) a warning saying ther is no primary key. – Mathieu Aug 23 '15 at 18:45
  • ok - just updated my answer, but that is the GUI way of doing it... let me know if that works for you... – DPSSpatial Aug 24 '15 at 21:51
  • Thanks a lot for the procedure, and now i understand why I cannot do it : the database is imported using osm2pgsql directly into postgis from osm.pbf file. In you procedure is an external file that have to be load. In my case the database is already in postgis and saying no primary key like shown in your screen capture. So there is a way - when database already imported - to generate under pgsql a primary key ? – Mathieu Aug 26 '15 at 11:53

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.