Skip to content

Instantly share code, notes, and snippets.

@andrewxhill
Created February 3, 2012 15:37
Show Gist options
  • Save andrewxhill/1730768 to your computer and use it in GitHub Desktop.
Save andrewxhill/1730768 to your computer and use it in GitHub Desktop.
fastest query for admin content
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