server.ts•57.2 kB
import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import {
CallToolRequestSchema,
ListToolsRequestSchema,
ListResourcesRequestSchema,
ReadResourceRequestSchema,
} from "@modelcontextprotocol/sdk/types.js";
import { z } from "zod";
import { Client } from "pg";
import { config } from "dotenv";
// Environment variables yükle
config();
// Akıllı Sorgulama Schema'ları
const AnalyzeDatabaseSchema = z.object({
force_refresh: z.boolean().optional().default(false),
});
const SmartQuerySchema = z.object({
question: z.string(),
language: z.enum(["tr", "en"]).optional().default("tr"),
});
const TableInfoSchema = z.object({
table_name: z.string(),
});
// PostGIS Geometri Schema'ları
const CreatePointSchema = z.object({
longitude: z.number(),
latitude: z.number(),
srid: z.number().optional().default(4326),
});
const CreateLineStringSchema = z.object({
coordinates: z.array(z.array(z.number().min(2).max(3))),
srid: z.number().optional().default(4326),
});
const CreatePolygonSchema = z.object({
exterior_ring: z.array(z.array(z.number().min(2).max(3))),
interior_rings: z.array(z.array(z.array(z.number()))).optional(),
srid: z.number().optional().default(4326),
});
const CalculateDistanceSchema = z.object({
point1_lon: z.number(),
point1_lat: z.number(),
point2_lon: z.number(),
point2_lat: z.number(),
use_geography: z.boolean().optional().default(true),
});
const FindNearbySchema = z.object({
latitude: z.number(),
longitude: z.number(),
distance_km: z.number(),
table_name: z.string(),
limit: z.number().optional().default(10),
});
const CreateBufferSchema = z.object({
geometry_wkt: z.string(),
distance_meters: z.number(),
});
const TransformCoordinatesSchema = z.object({
geometry_wkt: z.string(),
source_srid: z.number(),
target_srid: z.number(),
});
const GeometryInfoSchema = z.object({
geometry_wkt: z.string(),
});
const GeometryIntersectionSchema = z.object({
geometry1_wkt: z.string(),
geometry2_wkt: z.string(),
});
const GeometryUnionSchema = z.object({
geometries_wkt: z.array(z.string()),
});
const SimplifyGeometrySchema = z.object({
geometry_wkt: z.string(),
tolerance: z.number(),
preserve_collapsed: z.boolean().optional().default(false),
});
const ValidateGeometrySchema = z.object({
geometry_wkt: z.string(),
});
const GeometryConvexHullSchema = z.object({
geometry_wkt: z.string(),
});
const GeometryCentroidSchema = z.object({
geometry_wkt: z.string(),
});
const SpatialJoinSchema = z.object({
table1: z.string(),
table2: z.string(),
join_type: z.enum(["intersects", "within", "contains", "touches"]),
geometry_column1: z.string().optional().default("geom"),
geometry_column2: z.string().optional().default("geom"),
limit: z.number().optional().default(100),
});
const SpatialIndexSchema = z.object({
table_name: z.string(),
geometry_column: z.string().optional().default("geom"),
});
const RasterInfoSchema = z.object({
table_name: z.string(),
raster_column: z.string().optional().default("rast"),
});
const ExtractRasterValueSchema = z.object({
table_name: z.string(),
longitude: z.number(),
latitude: z.number(),
raster_column: z.string().optional().default("rast"),
});
// Database Schema Types
interface ColumnInfo {
column_name: string;
data_type: string;
is_nullable: boolean;
column_default: string | null;
is_primary_key: boolean;
is_foreign_key: boolean;
foreign_table?: string;
foreign_column?: string;
description?: string;
}
interface TableInfo {
table_name: string;
table_schema: string;
table_type: string;
columns: ColumnInfo[];
geometry_columns: string[];
row_count: number;
table_comment?: string;
indexes: string[];
sample_data?: any[];
}
interface DatabaseSchema {
tables: { [key: string]: TableInfo };
spatial_tables: string[];
last_analyzed: Date;
connection_info: {
database: string;
host: string;
};
}
// Global schema cache
let databaseSchema: DatabaseSchema | null = null;
// PostgreSQL client
const createDbClient = () => {
return new Client({
host: process.env.DB_HOST || "localhost",
port: parseInt(process.env.DB_PORT || "5432"),
database: process.env.DB_NAME,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
});
};
// Database Schema Analyzer
async function analyzeDatabaseSchema(client: Client): Promise<DatabaseSchema> {
const schema: DatabaseSchema = {
tables: {},
spatial_tables: [],
last_analyzed: new Date(),
connection_info: {
database: process.env.DB_NAME || "unknown",
host: process.env.DB_HOST || "localhost",
},
};
// 1. Get all tables with basic info
const tablesResult = await client.query(`
SELECT
t.table_name,
t.table_schema,
t.table_type,
obj_description(c.oid) as table_comment
FROM information_schema.tables t
LEFT JOIN pg_class c ON c.relname = t.table_name
WHERE t.table_schema NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
ORDER BY t.table_name;
`);
// 2. Get all columns with detailed info
const columnsResult = await client.query(`
SELECT
c.table_name,
c.column_name,
c.data_type,
c.is_nullable::boolean,
c.column_default,
CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END as is_primary_key,
CASE WHEN fk.column_name IS NOT NULL THEN true ELSE false END as is_foreign_key,
fk.foreign_table_name,
fk.foreign_column_name,
col_description(pgc.oid, c.ordinal_position) as description
FROM information_schema.columns c
LEFT JOIN (
SELECT ku.table_name, ku.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage ku ON tc.constraint_name = ku.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY'
) pk ON c.table_name = pk.table_name AND c.column_name = pk.column_name
LEFT JOIN (
SELECT
ku.table_name,
ku.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage ku ON tc.constraint_name = ku.constraint_name
JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
) fk ON c.table_name = fk.table_name AND c.column_name = fk.column_name
LEFT JOIN pg_class pgc ON pgc.relname = c.table_name
WHERE c.table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY c.table_name, c.ordinal_position;
`);
// 3. Get geometry columns
let geometryColumns: any[] = [];
try {
const geometryResult = await client.query(`
SELECT
f_table_name as table_name,
f_geometry_column as column_name,
type as geometry_type,
srid
FROM geometry_columns;
`);
geometryColumns = geometryResult.rows;
} catch (error) {
// PostGIS might not be installed
}
// 4. Get indexes
const indexesResult = await client.query(`
SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('information_schema', 'pg_catalog');
`);
// 5. Build table structures
for (const table of tablesResult.rows) {
const tableName = table.table_name;
// Get columns for this table
const tableColumns = columnsResult.rows
.filter((col) => col.table_name === tableName)
.map((col) => ({
column_name: col.column_name,
data_type: col.data_type,
is_nullable: col.is_nullable,
column_default: col.column_default,
is_primary_key: col.is_primary_key,
is_foreign_key: col.is_foreign_key,
foreign_table: col.foreign_table_name,
foreign_column: col.foreign_column_name,
description: col.description,
}));
// Get geometry columns for this table
const tableGeometryColumns = geometryColumns
.filter((gc) => gc.table_name === tableName)
.map((gc) => gc.column_name);
// Get indexes for this table
const tableIndexes = indexesResult.rows
.filter((idx) => idx.tablename === tableName)
.map((idx) => idx.indexname);
// Get row count
let rowCount = 0;
try {
const countResult = await client.query(
`SELECT COUNT(*) as count FROM ${tableName}`
);
rowCount = parseInt(countResult.rows[0].count);
} catch (error) {
// Table might be inaccessible
}
// Get sample data (first 3 rows)
let sampleData: any[] = [];
try {
const sampleResult = await client.query(
`SELECT * FROM ${tableName} LIMIT 3`
);
sampleData = sampleResult.rows;
} catch (error) {
// Table might be inaccessible
}
schema.tables[tableName] = {
table_name: tableName,
table_schema: table.table_schema,
table_type: table.table_type,
columns: tableColumns,
geometry_columns: tableGeometryColumns,
row_count: rowCount,
table_comment: table.table_comment,
indexes: tableIndexes,
sample_data: sampleData,
};
if (tableGeometryColumns.length > 0) {
schema.spatial_tables.push(tableName);
}
}
return schema;
}
// Smart Query Processor
function findRelevantTables(
question: string,
schema: DatabaseSchema
): {
table: string;
relevance_score: number;
reasoning: string;
}[] {
const questionLower = question.toLowerCase();
const results: {
table: string;
relevance_score: number;
reasoning: string;
}[] = [];
// Keywords to table mappings (Turkish)
const keywordMappings = {
istasyon: ["station", "istasyon", "durak", "terminal"],
şehir: ["city", "sehir", "il", "province"],
ilçe: ["district", "ilce", "county"],
mahalle: ["neighborhood", "mahalle", "quarter"],
yol: ["road", "street", "yol", "cadde", "sokak"],
bina: ["building", "bina", "yapı"],
park: ["park", "green", "yesil"],
hastane: ["hospital", "hastane", "saglik"],
okul: ["school", "okul", "egitim"],
müze: ["museum", "muze", "kultur"],
market: ["market", "magaza", "alisveris"],
restoran: ["restaurant", "restoran", "yemek"],
otobüs: ["bus", "otobus", "halk_otobus"],
metro: ["metro", "subway", "rail"],
tramvay: ["tram", "tramvay"],
köprü: ["bridge", "kopru"],
havaalanı: ["airport", "havaalani", "havalimani"],
};
for (const [tableName, tableInfo] of Object.entries(schema.tables)) {
let score = 0;
let reasoning: string[] = [];
// 1. Direct table name match
if (questionLower.includes(tableName.toLowerCase())) {
score += 50;
reasoning.push(`Tablo adı '${tableName}' soruda geçiyor`);
}
// 2. Keyword matching
for (const [concept, keywords] of Object.entries(keywordMappings)) {
if (keywords.some((keyword) => questionLower.includes(keyword))) {
if (
keywords.some((keyword) => tableName.toLowerCase().includes(keyword))
) {
score += 30;
reasoning.push(
`'${concept}' kavramı hem soruda hem tablo adında var`
);
}
}
}
// 3. Column name matching
for (const column of tableInfo.columns) {
const columnLower = column.column_name.toLowerCase();
if (questionLower.includes(columnLower)) {
score += 20;
reasoning.push(`Kolon '${column.column_name}' soruda geçiyor`);
}
if (
questionLower.includes("nerede") ||
questionLower.includes("hangi") ||
questionLower.includes("kaç") ||
questionLower.includes("sayı")
) {
if (
columnLower.includes("name") ||
columnLower.includes("ad") ||
columnLower.includes("isim") ||
columnLower.includes("count")
) {
score += 15;
reasoning.push(
`Mekansal sorgu için uygun kolon: ${column.column_name}`
);
}
}
}
// 4. Spatial table bonus
if (schema.spatial_tables.includes(tableName)) {
score += 10;
reasoning.push("Spatial tablo");
}
// 5. Row count consideration
if (tableInfo.row_count > 0) {
score += 5;
reasoning.push(`${tableInfo.row_count} kayıt içeriyor`);
}
// 6. Location-based questions for spatial tables
if (
(questionLower.includes("istanbul") ||
questionLower.includes("ankara") ||
questionLower.includes("izmir")) &&
schema.spatial_tables.includes(tableName)
) {
score += 25;
reasoning.push("Şehir ismi + spatial tablo");
}
if (score > 0) {
results.push({
table: tableName,
relevance_score: score,
reasoning: reasoning.join(", "),
});
}
}
return results.sort((a, b) => b.relevance_score - a.relevance_score);
}
// Generate SQL query based on question and selected table
function generateSmartQuery(
question: string,
tableName: string,
tableInfo: TableInfo
): string {
const questionLower = question.toLowerCase();
let query = `SELECT `;
let whereClause = "";
let groupBy = "";
let orderBy = "";
// Determine what to select
if (
questionLower.includes("kaç") ||
questionLower.includes("sayı") ||
questionLower.includes("count")
) {
query += `COUNT(*) as count`;
// Add grouping if needed
const nameColumns = tableInfo.columns.filter(
(col) =>
col.column_name.toLowerCase().includes("name") ||
col.column_name.toLowerCase().includes("ad") ||
col.column_name.toLowerCase().includes("isim") ||
col.column_name.toLowerCase().includes("type") ||
col.column_name.toLowerCase().includes("tip")
);
if (nameColumns.length > 0) {
const groupColumn = nameColumns[0].column_name;
query = `SELECT ${groupColumn}, COUNT(*) as count`;
groupBy = ` GROUP BY ${groupColumn}`;
orderBy = ` ORDER BY count DESC`;
}
} else {
// Select first few important columns
const importantColumns = tableInfo.columns
.filter(
(col) =>
col.column_name.toLowerCase().includes("name") ||
col.column_name.toLowerCase().includes("ad") ||
col.column_name.toLowerCase().includes("id") ||
col.is_primary_key
)
.slice(0, 5)
.map((col) => col.column_name);
if (importantColumns.length === 0) {
query += "*";
} else {
query += importantColumns.join(", ");
}
// Add geometry if available
if (tableInfo.geometry_columns.length > 0) {
query += `, ST_AsGeoJSON(${tableInfo.geometry_columns[0]}) as geometry`;
}
orderBy = " LIMIT 10";
}
query += ` FROM ${tableName}`;
// Add location-based WHERE clause
if (questionLower.includes("istanbul")) {
const locationColumns = tableInfo.columns.filter(
(col) =>
col.column_name.toLowerCase().includes("city") ||
col.column_name.toLowerCase().includes("sehir") ||
col.column_name.toLowerCase().includes("il")
);
if (locationColumns.length > 0) {
whereClause = ` WHERE LOWER(${locationColumns[0].column_name}) LIKE '%istanbul%'`;
}
}
return query + whereClause + groupBy + orderBy;
}
// MCP Server oluştur
const server = new Server(
{
name: "smart-postgis-mcp-server",
version: "1.0.0",
},
{
capabilities: {
tools: {},
resources: {},
},
}
);
// KAPSAMLI tools listesi - Akıllı Sorgulama + Tüm PostGIS Fonksiyonları
server.setRequestHandler(ListToolsRequestSchema, async () => {
return {
tools: [
// AKİLLI SORGULAMA ARAÇLARI
{
name: "test-connection",
description: "PostGIS veritabanı bağlantısını test et",
inputSchema: { type: "object", properties: {} },
},
{
name: "analyze-database",
description: "Veritabanı şemasını analiz et ve tablo bilgilerini topla",
inputSchema: {
type: "object",
properties: {
force_refresh: {
type: "boolean",
description: "Önbelleği yenileyip yeniden analiz et",
},
},
},
},
{
name: "smart-query",
description:
"Doğal dilde sorulan soruyu anla ve uygun veritabanı sorgusuna çevir",
inputSchema: {
type: "object",
properties: {
question: {
type: "string",
description:
"Doğal dildeki soru (örn: 'İstanbul'da kaç tane istasyon var?')",
},
language: {
type: "string",
enum: ["tr", "en"],
description: "Soru dili",
},
},
required: ["question"],
},
},
{
name: "get-table-info",
description: "Belirli bir tablo hakkında detaylı bilgi al",
inputSchema: {
type: "object",
properties: {
table_name: { type: "string", description: "Tablo adı" },
},
required: ["table_name"],
},
},
// GEOMETRİ OLUŞTURMA ARAÇLARI
{
name: "create-point",
description: "Koordinatlardan nokta geometrisi oluştur",
inputSchema: {
type: "object",
properties: {
longitude: { type: "number", description: "Boylam koordinatı" },
latitude: { type: "number", description: "Enlem koordinatı" },
srid: {
type: "number",
description: "Spatial Reference System ID (varsayılan: 4326)",
},
},
required: ["longitude", "latitude"],
},
},
{
name: "create-linestring",
description: "Koordinat dizisinden çizgi geometrisi oluştur",
inputSchema: {
type: "object",
properties: {
coordinates: {
type: "array",
description: "Koordinat dizisi [[lon,lat], [lon,lat], ...]",
items: { type: "array", items: { type: "number" } },
},
srid: { type: "number", description: "SRID (varsayılan: 4326)" },
},
required: ["coordinates"],
},
},
{
name: "create-polygon",
description: "Koordinat dizisinden poligon geometrisi oluştur",
inputSchema: {
type: "object",
properties: {
exterior_ring: {
type: "array",
description: "Dış halka koordinatları",
items: { type: "array", items: { type: "number" } },
},
interior_rings: {
type: "array",
description: "İç halka koordinatları (opsiyonel)",
items: {
type: "array",
items: { type: "array", items: { type: "number" } },
},
},
srid: { type: "number", description: "SRID (varsayılan: 4326)" },
},
required: ["exterior_ring"],
},
},
// MESAFE VE ÖLÇÜM ARAÇLARI
{
name: "calculate-distance",
description: "İki nokta arasındaki mesafeyi hesapla",
inputSchema: {
type: "object",
properties: {
point1_lon: { type: "number", description: "Birinci nokta boylam" },
point1_lat: { type: "number", description: "Birinci nokta enlem" },
point2_lon: { type: "number", description: "İkinci nokta boylam" },
point2_lat: { type: "number", description: "İkinci nokta enlem" },
use_geography: {
type: "boolean",
description: "Coğrafi hesaplama kullan (varsayılan: true)",
},
},
required: ["point1_lon", "point1_lat", "point2_lon", "point2_lat"],
},
},
{
name: "geometry-info",
description:
"Geometri hakkında detaylı bilgi al (alan, uzunluk, boyut vs.)",
inputSchema: {
type: "object",
properties: {
geometry_wkt: {
type: "string",
description: "WKT formatında geometri",
},
},
required: ["geometry_wkt"],
},
},
// BUFFER VE DÖNÜŞÜM ARAÇLARI
{
name: "create-buffer",
description: "Geometri etrafında buffer oluştur",
inputSchema: {
type: "object",
properties: {
geometry_wkt: {
type: "string",
description: "WKT formatında geometri",
},
distance_meters: {
type: "number",
description: "Buffer mesafesi (metre)",
},
},
required: ["geometry_wkt", "distance_meters"],
},
},
{
name: "transform-coordinates",
description: "Koordinat sistemini dönüştür",
inputSchema: {
type: "object",
properties: {
geometry_wkt: {
type: "string",
description: "WKT formatında geometri",
},
source_srid: {
type: "number",
description: "Kaynak koordinat sistemi SRID",
},
target_srid: {
type: "number",
description: "Hedef koordinat sistemi SRID",
},
},
required: ["geometry_wkt", "source_srid", "target_srid"],
},
},
{
name: "simplify-geometry",
description: "Geometriyi basitleştir (Douglas-Peucker algoritması)",
inputSchema: {
type: "object",
properties: {
geometry_wkt: {
type: "string",
description: "WKT formatında geometri",
},
tolerance: {
type: "number",
description: "Basitleştirme toleransı",
},
preserve_collapsed: {
type: "boolean",
description: "Çöken geometrileri koru",
},
},
required: ["geometry_wkt", "tolerance"],
},
},
// GEOMETRİ ANALİZ ARAÇLARI
{
name: "geometry-intersection",
description: "İki geometrinin kesişimini bul",
inputSchema: {
type: "object",
properties: {
geometry1_wkt: {
type: "string",
description: "Birinci geometri (WKT)",
},
geometry2_wkt: {
type: "string",
description: "İkinci geometri (WKT)",
},
},
required: ["geometry1_wkt", "geometry2_wkt"],
},
},
{
name: "geometry-union",
description: "Birden fazla geometriyi birleştir",
inputSchema: {
type: "object",
properties: {
geometries_wkt: {
type: "array",
description: "WKT formatında geometri dizisi",
items: { type: "string" },
},
},
required: ["geometries_wkt"],
},
},
{
name: "geometry-centroid",
description: "Geometrinin merkez noktasını bul",
inputSchema: {
type: "object",
properties: {
geometry_wkt: {
type: "string",
description: "WKT formatında geometri",
},
},
required: ["geometry_wkt"],
},
},
{
name: "geometry-convex-hull",
description: "Geometrinin konveks zarfını hesapla",
inputSchema: {
type: "object",
properties: {
geometry_wkt: {
type: "string",
description: "WKT formatında geometri",
},
},
required: ["geometry_wkt"],
},
},
// VALİDASYON ARAÇLARI
{
name: "validate-geometry",
description: "Geometrinin geçerliliğini kontrol et",
inputSchema: {
type: "object",
properties: {
geometry_wkt: {
type: "string",
description: "WKT formatında geometri",
},
},
required: ["geometry_wkt"],
},
},
// MEKANSAL SORGU ARAÇLARI
{
name: "find-nearby",
description: "Belirli bir noktanın çevresindeki özellikleri bul",
inputSchema: {
type: "object",
properties: {
latitude: { type: "number", description: "Merkez nokta enlem" },
longitude: { type: "number", description: "Merkez nokta boylam" },
distance_km: { type: "number", description: "Arama mesafesi (km)" },
table_name: { type: "string", description: "Aranacak tablo adı" },
limit: {
type: "number",
description: "Maksimum sonuç sayısı (varsayılan: 10)",
},
},
required: ["latitude", "longitude", "distance_km", "table_name"],
},
},
{
name: "spatial-join",
description: "İki tablo arasında mekansal join işlemi yap",
inputSchema: {
type: "object",
properties: {
table1: { type: "string", description: "Birinci tablo adı" },
table2: { type: "string", description: "İkinci tablo adı" },
join_type: {
type: "string",
enum: ["intersects", "within", "contains", "touches"],
description: "Join tipi",
},
geometry_column1: {
type: "string",
description: "Tablo1 geometri kolonu (varsayılan: geom)",
},
geometry_column2: {
type: "string",
description: "Tablo2 geometri kolonu (varsayılan: geom)",
},
limit: {
type: "number",
description: "Sonuç limiti (varsayılan: 100)",
},
},
required: ["table1", "table2", "join_type"],
},
},
// İNDEKS YÖNETİM ARAÇLARI
{
name: "create-spatial-index",
description: "Mekansal indeks oluştur",
inputSchema: {
type: "object",
properties: {
table_name: { type: "string", description: "Tablo adı" },
geometry_column: {
type: "string",
description: "Geometri kolonu (varsayılan: geom)",
},
},
required: ["table_name"],
},
},
// RASTER İŞLEM ARAÇLARI
{
name: "raster-info",
description: "Raster verisinin bilgilerini al",
inputSchema: {
type: "object",
properties: {
table_name: { type: "string", description: "Raster tablosu adı" },
raster_column: {
type: "string",
description: "Raster kolonu (varsayılan: rast)",
},
},
required: ["table_name"],
},
},
{
name: "extract-raster-value",
description: "Belirli koordinattaki raster değerini çıkar",
inputSchema: {
type: "object",
properties: {
table_name: { type: "string", description: "Raster tablosu" },
longitude: { type: "number", description: "Boylam" },
latitude: { type: "number", description: "Enlem" },
raster_column: {
type: "string",
description: "Raster kolonu (varsayılan: rast)",
},
},
required: ["table_name", "longitude", "latitude"],
},
},
],
};
});
// Enhanced resources
server.setRequestHandler(ListResourcesRequestSchema, async () => {
return {
resources: [
{
uri: "schema://database",
name: "Database Schema",
description: "Veritabanı şeması ve tablo bilgileri",
mimeType: "application/json",
},
{
uri: "schema://tables",
name: "Smart Table Analysis",
description: "Akıllı tablo analizi ve öneriler",
mimeType: "application/json",
},
{
uri: "spatial://tables",
name: "Spatial Tables",
description: "Mekansal tablolar",
mimeType: "application/json",
},
{
uri: "spatial://indexes",
name: "Spatial Indexes",
description: "Mekansal indeksler",
mimeType: "application/json",
},
],
};
});
// Enhanced resource reading
server.setRequestHandler(ReadResourceRequestSchema, async (request) => {
const { uri } = request.params;
const client = createDbClient();
try {
await client.connect();
if (uri === "schema://database") {
if (!databaseSchema) {
databaseSchema = await analyzeDatabaseSchema(client);
}
return {
contents: [
{
uri,
mimeType: "application/json",
text: JSON.stringify(databaseSchema, null, 2),
},
],
};
}
if (uri === "schema://tables") {
if (!databaseSchema) {
databaseSchema = await analyzeDatabaseSchema(client);
}
const tablesSummary = Object.entries(databaseSchema.tables).map(
([name, info]) => ({
table_name: name,
row_count: info.row_count,
column_count: info.columns.length,
has_geometry: info.geometry_columns.length > 0,
primary_columns: info.columns
.filter((c) => c.is_primary_key)
.map((c) => c.column_name),
sample_columns: info.columns.slice(0, 5).map((c) => c.column_name),
})
);
return {
contents: [
{
uri,
mimeType: "application/json",
text: JSON.stringify(
{
total_tables: Object.keys(databaseSchema.tables).length,
spatial_tables: databaseSchema.spatial_tables.length,
last_analyzed: databaseSchema.last_analyzed,
tables: tablesSummary,
},
null,
2
),
},
],
};
}
if (uri === "spatial://tables") {
const result = await client.query(`
SELECT f_table_name, f_geometry_column, type, srid, coord_dimension
FROM geometry_columns ORDER BY f_table_name;
`);
return {
contents: [
{
uri,
mimeType: "application/json",
text: JSON.stringify(result.rows, null, 2),
},
],
};
}
if (uri === "spatial://indexes") {
const result = await client.query(`
SELECT
schemaname,
tablename,
indexname,
indexdef
FROM pg_indexes
WHERE indexdef LIKE '%gist%' OR indexdef LIKE '%spatial%'
ORDER BY schemaname, tablename;
`);
return {
contents: [
{
uri,
mimeType: "application/json",
text: JSON.stringify(result.rows, null, 2),
},
],
};
}
throw new Error(`Unknown resource: ${uri}`);
} catch (error) {
throw new Error(
`Resource read error: ${
error instanceof Error ? error.message : "Unknown error"
}`
);
} finally {
await client.end();
}
});
// KAPSAMLI tool implementations - Akıllı Sorgulama + Tüm PostGIS Fonksiyonları
server.setRequestHandler(CallToolRequestSchema, async (request) => {
const { name, arguments: args } = request.params;
const client = createDbClient();
try {
await client.connect();
switch (name) {
// AKİLLI SORGULAMA ARAÇLARI
case "test-connection": {
const result = await client.query(
"SELECT PostGIS_Version() as version"
);
return {
content: [
{
type: "text",
text: `PostGIS bağlantısı başarılı! Versiyon: ${result.rows[0].version}`,
},
],
};
}
case "analyze-database": {
const { force_refresh } = AnalyzeDatabaseSchema.parse(args);
if (!databaseSchema || force_refresh) {
databaseSchema = await analyzeDatabaseSchema(client);
}
const summary = {
database: databaseSchema.connection_info.database,
host: databaseSchema.connection_info.host,
total_tables: Object.keys(databaseSchema.tables).length,
spatial_tables: databaseSchema.spatial_tables.length,
last_analyzed: databaseSchema.last_analyzed,
table_summary: Object.entries(databaseSchema.tables).map(
([name, info]) => ({
name,
rows: info.row_count,
columns: info.columns.length,
spatial: info.geometry_columns.length > 0,
description: info.table_comment || "Açıklama yok",
})
),
};
return {
content: [
{
type: "text",
text: JSON.stringify(summary, null, 2),
},
],
};
}
case "smart-query": {
const { question, language } = SmartQuerySchema.parse(args);
if (!databaseSchema) {
databaseSchema = await analyzeDatabaseSchema(client);
}
const relevantTables = findRelevantTables(question, databaseSchema);
if (relevantTables.length === 0) {
return {
content: [
{
type: "text",
text: JSON.stringify(
{
question,
status: "no_relevant_tables",
message: "Sorunuzla ilgili uygun tablo bulunamadı",
available_tables: Object.keys(databaseSchema.tables),
},
null,
2
),
},
],
};
}
const bestTable = relevantTables[0];
const tableInfo = databaseSchema.tables[bestTable.table];
const sqlQuery = generateSmartQuery(
question,
bestTable.table,
tableInfo
);
try {
const result = await client.query(sqlQuery);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
question,
selected_table: bestTable.table,
reasoning: bestTable.reasoning,
relevance_score: bestTable.relevance_score,
generated_sql: sqlQuery,
result_count: result.rows.length,
results: result.rows,
all_candidates: relevantTables.slice(0, 3),
},
null,
2
),
},
],
};
} catch (queryError) {
return {
content: [
{
type: "text",
text: JSON.stringify(
{
question,
selected_table: bestTable.table,
generated_sql: sqlQuery,
status: "query_error",
error:
queryError instanceof Error
? queryError.message
: "Unknown error",
suggestion:
"SQL sorgusu çalıştırılamadı, tablo yapısını kontrol edin",
},
null,
2
),
},
],
};
}
}
case "get-table-info": {
const { table_name } = TableInfoSchema.parse(args);
if (!databaseSchema) {
databaseSchema = await analyzeDatabaseSchema(client);
}
const tableInfo = databaseSchema.tables[table_name];
if (!tableInfo) {
return {
content: [
{
type: "text",
text: JSON.stringify(
{
error: `Tablo '${table_name}' bulunamadı`,
available_tables: Object.keys(databaseSchema.tables),
},
null,
2
),
},
],
};
}
return {
content: [
{
type: "text",
text: JSON.stringify(tableInfo, null, 2),
},
],
};
}
// GEOMETRİ OLUŞTURMA ARAÇLARI
case "create-point": {
const { longitude, latitude, srid } = CreatePointSchema.parse(args);
const result = await client.query(
`
SELECT
ST_AsText(ST_SetSRID(ST_MakePoint($1, $2), $3)) as wkt,
ST_AsGeoJSON(ST_SetSRID(ST_MakePoint($1, $2), $3)) as geojson
`,
[longitude, latitude, srid]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
wkt: result.rows[0].wkt,
geojson: JSON.parse(result.rows[0].geojson),
coordinates: [longitude, latitude],
srid: srid,
},
null,
2
),
},
],
};
}
case "create-linestring": {
const { coordinates, srid } = CreateLineStringSchema.parse(args);
const coordString = coordinates
.map((coord) => `${coord[0]} ${coord[1]}`)
.join(", ");
const result = await client.query(
`
SELECT
ST_AsText(ST_GeomFromText('LINESTRING(${coordString})', $1)) as wkt,
ST_AsGeoJSON(ST_GeomFromText('LINESTRING(${coordString})', $1)) as geojson,
ST_Length(ST_GeomFromText('LINESTRING(${coordString})', $1)::geography) as length_meters
`,
[srid]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
wkt: result.rows[0].wkt,
geojson: JSON.parse(result.rows[0].geojson),
coordinates: coordinates,
length_meters: parseFloat(result.rows[0].length_meters),
srid: srid,
},
null,
2
),
},
],
};
}
case "create-polygon": {
const {
exterior_ring,
interior_rings = [],
srid,
} = CreatePolygonSchema.parse(args);
const exteriorString = exterior_ring
.map((coord) => `${coord[0]} ${coord[1]}`)
.join(", ");
let polygonWkt = `POLYGON((${exteriorString}))`;
if (interior_rings.length > 0) {
const interiorStrings = interior_rings
.map(
(ring) =>
`(${ring.map((coord) => `${coord[0]} ${coord[1]}`).join(", ")})`
)
.join(", ");
polygonWkt = `POLYGON((${exteriorString}), ${interiorStrings})`;
}
const result = await client.query(
`
SELECT
ST_AsText(ST_GeomFromText($1, $2)) as wkt,
ST_AsGeoJSON(ST_GeomFromText($1, $2)) as geojson,
ST_Area(ST_GeomFromText($1, $2)::geography) as area_sqmeters,
ST_Perimeter(ST_GeomFromText($1, $2)::geography) as perimeter_meters
`,
[polygonWkt, srid]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
wkt: result.rows[0].wkt,
geojson: JSON.parse(result.rows[0].geojson),
area_sqmeters: parseFloat(result.rows[0].area_sqmeters),
perimeter_meters: parseFloat(result.rows[0].perimeter_meters),
srid: srid,
},
null,
2
),
},
],
};
}
// MESAFE VE ÖLÇÜM ARAÇLARI
case "calculate-distance": {
const {
point1_lon,
point1_lat,
point2_lon,
point2_lat,
use_geography,
} = CalculateDistanceSchema.parse(args);
const castType = use_geography ? "::geography" : "";
const result = await client.query(
`
SELECT ST_Distance(
ST_SetSRID(ST_MakePoint($1, $2), 4326)${castType},
ST_SetSRID(ST_MakePoint($3, $4), 4326)${castType}
) as distance
`,
[point1_lon, point1_lat, point2_lon, point2_lat]
);
const distance = parseFloat(result.rows[0].distance);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
distance_meters: distance,
distance_km: distance / 1000,
point1: [point1_lon, point1_lat],
point2: [point2_lon, point2_lat],
calculation_type: use_geography ? "geography" : "geometry",
},
null,
2
),
},
],
};
}
case "geometry-info": {
const { geometry_wkt } = GeometryInfoSchema.parse(args);
const result = await client.query(
`
SELECT
ST_GeometryType(ST_GeomFromText($1)) as geometry_type,
ST_SRID(ST_GeomFromText($1)) as srid,
ST_Dimension(ST_GeomFromText($1)) as dimension,
ST_NumGeometries(ST_GeomFromText($1)) as num_geometries,
ST_NumPoints(ST_GeomFromText($1)) as num_points,
ST_Area(ST_GeomFromText($1)::geography) as area_sqmeters,
ST_Length(ST_GeomFromText($1)::geography) as length_meters,
ST_Perimeter(ST_GeomFromText($1)::geography) as perimeter_meters,
ST_IsValid(ST_GeomFromText($1)) as is_valid,
ST_IsSimple(ST_GeomFromText($1)) as is_simple,
ST_IsEmpty(ST_GeomFromText($1)) as is_empty
`,
[geometry_wkt]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
input_wkt: geometry_wkt,
geometry_type: result.rows[0].geometry_type,
srid: result.rows[0].srid,
dimension: result.rows[0].dimension,
num_geometries: result.rows[0].num_geometries,
num_points: result.rows[0].num_points,
area_sqmeters: parseFloat(result.rows[0].area_sqmeters) || 0,
length_meters: parseFloat(result.rows[0].length_meters) || 0,
perimeter_meters:
parseFloat(result.rows[0].perimeter_meters) || 0,
is_valid: result.rows[0].is_valid,
is_simple: result.rows[0].is_simple,
is_empty: result.rows[0].is_empty,
},
null,
2
),
},
],
};
}
// BUFFER VE DÖNÜŞÜM ARAÇLARI
case "create-buffer": {
const { geometry_wkt, distance_meters } =
CreateBufferSchema.parse(args);
const result = await client.query(
`
SELECT
ST_AsText(ST_Buffer(ST_GeomFromText($1)::geography, $2)) as buffer_wkt,
ST_AsGeoJSON(ST_Buffer(ST_GeomFromText($1)::geography, $2)) as buffer_geojson,
ST_Area(ST_Buffer(ST_GeomFromText($1)::geography, $2)) as buffer_area
`,
[geometry_wkt, distance_meters]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
original_geometry: geometry_wkt,
buffer_distance_meters: distance_meters,
buffer_wkt: result.rows[0].buffer_wkt,
buffer_geojson: JSON.parse(result.rows[0].buffer_geojson),
buffer_area_sqmeters: parseFloat(result.rows[0].buffer_area),
},
null,
2
),
},
],
};
}
case "transform-coordinates": {
const { geometry_wkt, source_srid, target_srid } =
TransformCoordinatesSchema.parse(args);
const result = await client.query(
`
SELECT
ST_AsText(ST_Transform(ST_GeomFromText($1, $2), $3)) as transformed_wkt,
ST_AsGeoJSON(ST_Transform(ST_GeomFromText($1, $2), $3)) as transformed_geojson,
$2 as source_srid,
$3 as target_srid
`,
[geometry_wkt, source_srid, target_srid]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
original_geometry: geometry_wkt,
source_srid: source_srid,
target_srid: target_srid,
transformed_wkt: result.rows[0].transformed_wkt,
transformed_geojson: JSON.parse(
result.rows[0].transformed_geojson
),
},
null,
2
),
},
],
};
}
case "simplify-geometry": {
const { geometry_wkt, tolerance, preserve_collapsed } =
SimplifyGeometrySchema.parse(args);
const simplifyFunc = preserve_collapsed
? "ST_SimplifyPreserveTopology"
: "ST_Simplify";
const result = await client.query(
`
SELECT
ST_AsText(${simplifyFunc}(ST_GeomFromText($1), $2)) as simplified_wkt,
ST_AsGeoJSON(${simplifyFunc}(ST_GeomFromText($1), $2)) as simplified_geojson,
ST_NumPoints(ST_GeomFromText($1)) as original_points,
ST_NumPoints(${simplifyFunc}(ST_GeomFromText($1), $2)) as simplified_points,
ST_Area(ST_GeomFromText($1)::geography) as original_area,
ST_Area(${simplifyFunc}(ST_GeomFromText($1), $2)::geography) as simplified_area
`,
[geometry_wkt, tolerance]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
input_geometry: geometry_wkt,
tolerance: tolerance,
preserve_collapsed: preserve_collapsed,
simplified_wkt: result.rows[0].simplified_wkt,
simplified_geojson: JSON.parse(
result.rows[0].simplified_geojson
),
original_points: result.rows[0].original_points,
simplified_points: result.rows[0].simplified_points,
original_area_sqmeters:
parseFloat(result.rows[0].original_area) || 0,
simplified_area_sqmeters:
parseFloat(result.rows[0].simplified_area) || 0,
reduction_ratio:
result.rows[0].original_points > 0
? 1 -
result.rows[0].simplified_points /
result.rows[0].original_points
: 0,
},
null,
2
),
},
],
};
}
// MEKANSAL SORGU ARAÇLARI
case "find-nearby": {
const { latitude, longitude, distance_km, table_name, limit } =
FindNearbySchema.parse(args);
const sanitizedTableName = table_name.replace(/[^a-zA-Z0-9_]/g, "");
const result = await client.query(
`
SELECT
*,
ST_AsGeoJSON(geom) as geometry,
ST_Distance(
geom,
ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography
) / 1000 as distance_km
FROM ${sanitizedTableName}
WHERE ST_DWithin(
geom,
ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography,
$3
)
ORDER BY distance_km
LIMIT $4;
`,
[longitude, latitude, distance_km * 1000, limit]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
search_point: [longitude, latitude],
search_distance_km: distance_km,
table_name: sanitizedTableName,
results_count: result.rows.length,
results: result.rows,
},
null,
2
),
},
],
};
}
case "spatial-join": {
const {
table1,
table2,
join_type,
geometry_column1,
geometry_column2,
limit,
} = SpatialJoinSchema.parse(args);
const sanitizedTable1 = table1.replace(/[^a-zA-Z0-9_]/g, "");
const sanitizedTable2 = table2.replace(/[^a-zA-Z0-9_]/g, "");
const sanitizedGeomCol1 = geometry_column1 || "geom";
const sanitizedGeomCol2 = geometry_column2 || "geom";
const spatialPredicate = {
intersects: "ST_Intersects",
within: "ST_Within",
contains: "ST_Contains",
touches: "ST_Touches",
}[join_type];
const result = await client.query(
`
SELECT
t1.*,
t2.*,
ST_AsGeoJSON(t1.${sanitizedGeomCol1}) as t1_geometry,
ST_AsGeoJSON(t2.${sanitizedGeomCol2}) as t2_geometry
FROM ${sanitizedTable1} t1
JOIN ${sanitizedTable2} t2 ON ${spatialPredicate}(t1.${sanitizedGeomCol1}, t2.${sanitizedGeomCol2})
LIMIT $1;
`,
[limit]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
table1: sanitizedTable1,
table2: sanitizedTable2,
join_type: join_type,
spatial_predicate: spatialPredicate,
results_count: result.rows.length,
results: result.rows,
},
null,
2
),
},
],
};
}
// İNDEKS YÖNETİM ARAÇLARI
case "create-spatial-index": {
const { table_name, geometry_column } = SpatialIndexSchema.parse(args);
const sanitizedTableName = table_name.replace(/[^a-zA-Z0-9_]/g, "");
const sanitizedGeomColumn = geometry_column || "geom";
const indexName = `idx_${sanitizedTableName}_${sanitizedGeomColumn}_gist`;
try {
await client.query(`
CREATE INDEX IF NOT EXISTS ${indexName}
ON ${sanitizedTableName}
USING GIST (${sanitizedGeomColumn});
`);
const indexInfo = await client.query(
`
SELECT
indexname,
indexdef,
tablename
FROM pg_indexes
WHERE indexname = $1;
`,
[indexName]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
table_name: sanitizedTableName,
geometry_column: sanitizedGeomColumn,
index_name: indexName,
index_created: true,
index_info: indexInfo.rows[0] || null,
},
null,
2
),
},
],
};
} catch (error) {
return {
content: [
{
type: "text",
text: JSON.stringify(
{
table_name: sanitizedTableName,
geometry_column: sanitizedGeomColumn,
index_name: indexName,
index_created: false,
error:
error instanceof Error ? error.message : "Unknown error",
},
null,
2
),
},
],
};
}
}
// RASTER İŞLEM ARAÇLARI
case "raster-info": {
const { table_name, raster_column } = RasterInfoSchema.parse(args);
const sanitizedTableName = table_name.replace(/[^a-zA-Z0-9_]/g, "");
const sanitizedRasterColumn = raster_column || "rast";
const result = await client.query(`
SELECT
ST_NumBands(${sanitizedRasterColumn}) as num_bands,
ST_Width(${sanitizedRasterColumn}) as width,
ST_Height(${sanitizedRasterColumn}) as height,
ST_PixelWidth(${sanitizedRasterColumn}) as pixel_width,
ST_PixelHeight(${sanitizedRasterColumn}) as pixel_height,
ST_SRID(${sanitizedRasterColumn}) as srid,
ST_AsText(ST_Envelope(${sanitizedRasterColumn})) as envelope_wkt,
COUNT(*) as tile_count
FROM ${sanitizedTableName}
GROUP BY
ST_NumBands(${sanitizedRasterColumn}),
ST_Width(${sanitizedRasterColumn}),
ST_Height(${sanitizedRasterColumn}),
ST_PixelWidth(${sanitizedRasterColumn}),
ST_PixelHeight(${sanitizedRasterColumn}),
ST_SRID(${sanitizedRasterColumn}),
ST_AsText(ST_Envelope(${sanitizedRasterColumn}))
LIMIT 1;
`);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
table_name: sanitizedTableName,
raster_column: sanitizedRasterColumn,
raster_info: result.rows[0] || null,
},
null,
2
),
},
],
};
}
case "extract-raster-value": {
const { table_name, longitude, latitude, raster_column } =
ExtractRasterValueSchema.parse(args);
const sanitizedTableName = table_name.replace(/[^a-zA-Z0-9_]/g, "");
const sanitizedRasterColumn = raster_column || "rast";
const result = await client.query(
`
SELECT
ST_Value(${sanitizedRasterColumn}, ST_SetSRID(ST_MakePoint($1, $2), 4326)) as raster_value,
ST_Value(${sanitizedRasterColumn}, 1, ST_SetSRID(ST_MakePoint($1, $2), 4326)) as band1_value,
ST_NumBands(${sanitizedRasterColumn}) as num_bands
FROM ${sanitizedTableName}
WHERE ST_Intersects(${sanitizedRasterColumn}, ST_SetSRID(ST_MakePoint($1, $2), 4326))
LIMIT 1;
`,
[longitude, latitude]
);
return {
content: [
{
type: "text",
text: JSON.stringify(
{
table_name: sanitizedTableName,
raster_column: sanitizedRasterColumn,
coordinates: [longitude, latitude],
raster_value: result.rows[0]?.raster_value || null,
band1_value: result.rows[0]?.band1_value || null,
num_bands: result.rows[0]?.num_bands || 0,
found_intersection: result.rows.length > 0,
},
null,
2
),
},
],
};
}
default: {
return {
content: [
{
type: "text",
text: `Bilinmeyen tool: ${name}`,
},
],
};
}
}
} catch (error) {
const err = error as Error;
return {
content: [
{
type: "text",
text: `Hata: ${err.message}`,
},
],
};
} finally {
await client.end();
}
});
// Server'ı başlat
async function startServer() {
const transport = new StdioServerTransport();
await server.connect(transport);
}
// Server'ı çalıştır
startServer().catch((error) => {
process.stderr.write(`Server başlatma hatası: ${error}\n`);
process.exit(1);
});