2

I have a point layer ('points') and a polygon layer ('multipolygons') . I want to load the points layer combined with the centroids of the polygon layer.

Im using GeoPackage and tryed to go a SQL way by inserting the centroids into the points table:

insert into points
select ST_Centroid(geom) as geometry, *
from multipolygons where "tourism"!='NULL'

This returns that error:

table points has 27 columns but 28 values were supplied

Im using the DB-Manager of QGIS:enter image description here

enter image description here

The GeoPackage was generated from osm.pbf by using ogr2ogr

I need to have the centroids and the points into one place. Also other solutions would be nice (virtual layer?)

Add: According to the answer of user30184, I wrote the following query, resulting in a expected attribute table but without relating geometries:

insert into points (geom, osm_id, name, amenity, historic, leisure, man_made, natural, office, place, sport, tourism)
select st_centroid(geom) as geom, osm_id, name, amenity, historic, leisure, man_made, natural, office, place, sport, tourism
from multipolygons 
where tourism in ('attraction','museum','camp_site','caravan_site','hostel','zoo','information','viewpoint')
  • 1
    It seems that the structure of the points table is not equal to the structure of the multipolygons table, bring them to one structure and everything should be in order ... – Cyril Jun 6 at 10:19
  • 1
    Is there also an issue with using != 'NULL' rather than is not NULL? Or do you actually have the string 'null' instead of an actual null value? – csk Jun 6 at 17:50
2

You have a few issues in your SQL query.

I suppose that your points and multipolygon tables have identical schema with 27 attributes. The reported error about not matching number of columns comes because

SELECT * selects all the columns (27), including the origin geometry "geom", and ST_Centroid(geom) as geometry ads one, makes 28 in total. You can't use select * but you must list all the attributes except the native geometry in your SQL statement. All attributes must be selected in the same order than they appear in the points table.

If you could get over the first error you may stop to next one telling you "Unique constraint failed:points.fid" which is thrown because you may have the same fid values in the points and multipolygons tables. For resolving that you must generate fids which are not used already in the points table. You must also convert the geometries from gpkg BLOB into SpatiaLite BLOB and back for making the ST_Centroid and inserts to work. Something like this should work

insert into points
select max(p.fid)+1,
AsGPB(ST_Centroid(castautomagic(pol.geom))) as geometry,
mpol.attr_1,mpol_attr_2,...mpol_attr_27)
from points p,multipolygons mpol;
  • why do I have to insert the same amount of columns? The schema of the tables are not the same. I added screenshots to the question – MAP Jun 6 at 15:48
  • 1
    You can't insert more attributes than what you have in the schema. You can make an insert with less columns but then you must improve your SQL w3schools.com/sql/sql_insert.asp and list the columns where to insert data. As your SQL stands now it wants data into all columns. – user30184 Jun 6 at 16:13
  • It may be that ogr2ogr with -addfields would suit for your use case gdal.org/programs/ogr2ogr.html#ogr2ogr once you know what your aim is really. – user30184 Jun 6 at 18:34
  • All right, I did the insert with filter following your example (added to the question). The result in the attribute table is like I expected, but I can not load it as geometries (no points relating to the features in the table) – MAP Jun 7 at 6:06
  • Your query looks correct to me. Unfortunately I can't say what goes wrong. Can you run just the select part and check if the result contains point geometries? – user30184 Jun 7 at 7:17

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.