4 ms·

Do you mind linking the specific dataset? I agree very wide columns break a lot of tools but other columnar postgres forks should support this no problem. It so

Do you mind linking the specific dataset? I agree very wide columns break a lot of tools but other columnar postgres forks should support this no problem. It sounds like you didn't use Redshift, which I find surprising as it directly competes with BQ. Redshift has "super" columns that can be very large, even larger than the maximum supported by BigQuery. I constantly see folks finding out the hard way that PostGIS is really hard to beat. The fact that Trini/Presto and Spark have languished here is particularly telling.

ZeroCool2u 3y ago
It's FEMA's NFHL. I can't recall the specific layer of the GDB file, but you could probably figure it out. Try loading up Iowa into redshift and if that works for you I'd be quite surprised. My org has a very large AWS spend and we got to have a chat with some of their SWE's that work on the geospatial processing features for Redshift and Athena. We described what we needed and they said our only option was to aggregate the data first or drop the offending rows. Obviously we're not interested in compromising our work just to use a specific tool, so we opted for better tools. The crux of the issue was that the large problem column was the geometry itself. Specifically, MultiPolygon. You need to use the geometry datatype for this[1]. However, our MultiPolygon column was 10's to 100's of MB's. Well outside the max size for the Super datatype from what I can tell as it looks like that's 16 MB. [1]: https://docs.aws.amazon.com/redshift/latest/dg/GeometryType-function.html https://docs.aws.amazon.com/redshift/latest/dg/GeometryType-...
- beeboobaa3 3y ago
  Just split it out into multiple polygons, one per row. If you're using a relational database, do as relational databases do.
- xyzzy_plugh 3y ago
  The description you provide appears too large for even BigQuery.

ZeroCool2u 3y ago

It's FEMA's NFHL. I can't recall the specific layer of the GDB file, but you could probably figure it out. Try loading up Iowa into redshift and if that works for you I'd be quite surprised. My org has a very large AWS spend and we got to have a chat with some of their SWE's that work on the geospatial processing features for Redshift and Athena. We described what we needed and they said our only option was to aggregate the data first or drop the offending rows. Obviously we're not interested in compromising our work just to use a specific tool, so we opted for better tools. The crux of the issue was that the large problem column was the geometry itself. Specifically, MultiPolygon. You need to use the geometry datatype for this[1]. However, our MultiPolygon column was 10's to 100's of MB's. Well outside the max size for the Super datatype from what I can tell as it looks like that's 16 MB. [1]: https://docs.aws.amazon.com/redshift/latest/dg/GeometryType-function.html https://docs.aws.amazon.com/redshift/latest/dg/GeometryType-...

beeboobaa3 3y ago
Just split it out into multiple polygons, one per row. If you're using a relational database, do as relational databases do.
xyzzy_plugh 3y ago
The description you provide appears too large for even BigQuery.