Convert Lat/Lng to X and Y Coordinates Using SQL: A Practical Guide

Converting latitude/longitude coordinates into flat X/Y plane coordinates is essential for local mapping, spatial clustering, and distance calculations in SQL. While great-circle formulas like Haversine handle global-scale distances, projecting onto a 2D plane with a reference point gives you fast, practical X/Y values you can use directly in queries, charts, or export to mapping tools.

If you’re already familiar with the Excel-based approach, check out the companion guide: Converting Lat Lng to XY Coordinates: A Guide — it covers the same formulas in spreadsheet form with a live JavaScript demo.

The Math: Latitude/Longitude to X/Y Projection

The core idea is simple: pick a reference point (like a central location in your dataset), treat it as the origin (0,0), and project every other point relative to it.

Y Coordinate (North-South)

Latitude converts directly to distance — one degree of latitude is roughly the same length everywhere on Earth (~111.32 km):

Y = (Latitude - ReferenceLatitude) × 40000 / 360

Breakdown: 40,000 km is the approximate circumference of the Earth. Dividing by 360 degrees gives ~111.1 km per degree of latitude. Multiplying your latitude offset by this factor gives the Y distance in kilometres.

X Coordinate (East-West)

Longitude degrees are trickier — meridians converge at the poles, so one degree of longitude covers less distance as you move away from the equator. A cosine correction handles this:

X = (Longitude - ReferenceLongitude) × 40000 × cos((Latitude + ReferenceLatitude) × π / 360) / 360

Breakdown:

• Longitude - ReferenceLongitude — the east-west offset in degrees
• cos((Lat + RefLat) × π / 360) — the cosine of the average latitude (in radians) scales the longitude degree to actual ground distance. At the equator (lat 0°), cos(0) = 1 so no reduction. At 60° latitude, cos(60°) = 0.5, so longitude distances halve.
• Multiply by 40000/360 to convert degrees to kilometres

Why average latitude? Using the midpoint between the reference and target latitudes gives a balanced cosine correction that works well for both points. For small areas (a few km across), the cosine of either latitude alone works fine too.

Euclidean Distance from the Reference Point

Once you have X and Y, calculating straight-line (Euclidean) distance from your reference point is just Pythagoras:

Distance = SQRT(X² + Y²)

Note: this is a flat-plane approximation. For distances over tens of kilometres, especially near the poles, switch to the Haversine formula for better accuracy on a curved Earth.

Full SQL Implementation (T-SQL / SQL Server)

Here’s a complete production-grade query that calculates X, Y, and Euclidean distance for trapping event locations in South-East Queensland, Australia:

DECLARE @latconstant FLOAT = -27.6830342; -- Reference latitude
DECLARE @lonconstant FLOAT = 153.1466136; -- Reference longitude

SELECT 
    r.RunNumber,
    te.[Id],
    te.[Trap_Number],
    te.EggCount,
    p.Suburb,
    p.[Latitude],
    p.[Longitude],
    ((p.[Longitude] - @lonconstant) * 40000 * COS((p.[Latitude] + @latconstant) * PI() / 360) / 360) AS X,
    ((p.[Latitude] - @latconstant) * 40000 / 360) AS Y,
    -- Calculate the distance from the reference point
    SQRT(
        POWER(((p.[Longitude] - @lonconstant) * 40000 * COS((p.[Latitude] + @latconstant) * PI() / 360) / 360), 2) +
        POWER(((p.[Latitude] - @latconstant) * 40000 / 360), 2)
    ) AS DistanceFromReference
FROM 
    [EntoMSouth].[dbo].[TrappingEvents] te
JOIN 
    [EntoMSouth].[dbo].[Person] p
ON 
    te.[PersonId] = p.[Id]
JOIN 
    [EntoMSouth].[dbo].[Run] r
ON 
    te.RunId = r.Id
WHERE 
    te.[Trap_Number] IN ('2237275261', '2237275847', ...)
ORDER BY 
    r.RunNumber DESC;

What this query does:

• Sets a reference point at -27.6830342, 153.1466136 (a location in the study area)
• Joins three tables: TrappingEvents (trap data), Person (location/suburb info), and Run (survey run metadata)
• Calculates X (east-west offset) and Y (north-south offset) in kilometres for each trap location
• Computes DistanceFromReference as the straight-line distance from the reference point to each trap
• Filters by specific trap numbers and orders results by the most recent survey run

Porting to Other SQL Dialects

PostgreSQL

WITH ref AS (
    SELECT -27.6830342 AS lat0, 153.1466136 AS lon0
)
SELECT 
    p."Latitude",
    p."Longitude",
    ((p."Longitude" - ref.lon0) * 40000 * COS(RADIANS((p."Latitude" + ref.lat0) / 2.0)) / 360.0) AS X,
    ((p."Latitude" - ref.lat0) * 40000.0 / 360.0) AS Y
FROM 
    "Person" p
CROSS JOIN ref;

PostgreSQL has RADIANS() which is cleaner than multiplying by PI()/360. Note the / 2.0 to average the latitudes before taking cosine — some PostgreSQL users prefer this explicit midpoint approach.

MySQL / MariaDB

SET @lat0 = -27.6830342;
SET @lon0 = 153.1466136;

SELECT 
    Latitude,
    Longitude,
    ((Longitude - @lon0) * 40000 * COS(RADIANS((Latitude + @lat0) / 2.0)) / 360.0) AS X,
    ((Latitude - @lat0) * 40000.0 / 360.0) AS Y,
    SQRT(
        POW((Longitude - @lon0) * 40000 * COS(RADIANS((Latitude + @lat0) / 2.0)) / 360.0, 2) +
        POW((Latitude - @lat0) * 40000.0 / 360.0, 2)
    ) AS Distance_km
FROM Person;

MySQL also supports RADIANS(). Session variables (SET @var) work well for reference points.

SQLite

SELECT 
    Latitude,
    Longitude,
    ((Longitude - -153.1466136) * 40000 * COS((Latitude + -27.6830342) * 3.1415926535 / 360.0) / 360.0) AS X,
    ((Latitude - -27.6830342) * 40000.0 / 360.0) AS Y
FROM Person;

SQLite has no PI() or RADIANS() — use the literal 3.1415926535 instead. Variables aren’t supported in standard SQLite, so hardcode the reference values or wrap in your application logic.

When to Use This Approach (and When Not To)

• Use X/Y projection for local datasets (within ~50 km) where you need fast SQL calculations, want to cluster points, or need to export to a 2D chart
• Use Haversine or Vincenty formulas when accuracy over long distances matters (thousands of km), or when working near the poles where the flat-Earth assumption breaks down
• Use the built-in geography/spatial types (PostGIS in PostgreSQL, GEOGRAPHY in SQL Server, ST_Distance_Sphere in MySQL) if your database has them — they handle all the math internally with full ellipsoid accuracy

Key Takeaways

• Latitude offset × 111.1 km/degree = Y (north-south distance)
• Longitude offset × 111.1 km/degree × cos(average latitude) = X (east-west distance)
• The cosine correction is the critical step — skipping it will stretch your X axis, especially at higher latitudes
• The formula works in any SQL dialect — just adapt PI(), RADIANS(), and variable syntax
• For the Excel-based version of this same technique (with a live JavaScript demo embedded), see the companion post: Converting Lat Lng to XY Coordinates: A Guide