CartoDB's SQL API: Row too large

Question

I am trying to run the following PostGIS query using CartoDB's SQL API:

SELECT 
the_geom,
ST_Area(ST_Intersection(the_geom::geography,ST_Buffer(ST_SetSRID(ST_MakePoint(-52.4379, -12.6235),4326)::geography, 25000))) / 1000000 AS polyarea FROM 
    dataset1
WHERE
    ST_Intersects(the_geom::geography,ST_Buffer(ST_SetSRID(ST_MakePoint(-52.4379, -12.6235),4326)::geography, 25000))

The same query works with two other datasets (one of which has 735,000 rows), but returns a

"row too large error"

for this particular dataset (which "only" has 160,400 rows).

Update: Is it possible that the features are too complex for the calculations?

You are buffering a geography datatype, ie, lat/lon, by 25000 degrees? That might be part of your problem. OK, I take it back, in psql such a query works, though it strikes me an non-sensical to have anything beyond (-180,180) and (-90,90). — John Powell, Aug 31 '15 at 09:16
The casting to geography is what actually allows me to use meters instead of degrees (see http://gis.stackexchange.com/questions/6681/what-are-the-pros-and-cons-of-postgis-geography-and-geometry-types). — vpgcloud, Aug 31 '15 at 09:38

score 2 · Answer 1 · answered Aug 31 '15 at 11:16

It's possible that the geometries are too large in terms of vertices, which will result in a very long WKB value that would need to be returned by the SQL API. If you don't need a high precision for them you could use ST_Simplify_PreserveTopology in order to reduce a little bit the length of the geometries.

score 2 · Accepted Answer · answered Aug 31 '15 at 12:43

2

My problem was fixed by removing the format parameter, so the SQL query itself actually wasn't at fault here (username.cartodb.com/api/v2/sql?format=GeoJSON&q=SELECT...).

answered Aug 31 '15 at 12:43

vpgcloud

323
1
11

CartoDB's SQL API: Row too large

2 Answers2