Skip to content

Instantly share code, notes, and snippets.

@knutole
Created September 23, 2016 00:04
Show Gist options
  • Save knutole/2ffad6636e2077febaf0e60f8ecdbdc9 to your computer and use it in GitHub Desktop.
Save knutole/2ffad6636e2077febaf0e60f8ecdbdc9 to your computer and use it in GitHub Desktop.
I simply need to clip a raster by a GeoJSON polygon. However, the query needs to fit into a Mapnik wrapper (of which I have no control).
# Full Mapnik query from PostGIS logs
SELECT ST_AsBinary(ST_Band(ST_Resample(ST_Clip("rast", ST_Expand(ST_SetSRID('BOX3D(743579.4111581944 8570731.10756024,900122.4450862355 8727274.141488284)'::box3d, 3857), greatest(abs(ST_ScaleX("rast")), abs(ST_ScaleY("rast"))))),305.748 * sign(ST_ScaleX("rast")),305.748 * sign(ST_ScaleY("rast")), 0, 0), 1)) AS geom FROM (SELECT ST_Clip(rast, st_transform(st_setsrid(ST_geomfromgeojson('{"type":"Polygon","coordinates":[[[9.019775390625,61.370409712010435],[7.998046875,61.17503266354878],[8.7890625,61.16443708638272],[9.29443359375,60.62471311568258],[10.469970703124998,61.63250678169624],[9.5361328125,61.201506036385375],[9.019775390625,61.370409712010435]]]}'), 4326), 3857)) FROM file_tpspmpkrwlsrcchskibg) as subquery WHERE "rast" && ST_SetSRID('BOX3D(743579.4111581944 8570731.10756024,900122.4450862355 8727274.141488284)'::box3d, 3857)
# The part which I can control
(SELECT ST_Clip(rast, st_transform(st_setsrid(ST_geomfromgeojson('{"type":"Polygon","coordinates":[[[9.019775390625,61.370409712010435],[7.998046875,61.17503266354878],[8.7890625,61.16443708638272],[9.29443359375,60.62471311568258],[10.469970703124998,61.63250678169624],[9.5361328125,61.201506036385375],[9.019775390625,61.370409712010435]]]}'), 4326), 3857)) FROM file_tpspmpkrwlsrcchskibg) as subquery
# The error I'm getting
2016-09-23 00:00:42 UTC [unknown] [5515-1] systemapic@zbcznatdqh ERROR: column "rast" does not exist at character 48
# When I run the subquery in PostGIS alone, it returns a raster, but with "st_clip" as column name. Could this be the problem?
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment