DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:Thread-Index:Content-Language;
	b=WGlZn+rnZWieeuZaIdeHo0nfLPdCp9iBu3yXR+L1o7oF/lltxsEwBARIG9bkOVqhRuP3ZebMKUI8ZFYhT0RiGtqQkS0Kd5bPFSoytcim3NdhNALLaC0GLUUB4QBHHHnjBbW5iijJsX8Wz4aJQP4vWm3wsaMi6Quk5njtQB/ULPs=
	;
From: "David Johnston" <polobo@yahoo.com>
To: "'air'" <mojaveranger7@gmail.com>, <pgsql-sql@postgresql.org>
References: <1349379109760-5726661.post@n5.nabble.com>
In-Reply-To: <1349379109760-5726661.post@n5.nabble.com>
Subject: Re: Calling the CTE for multiple inputs
Date: Thu, 4 Oct 2012 15:51:24 -0400
Message-ID: <021401cda269$a38a5470$ea9efd50$@yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AQIQ6GT3dnJg7Cu+a14FqHWFSDCiqZcjALmA
Content-Language: en-us

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of air
> Sent: Thursday, October 04, 2012 3:32 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Calling the CTE for multiple inputs
> 
> I have a CTE that takes top left and bottom right latitude/longitude
values
> along with a start and end date and it then calculates the amount of user
> requests that came from those coordinates per hourly intervals between the
> given start and end date. However, I want to execute this query for about
> 2600 seperate 4-tuples of lat/lon corner values instead of typing them in
one-
> by-one. How would I do that? The code is as below:
> 
>     AND lat BETWEEN '40' AND '42'
>     AND lon BETWEEN '28' AND '30'

I don't really follow but if I understand correctly you want to generate
2600 distinct rows containing values like (40, 42, 28, 30)?

You could use "generate_series()" to generate each individual number along
with a row_number and then join them all together:

SELECT lat_low, lat_high, long_low, long_high
FROM                   (SELECT ROW_NUMBER() OVER () AS index,
generate_series(...) AS lat_low) lat_low_rel
NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS
lat_high) lat_high_rel
NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS
long_low) long_low_rel
NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS
long_high) long_high_rel

You may (probably will) need to move the generate_series into a FROM clause
in the sub-query but the concept holds.

Then in the main query you'd simply...

AND lat BETWEEN lat_low AND lat_high
AND lon BETWEEN long_low AND long_high

HTH

David J.