spatial_data.sql

-- =====================================================
-- Which customers are in the Southern Hemisphere?
-- =====================================================
SELECT
	customernumber,
	customername,
	public.st_y (customerlocation) AS latitude
FROM
	customers
WHERE
	st_y (customerlocation) < 0;

-- =====================================================
-- Which US customers are south west of the New York office?
-- =====================================================
WITH
	ny AS (
		SELECT
			officelocation AS geom
		FROM
			offices
		WHERE
			city ILIKE 'nyc'
	)
SELECT
	c.customernumber,
	c.customername,
	st_astext (c.customerlocation) AS location
FROM
	customers c
	CROSS JOIN ny
WHERE
	c.country = 'USA'
	AND st_y (c.customerlocation) < st_y (ny.geom)
	AND st_x (c.customerlocation) < st_x (ny.geom);

-- =====================================================
-- Which customers are closest to the Tokyo office?
-- =====================================================
WITH
	tokyo AS (
		SELECT
			officelocation AS geom
		FROM
			offices
		WHERE
			city ILIKE 'tokyo'
	)
SELECT
	c.customernumber,
	c.customername,
	st_astext (c.customerlocation) AS location
FROM
	customers c
	CROSS JOIN tokyo
ORDER BY
	c.customerlocation <-> tokyo.geom;

-- =====================================================
-- Which French customer is furthest from the Paris office?
-- =====================================================
WITH
	paris AS (
		SELECT
			officelocation AS geom
		FROM
			offices
		WHERE
			city = 'Paris'
	)
SELECT
	c.customernumber,
	c.customername,
	st_astext (c.customerlocation) AS location,
	st_distance (c.customerlocation::geography, paris.geom::geography) AS distance_meters
FROM
	customers c
	CROSS JOIN paris
WHERE
	c.country = 'France'
ORDER BY
	distance_meters DESC
LIMIT
	1;

-- =====================================================
-- Who is the northernmost customer?
-- =====================================================
SELECT
	customernumber,
	customername,
	st_astext (customerlocation) AS location,
	st_y (customerlocation) AS latitude
FROM
	customers
ORDER BY
	st_y (customerlocation) DESC
LIMIT
	1;

-- =====================================================
-- What is the distance between the Paris and Boston offices?
-- =====================================================
SELECT
	st_distance (p1.officelocation::geography, p2.officelocation::geography) / 1000 AS distance_km
FROM
	offices p1
	CROSS JOIN offices p2
WHERE
	p1.city = 'Paris'
	AND p2.city = 'Boston';