I have a multipoint adress layer creating problems with set-returning function cannot be used.

I tried :
Converting multipoint layer to point using PostGIS?
Multipoint vs point PostGIS

I tried converting the layer with vector > geometry tools > multipart to signle part in QGIS and sending it back to the PostGis dB but I still get the error. I tried as well by thicking the box "create single parts instead of multiparts" when sending the layer to postgis db with db manager in qgis but the error remains. And the type of my geometry is still MultiPoint:

I need this conversion to use ST_LineLocatePoint and get the following error. ERROR: line_locate_point: 2st arg isn't a point SQL state: XX000

3 Answers 3


If you want to do it with query, then you can possibly do it this way

1- create new column in your table with Point Type geometry

ALTER TABLE xyztable
  ADD COLUMN geom1 geometry (Point, SRID)

2- Now you need to update your the new column with converted geometries. You can get the point geometry using st_dump function, but you can not directly use it to update the geometry as update does not operate on the tuples So, it can be resolved by using this query

with temptable as
(select id, (ST_Dump(allp.points)).geom as gm
UPDATE xyztable 
set geom1= aa.gm
from temptable aa
where aa.id=xyztable.id;

3- and then you can rename your geom1 column to geom after deleting the original geom column.

Note: This all is to be done in postgis.


I used QGIS 3 environment toolbox

Consider that I'm not an advanced user, so things like projections and coordinate systems were overlooked.

I used the Multipart to single parts tool from the toolbox

Multipart to single part in the toolbox

I selected the file to convert from multipart to single part

Multipart to single part interface

As you can see when I hover on top of the resulting layer an indication of the point appears

Point shape

In contrast you can observe that in the original shape that I started with the indication of the MultiPoint appeared

enter image description here


Select (ST_DumpPoints(geom)).geom from your_name_table

  • Though technically correct, it could be helpful if you provided some explanation, why your solution is desirable and where OP went wrong.
    – Erik
    Commented Dec 17, 2018 at 13:08
  • postgis.net/docs/ST_DumpPoints.html Commented Dec 17, 2018 at 13:12
  • Also, please take in mind that links may be faulty sometimes. It is appreciated, if you copy relevant passages of text from your source while also providing the link, thank you.
    – Erik
    Commented Dec 17, 2018 at 13:16
  • I hope that the information will help to solve your problem, :-), Commented Dec 17, 2018 at 13:18

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.