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

2 Answers 2


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.


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
    Dec 17, 2018 at 13:08
  • 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
    Dec 17, 2018 at 13:16
  • I hope that the information will help to solve your problem, :-), Dec 17, 2018 at 13:18

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.