When there is no implicit cast between two datatypes you need to state it explicitly. In your case ST_Force3D (or ST_Force_3D for PostGIS versions earlier than 2.1) should do the trick:
ALTER TABLE cl
ALTER COLUMN geom TYPE geometry(MultiLineStringZ)
USING ST_Force_3D(geom);
I'm guessing that you may have a few invalid geometries in your base table. You will need to do a bit of investigation.
Another thing to do would be select the well known text (ST_AsText) from your created table then look for anomalies, eg POINTs and LINEs in the collections.
You might want to add a few examples to your question.
Firstly I would make sure that your base geometries are valid
UPDATE gis_schema.sl_adm2
SET geom = ST_MakeValid(geom)
WHERE ST_IsValid(geom) IS FALSE
Then when you build your table add a check in to make sure only polygons are brought back.
You may want to consider using ST_Union rather than ST_Collect, but the depends on the result you are trying to achieve. I recall that MULTIPOLYGONs can't having touching or overlapping parts, but I can't find a reference to it at the moment. Even though it is slower ST_UNION will likely give you better results. Be aware that it will dissolve boundaries between touching polygons.
CREATE TABLE example_geom AS
SELECT sl_adm2_sums_alias.* , adm2_code as district_pcode
FROM gis_schema.sl_adm2
JOIN (
SELECT ST_Multi(st_collect(sl_adm2.geom)) AS geometry, sum(sl_sos.HEALTH) AS health_sum,
sum(sl_sos.PROTECTION) AS protection_sum, sum(sl_sos.WASH) AS wash_sum,
sum(sl_sos.cccm) AS cccm_sum, sum(sl_sos.food) AS food_sum,
sum(sl_sos.livelihood) AS livelihood_sum,
sum(sl_sos.education) AS education_sum,
sum(sl_sos.agriculture) AS agriculture_sum,
sum(sl_sos.HUMANITARIAN_ACCESS) AS humaccess_sum,
sum(sl_sos.logistics) AS logistics_sum,
sum(sl_sos.SHELTER_AND_NFI) AS shelter_and_wifi_sum, sl_sos.district
FROM sl_sos
JOIN gis_schema.sl_adm2 ON sl_sos.DISTRICT_PCODE = sl_adm2.adm2_code
WHERE ST_GeometryType(geom) in ('ST_Polygon','ST_MultiPolygon')
GROUP BY sl_sos.district)
sl_adm2_sums_alias ON
sl_adm2.adm2_name = sl_adm2_sums_alias.district;
Best Answer
There may be a more elegant solution, but can you import the points as 3-D points, and then create a 2-D point in Postgres? For example
ST_X( 3dpoint )andST_Y( 3dpoint )would give you the x and y coordinates so you could then create a point using:I don't know what form your initial data is in, so perhaps you can just use the x and y values by themselves.