PostGIS

-- Create the PostGIS extension
CREATE EXTENSION postgis;
 
-- Verify the installation
SELECT PostGIS_version();

CREATE TABLE spatial_data (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(GEOMETRY, 4326)  -- SRID 4326 is WGS84 spatial reference
);
 
-- Alternatively, use GEOGRAPHY type
CREATE TABLE geographic_data (
    id SERIAL PRIMARY KEY,
    location GEOGRAPHY(POINT, 4326)
);

INSERT INTO spatial_data (name, geom)
VALUES ('Central Park', ST_GeomFromText('POLYGON((-73.9687 40.8006, -73.9587 40.8006, -73.9587 40.7631, -73.9687 40.7631, -73.9687 40.8006))', 4326));

INSERT INTO spatial_data (name, geom)
VALUES ('London', ST_GeomFromWKB(E'\\\\x0101000020E6100000AAF1D24D6210C0BF1B2FDD2406C14940', 4326));

SELECT ST_AsText(geom) FROM spatial_data WHERE id = 1;

SELECT ST_AsGeoJSON(geom) FROM spatial_data WHERE id = 1;

SELECT name, ST_Area(geom)
FROM spatial_data
WHERE ST_GeometryType(geom) = 'ST_Polygon';

-- Distance between two points (in meters)
SELECT ST_Distance(
    ST_GeomFromText('POINT(-73.935242 40.730610)', 4326)::GEOGRAPHY,
    ST_GeomFromText('POINT(-74.005941 40.712784)', 4326)::GEOGRAPHY
);

-- 1. Create tables for testing
CREATE TABLE countries (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(POLYGON, 4326)
);
 
CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    country_id INT,
    geom GEOMETRY(POINT, 4326)
);
 
CREATE TABLE lakes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    area_sqkm FLOAT,
    geom GEOMETRY(POLYGON, 4326)
);
 
-- 2. Insert sample country data (simplified boundaries)
INSERT INTO countries (name, geom) VALUES
('France', ST_GeomFromText('POLYGON((2.55 51.1, -4.8 48.6, -1.8 43.4, 7.6 43.6, 8.7 42.6, 6.8 41.9, 3.0 42.4, 2.55 51.1))', 4326)),
('Germany', ST_GeomFromText('POLYGON((5.9 51.0, 6.1 50.2, 8.2 49.7, 10.2 47.3, 12.1 47.7, 13.0 48.8, 14.3 48.6, 15.0 50.9, 12.1 53.5, 8.7 53.9, 5.9 51.0))', 4326));
 
-- 3. Insert sample city data
INSERT INTO cities (name, country_id, geom) VALUES
('Paris', 1, ST_GeomFromText('POINT(2.3522 48.8566)', 4326)),
('Lyon', 1, ST_GeomFromText('POINT(4.8357 45.7640)', 4326)),
('Berlin', 2, ST_GeomFromText('POINT(13.4050 52.5200)', 4326)),
('Munich', 2, ST_GeomFromText('POINT(11.5820 48.1351)', 4326)),
('London', NULL, ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)); -- City not belonging to any country
 
-- 4. Insert sample lake data
INSERT INTO lakes (name, area_sqkm, geom) VALUES
('Lake Constance', 536, ST_GeomFromText('POLYGON((9.2 47.6, 9.5 47.5, 9.7 47.6, 9.5 47.7, 9.2 47.6))', 4326)),
('Lake Geneva', 580, ST_GeomFromText('POLYGON((6.5 46.4, 6.9 46.2, 7.2 46.4, 6.8 46.5, 6.5 46.4))', 4326));
 
-- 5. Examples for containment queries
 
-- Example 1: Find the country containing a specific point
SELECT name FROM countries
WHERE ST_Contains(geom, ST_GeomFromText('POINT(2.3522 48.8566)', 4326));
 
-- Example 2: Find lakes completely contained in France
SELECT l.name, l.area_sqkm
FROM lakes l, countries c
WHERE c.name = 'France' AND ST_Contains(c.geom, l.geom);
 
-- Example 3: Count the number of cities in each country (including countries with zero)
SELECT c.name AS country, COUNT(ci.id) AS city_count
FROM countries c
LEFT JOIN cities ci ON ST_Contains(c.geom, ci.geom)
GROUP BY c.id, c.name
ORDER BY city_count DESC;
 
-- Example 4: Find cities not contained in any country
SELECT name FROM cities
WHERE id NOT IN (
    SELECT ci.id FROM cities ci, countries co
    WHERE ST_Contains(co.geom, ci.geom))
AND country_id IS NULL;
 
-- Example 5: Find lakes spanning multiple countries
SELECT l.name, COUNT(DISTINCT c.id) AS country_count
FROM lakes l, countries c
WHERE ST_Intersects(l.geom, c.geom)
GROUP BY l.id, l.name
HAVING COUNT(DISTINCT c.id) > 1;
 
-- Example 6: Using ST_Within (reverse of ST_Contains)
-- Find the country containing Paris
SELECT c.name FROM countries c, cities ci
WHERE ci.name = 'Paris' AND ST_Within(ci.geom, c.geom);
 
-- Example 7: Find lakes fully within Germany and > 500 sq km
SELECT l.name, l.area_sqkm
FROM lakes l, countries c
WHERE c.name = 'Germany'
  AND ST_Contains(c.geom, l.geom)
  AND l.area_sqkm > 500;
 
-- Example 8: Validate if a city lies within the country's boundary (using JOIN condition)
SELECT ci.name AS city, co.name AS country
FROM cities ci JOIN countries co ON ST_Contains(co.geom, ci.geom)
WHERE ci.country_id = co.id OR ci.country_id IS NULL;
 
-- Example 9: Find countries sharing a border with France (using ST_Touches)
SELECT n.name
FROM countries f, countries n
WHERE f.name = 'France'
  AND f.id != n.id
  AND ST_Touches(f.geom, n.geom);
 
-- Example 10: Find lakes partially in France (using ST_Intersects)
SELECT DISTINCT l.name
FROM lakes l, countries c
WHERE c.name = 'France' AND ST_Intersects(l.geom, c.geom);

-- 1. Create test tables
CREATE TABLE roads (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    road_type VARCHAR(50),
    geom GEOMETRY(LINESTRING, 4326)
);
 
CREATE TABLE buildings (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    floors INT,
    geom GEOMETRY(POLYGON, 4326)
);
 
CREATE TABLE parks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    area_sqkm FLOAT,
    geom GEOMETRY(POLYGON, 4326)
);
 
-- 2. Insert road data
INSERT INTO roads (name, road_type, geom) VALUES
('Main Street', 'primary', ST_GeomFromText('LINESTRING(-118.481 34.028, -118.471 34.025, -118.461 34.020, -118.451 34.015)', 4326)),
('Oak Avenue', 'secondary', ST_GeomFromText('LINESTRING(-118.480 34.020, -118.475 34.025, -118.470 34.030, -118.465 34.035)', 4326)),
('Pine Road', 'tertiary', ST_GeomFromText('LINESTRING(-118.455 34.030, -118.460 34.025, -118.465 34.020, -118.470 34.015)', 4326));
 
-- 3. Insert building data
INSERT INTO buildings (name, floors, geom) VALUES
('City Hall', 5, ST_GeomFromText('POLYGON((-118.473 34.022, -118.470 34.022, -118.470 34.020, -118.473 34.020, -118.473 34.022))', 4326)),
('Library', 3, ST_GeomFromText('POLYGON((-118.463 34.018, -118.460 34.018, -118.460 34.015, -118.463 34.015, -118.463 34.018))', 4326)),
('Shopping Mall', 2, ST_GeomFromText('POLYGON((-118.468 34.028, -118.465 34.028, -118.465 34.025, -118.468 34.025, -118.468 34.028))', 4326));
 
-- 4. Insert park data
INSERT INTO parks (name, area_sqkm, geom) VALUES
('Central Park', 1.2, ST_GeomFromText('POLYGON((-118.478 34.032, -118.475 34.032, -118.475 34.028, -118.478 34.028, -118.478 34.032))', 4326)),
('Riverside Park', 0.8, ST_GeomFromText('POLYGON((-118.458 34.022, -118.455 34.022, -118.455 34.018, -118.458 34.018, -118.458 34.022))', 4326));
 
-- 5. Example queries for intersection relations
 
-- Example 1: Find buildings that intersect with a specific road
SELECT b.name, b.floors
FROM buildings b, roads r
WHERE r.name = 'Main Street' AND ST_Intersects(b.geom, r.geom);
 
-- Example 2: Find roads crossing through a specific park
SELECT r.name, r.road_type
FROM roads r, parks p
WHERE p.name = 'Central Park' AND ST_Intersects(r.geom, p.geom);
 
-- Example 3: Count the number of roads intersecting each park
SELECT p.name, COUNT(r.id) AS road_count
FROM parks p
LEFT JOIN roads r ON ST_Intersects(r.geom, p.geom)
GROUP BY p.id, p.name
ORDER BY road_count DESC;
 
-- Example 4: Find roads that do not intersect with any building
SELECT r.name
FROM roads r
WHERE NOT EXISTS (
    SELECT 1 FROM buildings b
    WHERE ST_Intersects(b.geom, r.geom)
);
 
-- Example 5: Find roads intersecting with more than one park
SELECT r.name, COUNT(DISTINCT p.id) AS park_count
FROM roads r, parks p
WHERE ST_Intersects(r.geom, p.geom)
GROUP BY r.id, r.name
HAVING COUNT(DISTINCT p.id) > 1;
 
-- Example 6: Use ST_Crosses to find roads that truly cross through buildings (not just touching)
SELECT r.name, b.name
FROM roads r, buildings b
WHERE ST_Crosses(r.geom, b.geom);
 
-- Example 7: Find parks that intersect any road and have an area greater than 1 sq km
SELECT p.name, p.area_sqkm
FROM parks p, roads r
WHERE ST_Intersects(p.geom, r.geom) AND p.area_sqkm > 1;
 
-- Example 8: Use ST_Overlaps to find partially overlapping buildings (3D relationship)
-- Assumes building height is available
SELECT b1.name AS building1, b2.name AS building2
FROM buildings b1, buildings b2
WHERE b1.id < b2.id AND ST_Overlaps(b1.geom, b2.geom);
 
-- Example 9: Find park boundaries intersected by the road network
SELECT p.name, ST_Length(ST_Intersection(p.geom, r.geom)) AS intersection_length
FROM parks p, roads r
WHERE ST_Intersects(p.geom, r.geom);
 
-- Example 10: Use a buffer to find buildings near roads (intersecting buffer)
SELECT b.name, ST_Distance(b.geom, r.geom) AS distance
FROM buildings b, roads r
WHERE ST_Intersects(ST_Buffer(r.geom::geography, 50)::geometry, b.geom)
ORDER BY distance;

-- Create buildings table
CREATE TABLE buildings (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(POLYGON, 4326)
);
 
-- Create points of interest table
CREATE TABLE points_of_interest (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(POINT, 4326)
);
 
-- Insert building data (polygons)
INSERT INTO buildings (name, geom) VALUES
('Main Building', ST_GeomFromText('POLYGON((-74.006 40.7128, -74.005 40.7128, -74.005 40.7125, -74.006 40.7125, -74.006 40.7128))', 4326)),
('Science Center', ST_GeomFromText('POLYGON((-74.007 40.7135, -74.006 40.7135, -74.006 40.7130, -74.007 40.7130, -74.007 40.7135))', 4326));
 
-- Insert points of interest (points)
INSERT INTO points_of_interest (name, geom) VALUES
('Statue A', ST_GeomFromText('POINT(-74.0065 40.7126)', 4326)),
('Fountain', ST_GeomFromText('POINT(-74.004 40.7127)', 4326)),
('Monument', ST_GeomFromText('POINT(-74.0062 40.7132)', 4326));
 
 
-- Find all points of interest within a 100m buffer around each building, using geography type for distances in meters
SELECT poi.name AS interest_point,
       b.name AS building,
       ST_Distance(poi.geom::geography, b.geom::geography) AS distance_meters
FROM points_of_interest poi, buildings b
WHERE ST_DWithin(
    poi.geom::geography,
    b.geom::geography,
    100  -- 100 meter buffer distance
)
ORDER BY b.name, distance_meters;
 
-- Visualize buffers (generate buffer polygons), create a 100m buffer for each building for display
SELECT
    b.name,
    ST_AsText(ST_Buffer(b.geom::geography, 100)::geometry) AS buffer_geom
FROM buildings b;