SQLite Forum

Finding overlap of boxed coordinates
Can be done by SQLite using multiple CASE constructions, or nested IF statements.  However, It's not going to be fast, compact, neat, or easy to debug. IMO it's a task which should be performed in a programming language rather than trying to make a complicated SQLite command.

Given two isometric (same axes) rectangles A and B, with boundaries l, r, b, t, intersection rectangle C is as follows:

<pre>C <-- A
IF B.l > C.l, C.b <-- B.l
IF B.b > C.b, C.b <-- B.b
IF B.r < C.r, C.r <-- B.r
IF B.t < C.t, C.t <-- B.t

If C.l >= C.r, there is no overlap
If C.b >= C.t, there is no overlap
Otherwise overlap is C.</pre>

Edge cases (e.g. left line of A is exactly the same as right line of B, or wrap around at -180/+180) must be considered within the context of your requirements.

Suppose you have the coordinates of a rectangle A, and your left longitude column of the database is indexed.  You can quickly dismiss a swathe of rectangles B which won't intersect with A using

<code>SELECT … WHERE B.l < A.r</code>