Skip site navigation (1) Skip section navigation (2)

Re: Calling the CTE for multiple inputs

From: "David Johnston" <polobo(at)yahoo(dot)com>
To: "'air'" <mojaveranger7(at)gmail(dot)com>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Calling the CTE for multiple inputs
Date: 2012-10-04 19:51:24
Message-ID: 021401cda269$a38a5470$ea9efd50$@yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-
> owner(at)postgresql(dot)org] On Behalf Of air
> Sent: Thursday, October 04, 2012 3:32 PM
> To: pgsql-sql(at)postgresql(dot)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.





In response to

pgsql-sql by date

Next:From: David JohnstonDate: 2012-10-04 19:57:28
Subject: Re: String Search
Previous:From: Fabio Ebner - Dna SolutionDate: 2012-10-04 19:40:48
Subject: String Search

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group