3

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?

  • 1
    Why are you not using PG_Geometry if you have PostGIS installed? ArcGIS doesn't make much difference either way. ST_Geometry is the Esri proprietary format which may not like being manipulated in this way. – Michael Stimson Apr 26 '18 at 12:07
  • which geometry backend are you trying to use? PG_Geomery or ST_Geometry? – ziggy Apr 26 '18 at 12:19
  • The geometry of my table is ST_Geometry as the geodatabase I'm working on is setup with this geometry type. However, I could move it to another geodatabase that has PG_Geometry quite easily – Thierry Apr 26 '18 at 12:21
  • 2
    I would caution against advice to always use PG Geometry. Whether you use PG geometry or ST_Geometry is going to come down to how you are using your data. The size of your data. And if you are using ArcGIS Server, the version and which set of bugs come with that version. I've issued multiple cases through Esri Support--an outcome of one case was to use PostGIS geometry. The outcome of the other was to use Esri's ST_Geometry! I certainly prefer PostGIS geometry, but you have to be careful when Esri is in your stack. – Jay Cummins Apr 26 '18 at 13:27
  • 3
    @MichaelStimson - I maintain one system that uses both Esri ST_Geometry and PG Geometry and I can insert into tables with either data type (just can't mix them). For convenience, I have a few conversion functions between the two (e.g. geom := ST_GeomFromWKB(ST_AsBinary(l_st_geometry),3857); if I need to cross over. And there's a couple of large tables that I have that use st_geometry and I maintain a side table of the pg geometry via triggers--that way ArcGIS Server performs fast, but I can still issue postgis queries and join over to the st_geometry table. – Jay Cummins Apr 27 '18 at 0:42
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!

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.