Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BC8C56334F7 for ; Mon, 30 Mar 2009 14:29:19 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 67118-08 for ; Mon, 30 Mar 2009 14:29:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f174.google.com (mail-fx0-f174.google.com [209.85.220.174]) by mail.postgresql.org (Postfix) with ESMTP id C11DD632CB4 for ; Mon, 30 Mar 2009 14:29:15 -0300 (ADT) Received: by fxm22 with SMTP id 22so3586847fxm.19 for ; Mon, 30 Mar 2009 10:29:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=Vqnxvtyh5L+tWfoI55J0hoUq86XEAUYuf7Bz8IA2IJQ=; b=vZdiggYv1lDkmkg+ehlWk0MbZILy6NnXaPfOIBlZ4nOJ4pCbblICKvlqVFM8X3jgjO 5zbkh+f9sygcVDjMGVS1AXkjzimlh/3hay5Xjf5Ap7bnfEuJA4Vvp7NB/Ozjm0NCqD2k XbYFRMB8Aq9YRplTRpPxNBIN+eVA9aZmFczVY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=WE3+gKGEM5SR0nQ8EBFs6HdChYSyJyfUUtjpW714hcTfmDbQxpzEsFkDH2lvaiAE05 /jEWwXZgCqHz4e95fa4UM8Vyyq5N+aRkUCimesVwVUXFOzdFoH7gvuoV8Z3da7s6nVsn 9uXk1kYg2MYueYohXg+RtDf51Cg4/ra9YeViQ= MIME-Version: 1.0 Received: by 10.103.214.8 with SMTP id r8mr1777614muq.92.1238429671015; Mon, 30 Mar 2009 09:14:31 -0700 (PDT) In-Reply-To: References: <331e40660903270936u5db00807sb1169df66443ade3@mail.gmail.com> <2298.1238179393@sss.pgh.pa.us> Date: Mon, 30 Mar 2009 19:14:30 +0300 Message-ID: <331e40660903300914x1c7747d0j94be7addeb2b31f5@mail.gmail.com> Subject: Re: Very specialised query From: =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= To: Matthew Wakeling Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=001636a7da939ecc8a04665860d1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/414 X-Sequence-Number: 33336 --001636a7da939ecc8a04665860d1 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi. Look, what I did mean by "symmetric" is that you don't need to make second part of query because you will get just same results simply by select case when n == 1 then id1 else id2 end, case when n == 2 then id1 else id2 end from ( SELECT l1.id AS id1, l2.id AS id2 FROM location l1, location l2 WHERE l1.objectid = 228000093 AND l2.objectid = 228000093 AND l1.id <> l2.id AND l1.start < l2.end AND l1.end > l2.start AND l1.start < l2.start) a, (values (1),(2)) b(n) (I may miss some border cases like when l1.start=l2.start and/or l1.end=l2.end, but this can be fixed by adding "=" to query). Look, You can have 4 types of intersections: a) 1s 2s 2e 1e - 2 inside 1 b) 2s 1s 1e 2e - 1 inside 2 (symmetric to (a), if you have 1,2 from (a) you can generate 2,1 for (b)) c) 1s 2s 1e 2e - 1 to the left of 2 d) 2s 1s 2e 1e - 2 to the left of 1 (symmetric to (c), if you have 1,2 from (c) you can generate 2,1 for (d)) The query above gives you results for (a) and (c) and you don't need any second part - simply add "symmetric" results. Correct me if I miss something. Best Regards, Vitalii Tymchyshyn --001636a7da939ecc8a04665860d1 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi.

Look, what I did mean by "symmetric" is that you don&#= 39;t need to make second part of query because you will get just same resul= ts simply by

select
case when n =3D=3D 1 then id1 else id2 end,=
case when n =3D=3D 2 then id1 else id2 end

from (
SELECT
=A0 =A0l1.id AS id1,
=A0 =A0l2.id AS id2
FROM
=A0 =A0location l1,
=A0 =A0location l2
WHERE
=A0 =A0 =A0 =A0l1.objectid =3D 228000093
=A0 =A0AND l2.objectid =3D 228000093
=A0 =A0AND l1.id <> = l2.id
=A0 =A0AND l1.start < l2.end
=A0 =A0AND l1.end > l2.start
=A0 =A0AND l1.start < l2.start) a, (values (1),(2)) b(n)

(I may = miss some border cases like when l1.start=3Dl2.start and/or l1.end=3Dl2.end= , but this can be fixed by adding "=3D" to query).

Look,= =A0 You can have 4 types of intersections:
a)=A0 1s 2s 2e 1e - 2 inside 1
b)=A0 2s 1s 1e 2e - 1 inside 2 (symmetric= to (a), if you have 1,2 from (a) you can generate 2,1 for (b))
c)=A0 1s= 2s 1e 2e - 1 to the left of 2
d)=A0 2s 1s 2e 1e - 2 to the left of 1 (s= ymmetric to (c), if you have 1,2 from (c) you can generate 2,1 for (d))

The query above gives you results for (a) and (c) and you don't nee= d=A0 any second part - simply add "symmetric" results.

Cor= rect me if I miss something.

Best Regards, Vitalii Tymchyshyn
--001636a7da939ecc8a04665860d1--