2

I want to make a geometry array or a new geometry which is combination of few other geometries. I have one table with id column, geometry column and start_cls column. So, I want to group my ids and geometries based on start_cls value. So, I executed following query.

SELECT array_agg(gid), st_union(geom), start_cls FROM public.edge_table_original group by start_cls limit 20

But in most of the cases, the union of geometry is giving null. I also tried with array_agg(geom) instead of st_union(geom). But the result is same. And I observed when the number of elements in the array is small, then it is giving the value of array_agg and st_union. But if the number of elements are significantly high, then it is not giving. May be there are some character limit problem. How to overcome this problem? I have added the screen shot of data output.

I also checked ST_AsTWKB. But this function returns bytea. But I need geometry or geometry[] output. As I shall use st_makeline on this geometry or geometry[].

Screen shot of the data output of this query

  • 2
    Are you sure the unerlying column is really null? Not the visual representation of pgAdmin/whatever software you use to view your data. – Michal Zimmermann Feb 27 '17 at 9:43
  • Ok, then how to check the values of this column? – LSG Feb 27 '17 at 9:48
  • 1
    As far as I know, there is no real limit on the size a geometry can be. You can always use a test like ST_Union(geom) IS NOT NULL or wrap ST_Union(geom) in some other function like ST_NumGeometries or ST_Area. You don't want to use array_agg with geom and, as Michal has said, pgAdmin is not a reliable way of determining whether you have data or not becuse of visual representation issues. – John Powell Feb 27 '17 at 9:53
  • 1
    @LSG SELECT COUNT(1) FROM your_table WHERE your_geom_column IS NULL – Michal Zimmermann Feb 27 '17 at 9:55
  • 1
    Well... What's the reasoning behind looking at random string of alphanumeric characters that doesn't say anything about what it actually represents? :) If you want to see the actual geometries, try QGIS or any other desktop GIS software. – Michal Zimmermann Feb 27 '17 at 10:33
6

The maximum size of any Postgres object, including the results of array_agg, ST_Collect, and other array-producing aggregates, is 1 GB.

If you hit this limit, these functions won't silently return null. They'll fail with a message like this:

ERROR: array size exceeds the maximum allowed (1073741823).

For what it's worth, this object size limit roughly corresponds to a 2D LineString with 67 million vertices. I wrote a post about this topic a while ago with a few more details.

  • Ha, so, this serves me right for saying no real limit. I would be interested to see how long it would take to do the intersection of two geometries at this limit. – John Powell Feb 27 '17 at 20:50

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.