Re: [SQL] Calculate the ratio

[Andreas Forø Tollefsen]

Great. This works like I wanted.

Thanks!

[SQL] Calculate the ratio

[Andreas Forø Tollefsen]

Hi all,

This is probably quite simple. I want to calculate the ratio of each country
(gwcode) which experiences a 1 in one column (cf).

Structure:

gid; gridyear; gwcode; area; cf

I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area)
WHERE cf = 0 into another column and group by gridyear and gwcode.
Then i can see the ratio of the area of each country affected by cf.

Any suggestions?

Thanks.

A.

Re: [SQL] ARRAY_AGG and COUNT

[Andreas Forø Tollefsen]

Great. Thanks. Do you have a suggestion on how to ignore the group id's with
0 as value?
I dont want these to be counted.

Thanks!

2011/2/17 bricklen 

> On Thu, Feb 17, 2011 at 6:20 AM, Andreas Forø Tollefsen
>  wrote:
> > Hi all!
> >
> > I have tried the below query, but i cannot find a way to select only
> > DISTINCT into the ARRAY_AGG function. I also do not want 0 to be included
> in
> > the array or 0 to be counted.
> >
> > Code:
> >
> > SELECT priogrid_land.gid, priogrid_land.cell,
> > array_to_string(array_agg(g1id), ';') AS g1list,
> > array_to_string(array_agg(g2id), ';') AS g2list,
> > array_to_string(array_agg(g3id), ';') AS g3list,
> > count(distinct g1id) AS g1count, count(distinct g2id) AS g2count,
> > count(distinct g3id) AS g3count
> > INTO greg_list
> > FROM "GREG", priogrid_land WHERE ST_Intersects("GREG".the_geom,
> > priogrid_land.cell)
> > GROUP BY priogrid_land.gid, priogrid_land.cell;
> >
> >
> > As you see i.e. in g1list 482 is counted twice in row 2. 0 is also
> counted.
> > The distinct works for count, but not for array_agg.
> >
>
> I don't have a version earlier than 8.4, but in 8.4+ you can use
> DISTINCT in array_agg().
> Eg.
> array_to_string(array_agg(distinct g1id),';') as ...
>

[SQL] ARRAY_AGG and COUNT

[Andreas Forø Tollefsen]

Hi all!

I am working on a query to identify which group ids exists within a spatial
cell. In this case i have the GREG table which has polygon data and the
priogrid_land which have the cell polygon.
I want to identify which and how many GREG group ids exists within each of
the priogrid_land cells.

I have tried the below query, but i cannot find a way to select only
DISTINCT into the ARRAY_AGG function. I also do not want 0 to be included in
the array or 0 to be counted.

Any suggestions on how to accomplish the above?
Thanks!

Code:

SELECT priogrid_land.gid, priogrid_land.cell,
array_to_string(array_agg(g1id), ';') AS g1list,
array_to_string(array_agg(g2id), ';') AS g2list,
array_to_string(array_agg(g3id), ';') AS g3list,
count(distinct g1id) AS g1count, count(distinct g2id) AS g2count,
count(distinct g3id) AS g3count
INTO greg_list
FROM "GREG", priogrid_land WHERE ST_Intersects("GREG".the_geom,
priogrid_land.cell)
GROUP BY priogrid_land.gid, priogrid_land.cell;



This will give this result:
g1list; g2list; g3list; g1count; g2count; g3count
"482";"0";"0";1;1;1
"23;482;482";"0;0;0";"0;0;0";2;1;1
"1227;482;23";"0;0;0";"0;0;0";3;1;1
"1227;23;482;66";"0;0;0;0";"0;0;0;0";4;1;1
"1227;23;66";"0;0;0";"0;0;0";3;1;1

As you see i.e. in g1list 482 is counted twice in row 2. 0 is also counted.
The distinct works for count, but not for array_agg.

Cheers.

Re: [SQL] Create Datefield from 3 fields

[Andreas Forø Tollefsen]

Excellent. I did not see that.

Thank you

2010/10/6 Thomas Kellerer 

> Andreas Forø Tollefsen, 06.10.2010 13:11:
>
>  Hi.
>>
>> I am trying to create a datefield using YEAR, MONTH and DAY fields of type
>> integer.
>> I tried this query, but it did not give good results:
>> select to_date(gwsyear::text || gwsmonth::text || gwsday::text,
>> '-MM-DD') FROM cshapes
>>
>
> You are missing the dashes in the input string that you specify in the
> format mask
>
>
>  to_date(gwsyear::text ||'-'|| gwsmonth::text ||'-'|| gwsday::text,
> '-MM-DD')
>
>
> Thomas
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

[SQL] Create Datefield from 3 fields

[Andreas Forø Tollefsen]

Hi.

I am trying to create a datefield using YEAR, MONTH and DAY fields of type
integer.
I tried this query, but it did not give good results:
select to_date(gwsyear::text || gwsmonth::text || gwsday::text,
'-MM-DD') FROM cshapes
"1966526-01-01"
"7991903-12-13"
"1962831-01-01"
"194611-01-01"
"196211-01-01"

Any ideas on how to do this?