I have a problem with aggregation of geometry either with ST_Collect, ST_Union, Array_Agg after doing intersection with transformed geometry. Faulty PSQL code goes like this:

SELECT ST_AsGeoJSON(st_collect(st_transform(pos,94326))) as pos,array_agg(cnt) as total
from planet_osm_te_lvl_3 
where ST_Intersects(pos,ST_Transform(ST_GeometryFromText('srid=94326;POLYGON ((14.0 44.0,  19.0 44.0, 19.0 46.0,  14.0 46.0,14.0 44.0))'),900913));

Column total is OK, but column pos returns null value. I figured out so far that the cause is the ST_Transform inside of ST_Intersect. The following code returns multipoint as expected:

SELECT ST_AsGeoJSON(st_collect(st_transform(pos,94326))), array_agg(cnt) 
from planet_osm_te_lvl_3 
where ST_Intersects(pos,ST_GeomFromText('POLYGON ((1737578.4691346379 5736562.273479164, 1737578.4691346379 5893061.453872256, 1932016.8447745405 5893061.453872256, 1932016.8447745405 5736562.273479164, 1737578.4691346379 5736562.273479164))',900913));

I have been busting my head over what could be the cause, but I can't figure it out. Any help would be greatly appreciated :)

OK, apparently there is possibly a buffer limit on the data that can be stored in a buffer/array. In my example there was around 500+ points that would return empty collection. Less than that would return multipoint.


Now I'm off to find a workaround :\

  • Add a count(*) ... You tested your hypothesis with a LIMIT clause? Example: SELECT ST_AsGeoJSON(st_collect(st_transform(pos,94326))), array_agg(cnt) FROM (SELECT * FROM planet_osm_te_lvl_3 ... LIMIT 100) as t; – Peter Krauss Sep 27 '12 at 16:59
  • If by "null" you mean "I can't see anything in the table view of PgAdmin", you don't actually have a null return, you're just hitting the PgAdmin "can't see anything bigger than 2K" visualization limitation. The answer is there but you can't see it. – Paul Ramsey Oct 28 '12 at 4:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.