PostGIS

-- Create a table for storing points of interest
CREATE TABLE points_of_interest (
    tenant_id uuid,
    id INTEGER,
    name VARCHAR(100),
    type VARCHAR(50),
    -- POINT geometry in WGS84 (latitude/longitude)
    location geometry(POINT, 4326),
    PRIMARY KEY (tenant_id, id)
);

-- Create a spatial index
CREATE INDEX points_of_interest_gist ON points_of_interest USING GIST(location);

-- Create a tenant first
INSERT INTO tenants (id, name) VALUES
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 'Tenant 1');

-- Insert some points of interest
INSERT INTO points_of_interest (tenant_id, id, name, type, location) VALUES
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 1, 'Central Park', 'park',
   ST_SetSRID(ST_MakePoint(-73.965355, 40.782865), 4326)),
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 2, 'Empire State', 'building',
   ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326)),
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 3, 'Statue of Liberty', 'monument',
   ST_SetSRID(ST_MakePoint(-74.044502, 40.689247), 4326));

SELECT name,
       ST_Distance(
         location::geography,
         ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326)::geography
       ) as distance_meters
FROM points_of_interest
WHERE ST_DWithin(
        location::geography,
        ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326)::geography,
        5000  -- 5km in meters
      )
ORDER BY distance_meters;

-- Distance calculations default to meters, you can multiple by 0.000621371 to get miles
SELECT ST_Distance(
    (SELECT location::geography FROM points_of_interest WHERE name = 'Central Park'),
    (SELECT location::geography FROM points_of_interest WHERE name = 'Empire State')
) as distance_meters;

-- Create a table for areas
CREATE TABLE areas (
    tenant_id uuid,
    id INTEGER,
    name VARCHAR(100),
    boundary geometry(POLYGON, 4326),
    PRIMARY KEY (tenant_id, id)
);

-- Insert multiple polygons (simplified boundaries)
INSERT INTO areas (tenant_id, id, name, boundary) VALUES
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 1, 'Central Park',
   ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(
     -73.968285 40.785091,
     -73.961675 40.785091,
     -73.961675 40.780467,
     -73.968285 40.780467,
     -73.968285 40.785091
   )')), 4326)),
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 2, 'Area1',
   ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(
     -73.965 40.783,
     -73.960 40.783,
     -73.960 40.779,
     -73.965 40.779,
     -73.965 40.783
   )')), 4326)),
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 3, 'Area2',
   ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(
     -73.963 40.784,
     -73.958 40.784,
     -73.958 40.780,
     -73.963 40.780,
     -73.963 40.784
   )')), 4326));

-- Find points within the area
SELECT p.name
FROM points_of_interest p
JOIN areas a ON ST_Contains(a.boundary, p.location)
WHERE a.name = 'Central Park';

-- Convert from WGS84 (EPSG:4326) to Web Mercator (EPSG:3857)
SELECT ST_Transform(
    ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326),
    3857
);

-- Calculate distance in meters
SELECT ST_Distance(
    ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326)::geography,
    ST_SetSRID(ST_MakePoint(-73.968285, 40.785091), 4326)::geography
);

-- Check if point is within polygon
SELECT ST_Contains(
    (SELECT boundary FROM areas WHERE name = 'Central Park'),
    (SELECT location FROM points_of_interest WHERE name = 'Empire State')
);

-- Find intersection of two polygons
SELECT ST_Intersection(a.boundary, b.boundary)
FROM areas a, areas b
WHERE a.name = 'Area1' AND b.name = 'Area2';

-- Create a point
SELECT ST_SetSRID(ST_MakePoint(-73.985428, 40.748817), 4326);

-- Create a line
SELECT ST_MakeLine(
    ST_MakePoint(-73.985428, 40.748817),
    ST_MakePoint(-73.968285, 40.785091)
);

-- Create a polygon
SELECT ST_MakePolygon(ST_GeomFromText('LINESTRING(
    0 0, 1 0, 1 1, 0 1, 0 0
)'));