Re: Calling the CTE for multiple inputs - Mailing list pgsql-sql

From David Johnston
Subject Re: Calling the CTE for multiple inputs
Date
Msg-id 021401cda269$a38a5470$ea9efd50$@yahoo.com
Whole thread Raw
In response to Calling the CTE for multiple inputs  (air <mojaveranger7@gmail.com>)
List pgsql-sql
> -----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.






pgsql-sql by date:

Previous
From: Fabio Ebner - Dna Solution
Date:
Subject: String Search
Next
From: "David Johnston"
Date:
Subject: Re: String Search