Created
February 3, 2012 15:37
-
-
Save andrewxhill/1730768 to your computer and use it in GitHub Desktop.
fastest query for admin content
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH foo AS (SELECT ST_AsGeoJSON(ST_Centroid(ST_Envelope(admin1_attributes_live.the_geom))) as center, ST_AsGeoJSON(ST_Envelope(admin1_attributes_live.the_geom)) as bbox, name_1 as name, id1 FROM admin1_attributes_live WHERE admin1_attributes_live.iso = 'IDN') SELECT center, bbox, foo.name, foo.id1, sum(global_4x_grid.total_incr) as total FROM foo,global_4x_grid WHERE global_4x_grid.iso='IDN' AND global_4x_grid.id1 = foo.id1 group by foo.id1, foo.name, center, bbox ORDER BY total; | |
--HUMAN READABLE | |
WITH foo AS | |
(SELECT | |
ST_AsGeoJSON(ST_Centroid(ST_Envelope(admin1_attributes_live.the_geom))) as center, | |
ST_AsGeoJSON(ST_Envelope(admin1_attributes_live.the_geom)) as bbox, | |
name_1 as name, | |
id1 | |
FROM | |
admin1_attributes_live | |
WHERE | |
admin1_attributes_live.iso = 'IDN' | |
) | |
SELECT | |
center, | |
bbox, | |
foo.name, | |
foo.id1, | |
sum(global_4x_grid.total_incr) as total | |
FROM | |
foo,global_4x_grid | |
WHERE | |
global_4x_grid.iso='IDN' AND | |
global_4x_grid.id1 = foo.id1 | |
group by | |
foo.id1, foo.name, center, bbox | |
order by | |
total | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment