Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

http://stackoverflow.com/questions/9604832/how-to-convert-many-points-to-a-multipoint-in-postgis

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
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.