Shortest Distance in a Plane - Practice Coding Problems

Shortest Distance in a Plane - Problem

Database Medium

You are given a database table Point2D containing coordinates of points on a 2D plane. Your task is to find the shortest distance between any two points in the table.

The distance between two points p1(x1, y1) and p2(x2, y2) is calculated using the Euclidean distance formula: sqrt((x2 - x1)² + (y2 - y1)²)

Table Schema:

Column Name	Type
x	int
y	int

The combination of (x, y) serves as the primary key, ensuring each point is unique.

Goal: Write a SQL query to return the shortest distance between any two distinct points, rounded to 2 decimal places.

Input & Output

example_1.sql — Basic Triangle

$ Input: Point2D table: | x | y | |---|---| |-1 |-1 | | 0 | 0 | |-1 |-2 |

› Output: | shortest_distance | |------------------| | 1.00 |

💡 Note: The three points form a triangle. Distance from (-1,-1) to (0,0) is √2 ≈ 1.41, from (-1,-1) to (-1,-2) is 1.00, and from (0,0) to (-1,-2) is √5 ≈ 2.24. The minimum is 1.00.

example_2.sql — Square Formation

$ Input: Point2D table: | x | y | |---|---| | 0 | 0 | | 1 | 0 | | 0 | 1 | | 1 | 1 |

› Output: | shortest_distance | |------------------| | 1.00 |

💡 Note: Four points forming a unit square. Adjacent sides have distance 1.00, while diagonals have distance √2 ≈ 1.41. The shortest distance is 1.00.

example_3.sql — Only Two Points

$ Input: Point2D table: | x | y | |---|---| | 3 | 4 | | 0 | 0 |

› Output: | shortest_distance | |------------------| | 5.00 |

💡 Note: With only two points (3,4) and (0,0), the distance is √(3² + 4²) = √25 = 5.00. This is also the classic 3-4-5 right triangle.

Constraints

At least 2 points will be present in the table
All coordinates are integers
-100 ≤ x, y ≤ 100
Maximum 1000 points in the table
Each (x, y) combination is unique

Visualization

Tap to expand

Understanding the Visualization

Plot all points

Visualize all points on a coordinate plane

Generate all pairs

Create lines between every possible pair of distinct points

Calculate distances

Apply Euclidean distance formula: √[(x₂-x₁)² + (y₂-y₁)²]

Find minimum

Identify the pair with the shortest distance

Key Takeaway

🎯 Key Insight: Use SQL's CROSS JOIN to generate all point pairs, then apply MIN() with the Euclidean distance formula to find the shortest distance efficiently in a single query.

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 25

The optimal approach uses a self-join SQL query to compare every point pair efficiently. The key insight is using CROSS JOIN with proper filtering conditions to avoid duplicates and self-comparisons, then applying the Euclidean distance formula with MIN() aggregation to find the shortest distance in one query.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force SQL Self-Join	O(n²)	O(n²)	Compare every point with every other point using cross join

Brute Force SQL Self-Join — Algorithm Steps

Create a cartesian product of the table with itself using CROSS JOIN
Add WHERE condition to ensure we don't compare a point with itself
Add condition to avoid duplicate pairs (p1.x < p2.x OR (p1.x = p2.x AND p1.y < p2.y))
Calculate Euclidean distance for each pair using SQRT and POWER functions
Use MIN() to find the shortest distance
Round the result to 2 decimal places using ROUND()

Visualization

Tap to expand

Step-by-Step Walkthrough

Set up points

Place all points on the coordinate plane

Compare pairs

For each point, calculate distance to all other points

Track minimum

Keep track of the smallest distance found so far

Return result

Round the minimum distance to 2 decimal places

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <math.h>
#include <float.h>

/* SQL Query for Shortest Distance in a Plane
SELECT ROUND(MIN(
    SQRT(
        POWER(p2.x - p1.x, 2) + POWER(p2.y - p1.y, 2)
    )
), 2) AS shortest_distance
FROM Point2D p1
CROSS JOIN Point2D p2
WHERE p1.x < p2.x OR (p1.x = p2.x AND p1.y < p2.y);
*/

typedef struct {
    int x, y;
} Point;

double shortestDistancePlane(Point* points, int n) {
    double minDist = DBL_MAX;
    
    for (int i = 0; i < n; i++) {
        for (int j = i + 1; j < n; j++) {
            double dx = points[j].x - points[i].x;
            double dy = points[j].y - points[i].y;
            double dist = sqrt(dx * dx + dy * dy);
            if (dist < minDist) {
                minDist = dist;
            }
        }
    }
    
    return round(minDist * 100.0) / 100.0;
}

int main() {
    Point points[] = {{-1, -1}, {0, 0}, {-1, -2}};
    int n = sizeof(points) / sizeof(points[0]);
    
    double result = shortestDistancePlane(points, n);
    printf("%.2f\n", result);
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

We compare each point with every other point, resulting in n*(n-1)/2 comparisons

⚠ Quadratic Growth

Space Complexity

O(n²)

The cross join creates n² intermediate rows before filtering

⚠ Quadratic Space

26.8K Views

Medium Frequency

~15 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force SQL Self-Join — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler