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?