Skip to content

Instantly share code, notes, and snippets.

@andrewxhill

andrewxhill/gist:1353003

Created November 9, 2011 20:58
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save andrewxhill/1353003 to your computer and use it in GitHub Desktop.
Save andrewxhill/1353003 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
SELECT
SUM(ST_Value(rast, 1, x, y)), count(*), SUM(ST_Value(rast, 1, x, y))/count(*)
FROM carbon_query_test_s CROSS JOIN
generate_series(1,10) As x CROSS JOIN generate_series(1,10) As y
WHERE rid in ( SELECT rid FROM carbon_query_test_s WHERE ST_Intersects(rast, ST_GeomFromText('MULTIPOLYGON(((18.511962890625 5.386335689520536,14.864501953125 2.141834969768584,18.775634765625 0.0769042737833478,22.510986328125 -0.5383221578577078,26.114501953125 0.8239462091017685,26.553955078125 3.3269862108134998,22.423095703125 6.085935520826564,18.511962890625 5.386335689520536)))',4326)) )
AND
ST_Intersects(
ST_Translate (ST_MakeEnvelope(
ST_UpperLeftX(rast), ST_UpperLeftY(rast),
ST_UpperLeftX(rast) + ST_ScaleX(rast),
ST_UpperLeftY(rast) + ST_ScaleY(rast), 4326
), ST_ScaleX(rast)*x, ST_ScaleY(rast)*y
),
ST_GeomFromText('MULTIPOLYGON(((18.511962890625 5.386335689520536,14.864501953125 2.141834969768584,18.775634765625 0.0769042737833478,22.510986328125 -0.5383221578577078,26.114501953125 0.8239462091017685,26.553955078125 3.3269862108134998,22.423095703125 6.085935520826564,18.511962890625 5.386335689520536)))',4326)
)
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment