GeoSight MCP Server

-- GeoSight PostgreSQL + PostGIS Initialization Script -- This script runs when the database container is first created -- Enable PostGIS extension CREATE EXTENSION IF NOT EXISTS postgis; CREATE EXTENSION IF NOT EXISTS postgis_topology; CREATE EXTENSION IF NOT EXISTS fuzzystrmatch; CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder; -- Create schema CREATE SCHEMA IF NOT EXISTS geosight; -- Analysis Jobs Table CREATE TABLE IF NOT EXISTS geosight.analysis_jobs ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), job_type VARCHAR(50) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', parameters JSONB NOT NULL, result JSONB, error_message TEXT, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), started_at TIMESTAMP WITH TIME ZONE, completed_at TIMESTAMP WITH TIME ZONE, user_id VARCHAR(255) ); -- Create index on status for job queue queries CREATE INDEX IF NOT EXISTS idx_jobs_status ON geosight.analysis_jobs(status); CREATE INDEX IF NOT EXISTS idx_jobs_created ON geosight.analysis_jobs(created_at DESC); -- Cached Results Table CREATE TABLE IF NOT EXISTS geosight.cached_results ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), cache_key VARCHAR(255) UNIQUE NOT NULL, result_type VARCHAR(50) NOT NULL, result_data JSONB NOT NULL, location GEOGRAPHY(POINT, 4326), bbox GEOGRAPHY(POLYGON, 4326), date_range DATERANGE, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), expires_at TIMESTAMP WITH TIME ZONE, hit_count INTEGER DEFAULT 0 ); -- Create spatial index CREATE INDEX IF NOT EXISTS idx_cached_location ON geosight.cached_results USING GIST(location); CREATE INDEX IF NOT EXISTS idx_cached_bbox ON geosight.cached_results USING GIST(bbox); CREATE INDEX IF NOT EXISTS idx_cached_key ON geosight.cached_results(cache_key); -- Analysis History Table CREATE TABLE IF NOT EXISTS geosight.analysis_history ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), analysis_type VARCHAR(50) NOT NULL, location_name VARCHAR(255), latitude DOUBLE PRECISION NOT NULL, longitude DOUBLE PRECISION NOT NULL, location GEOGRAPHY(POINT, 4326), radius_km DOUBLE PRECISION, start_date DATE, end_date DATE, parameters JSONB, result_summary JSONB, image_path VARCHAR(500), created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), user_id VARCHAR(255) ); -- Create indexes CREATE INDEX IF NOT EXISTS idx_history_location ON geosight.analysis_history USING GIST(location); CREATE INDEX IF NOT EXISTS idx_history_type ON geosight.analysis_history(analysis_type); CREATE INDEX IF NOT EXISTS idx_history_created ON geosight.analysis_history(created_at DESC); -- Reports Table CREATE TABLE IF NOT EXISTS geosight.reports ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), title VARCHAR(255) NOT NULL, location_name VARCHAR(255), location GEOGRAPHY(POINT, 4326), date_range DATERANGE, analyses TEXT[], report_content TEXT, report_format VARCHAR(20), file_path VARCHAR(500), created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), user_id VARCHAR(255) ); CREATE INDEX IF NOT EXISTS idx_reports_created ON geosight.reports(created_at DESC); -- Function to update location geography from lat/lon CREATE OR REPLACE FUNCTION geosight.update_location_geography() RETURNS TRIGGER AS $$ BEGIN NEW.location := ST_SetSRID(ST_MakePoint(NEW.longitude, NEW.latitude), 4326)::geography; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Trigger for analysis_history DROP TRIGGER IF EXISTS trg_update_history_location ON geosight.analysis_history; CREATE TRIGGER trg_update_history_location BEFORE INSERT OR UPDATE ON geosight.analysis_history FOR EACH ROW EXECUTE FUNCTION geosight.update_location_geography(); -- Grant permissions GRANT ALL PRIVILEGES ON SCHEMA geosight TO postgres; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA geosight TO postgres; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA geosight TO postgres; -- Insert sample data for testing INSERT INTO geosight.analysis_history ( analysis_type, location_name, latitude, longitude, radius_km, start_date, end_date, result_summary ) VALUES ( 'ndvi', 'Amazon Rainforest, Brazil', -3.4653, -62.2159, 50, '2024-01-01', '2024-06-01', '{"mean_ndvi": 0.75, "healthy_vegetation_pct": 82.5}' ), ( 'change_detection', 'Dubai, UAE', 25.2048, 55.2708, 20, '2020-01-01', '2024-01-01', '{"change_percentage": 15.3, "main_change": "urban_expansion"}' ) ON CONFLICT DO NOTHING; -- Cleanup function for expired cache CREATE OR REPLACE FUNCTION geosight.cleanup_expired_cache() RETURNS INTEGER AS $$ DECLARE deleted_count INTEGER; BEGIN DELETE FROM geosight.cached_results WHERE expires_at < NOW(); GET DIAGNOSTICS deleted_count = ROW_COUNT; RETURN deleted_count; END; $$ LANGUAGE plpgsql; -- Success message DO $$ BEGIN RAISE NOTICE 'GeoSight database initialized successfully!'; END $$;