ST_Buffer conundrum

Question

I have a table in Postgresql with a geom_multiline column which, as the name suggests, is of type MULTILINESTRING and a second column geom_buffer which is a MULTIPOLYGON type. When I perform this query:

UPDATE locparktable
SET geom_buffer = 
ST_Multi(ST_Transform(ST_Buffer(st_transform(geom_multiline,2248),200), 
4326))

I get this error:

Error : ERROR: Geometry type (MultiLineString) does not match column type (LineString)

I have dropped and readded geom_buffer multiple times I even at one point added it as a generic GEOMETRY type to see if that would help and it did not. I'm at the end of my knowledge and Googling is failing to provide any useful help. Does anyone know what might be causing this error?

The only thing I can think of (though I have not yet verified it) is that there may be MULTILINESTRING data in our geom_line (LINESTRING) column for a feature that we're trying to update with this query (No, I don't know how that would happen, but let's say it did).

Does anyone know if that would cause this error?

Answer 1

You need to convert from Multi-linestring to linestring using ST_Dump as follows

UPDATE locparktable
SET geom_buffer = 
ST_Dump(ST_Multi(ST_Transform(ST_Buffer(st_transform(geom_multiline,2248),200),4326))).geom

Note: geom is the name of geometry column, it may be different in your case. Just have a look at the results of

st_dump(geom_multiline)

to be sure.