queries that finally work

[ghost]

@njmattes @ricardobarroslourenco finally, these are the PostGIS queries for the user's queries 1,2,3,4 in #9 (denoted as (1), (2), (3), (4) below) as well as the query in #20 (denoted (5) below) along with the beginning of their outputs:

(1)

SELECT ST_AsText(geom), val FROM (SELECT (ST_PixelAsCentroids(rast)).* FROM grid_data WHERE ST_Intersects(rast, ST_Polygon(ST_GeomFromText('LINESTRING(-180 -90, 180 -90, 180 90, -180 90, -180 -90)'), 4326)) and meta_id=1 and var_id=1 and time=‘1999-12-27 00:00:00-06’) foo;

      st_astext       |         val         
----------------------+---------------------
 POINT(-54.75 -10.25) |    1.80949997901917
 POINT(-54.25 -10.25) |    1.86230003833771
 POINT(-53.75 -10.25) |    1.33959996700287
 POINT(-53.25 -10.25) |   0.607500016689301

(2)

SELECT ST_AsText(geom), val from (select (ST_PixelAsCentroids(ST_Clip(rast, ST_Polygon(ST_GeomFromText('LINESTRING(-180 -90, 180 -90, 180 90, -180 90, -180 -90)'), 4326), TRUE))).* from grid_data where meta_id=1 and var_id=1 and time='1999-12-27 00:00:00-06') foo;

      st_astext       |         val         
----------------------+---------------------
 POINT(-54.75 -10.25) |    1.80949997901917
 POINT(-54.25 -10.25) |    1.86230003833771
 POINT(-53.75 -10.25) |    1.33959996700287
 POINT(-53.25 -10.25) |   0.607500016689301

(3)

select ST_SummaryStats(ST_Union(ST_Clip(rast, ST_Polygon(ST_GeomFromText('LINESTRING(-180 -90, 180 -90, 180 90, -180 90, -180 -90)'), 4326), true))) from grid_data where meta_id=1 and var_id=1 and time='1999-12-27 00:00:00-06';

 count |       sum        |       mean       |     stddev      | min |       max        
-------+------------------+------------------+-----------------+-----+------------------
 38946 | 119303.881053173 | 3.06331538677072 | 2.7952550302091 |   0 | 16.4675998687744

(4)

with foo as (select array(select ROW(ST_Union(ST_Clip(rast, ST_Polygon(ST_GeomFromText('LINESTRING(-180 -90, 180 -90, 180 90, -180 90, -180 -90)'), 4326))), 1)::rastbandarg as rast from grid_data where meta_id=1 and var_id=1 and time>=‘1999-12-27 00:00:00-06’ group by time))
SELECT ST_AsText(geom), val FROM (select (ST_PixelAsCentroids(ST_MapAlgebra((select * from foo)::rastbandarg[], 'st_stddev4ma(double precision[], int[], text[])'::regprocedure))).*) foo;

      st_astext       |         val         
----------------------+---------------------
 POINT(-151.75 66.75) |   0.232899501919746
 POINT(-147.75 66.75) |    1.13250231742859
 POINT(-145.25 66.75) |  0.0509117059409618
 POINT(13.75 66.75)   |   0.630547940731049

(5) and this is the query for #20:

with foo as (select st_astext((ST_PixelAsCentroids(rast)).geom) as pos, time, (ST_PixelAsCentroids(rast)).val as val from grid_data where meta_id=1 and var_id=1)
select pos, array_agg((time, val)) from foo group by foo.pos;

POINT(14.75 63.25)   | {"(\"2005-12-25 00:00:00-06\",6.3668999671936)","(\"2006-12-25 00:00:00-06\",4.3206000328064)",...,"(\"2004-12-25 00:00:00-06\",7.25860023498535)"}
POINT(-64.25 -39.25) | {"(\"2004-12-25 00:00:00-06\",3.94410014152527)","(\"2005-12-25 00:00:00-06\",3.51139998435974)",...,"(\"2003-12-26 00:00:00-06\",3.36849999427795)"}

[ianfoster]

Hurrah!

On Mar 15, 2016, at 12:54 PM, Severin Thaler notifications@github.com<mailto:notifications@github.com> wrote:

the following queries work and should be close to what you got with MongoDB:

1. SELECT ST_AsText(geom), val FROM (SELECT (ST_PixelAsCentroids(rast)).* FROM grid_data WHERE ST_Intersects(rast, ST_Polygon(ST_GeomFromText('LINESTRING(-180 -90, 180 -90, 180 90, -180 90, -180 -90)'), 4326)) and meta_id=1 and var_id=1 and time=‘1999-12-27 00:00:00-06’) foo;
2. SELECT ST_AsText(geom), val from (select (ST_PixelAsCentroids(ST_Clip(rast, ST_Polygon(ST_GeomFromText('LINESTRING(-180 -90, 180 -90, 180 90, -180 90, -180 -90)'), 4326), TRUE))).* from grid_data where meta_id=1 and var_id=1 and time='1999-12-27 00:00:00-06') foo;
3. select ST_SummaryStats(ST_Union(ST_Clip(rast, ST_Polygon(ST_GeomFromText('LINESTRING(-180 -90, 180 -90, 180 90, -180 90, -180 -90)'), 4326), true))) from grid_data where meta_id=1 and var_id=1 and time='1999-12-27 00:00:00-06';

— You are receiving this because you are subscribed to this thread. Reply to this email directly or view it on GitHubhttps://github.com/RDCEP/EDE/issues/23

[ghost]

yep, also got query 4 to work finally. working on the geojson specification now, see #24.

[ricardobarroslourenco]

Sorry about the huge delay on answering. To me it seems really nice. The only part is about database tuning, because it is taking about a minute while retrieving, but this can be done on a second stage.

[ghost]

yep.

[ghost]

these queries are incorporated