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 :\