Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
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
Yes, i needet to limit the result, thank you – Dario Filipović Dec 24 '12 at 16:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.