create spatial view in postgis on an ArcGIS table with ST_Geometry type

Question

I am working on PostgreSQL 9.4.5 with Postgis 2.2.3.

I work on a spatial table created on ArcGIS, with geometry type "ST_POINT". I would like to create a spatial view of that table in postgreSQL using aggregation on few columns. However, I can't figure out how to write the query on how to aggregate my ST_POINT geometries into a multipoint geometry (ST_MultiPoint) in my spatial view.

Below is the script I am running, without success. The non-spatial aggregation is working perfectly fine for what I need (I tested it removing the geometry column) but I keep running into errors on whatever I try to write for aggregating the points into multipoint.

CREATE OR REPLACE VIEW global_ops AS 
 SELECT string_agg(a.country, ', ') as country,
 a.rb,
 a.start_date,
 a.end_date,
 a.op_type,
 string_agg(a.activity, ', ') as activities,
 SUM(a.planned) as total_planned,
 a.links,
 a.year,
 a.project_num,
 SUM(a.actual_transfers) as total_actual,
 string_agg(a.transfer_modality, ', ') as modalities,
 sde.st_multipoint(st_AsText(''||a.shape||'')) as shape
 FROM global_ops_2018 a GROUP BY a.rb, a.start_date, 
 a.end_date, a.op_type, a.links, a.year, a.project_num, a.shape ORDER BY 
 a.project_num ASC;

This line here

sde.st_multipoint(st_AsText(''||a.shape||'')) as shape

is the one I'm trying to solve. Any ideas?

Answer 1

As per suggestions in the comments above, I have decided to convert my spatial table created with ArcGIS from ST_Geometry to PG_Geometry. After doing that, I was able to simply use a ST_Collect(a.shape) function for aggregating my points into a multipoint spatial view.

Thanks!