Spatial and Hierarchy Extensions

Spatial and Hierarchy Extensions

postgres-mcp provides comprehensive support for geospatial and hierarchical data through PostGIS (15 tools) and ltree (8 tools).

---

PostGIS (15 Tools)

PostGIS transforms PostgreSQL into a spatial database with support for geographic objects, spatial queries, and GIS operations.

Installation

CREATE EXTENSION IF NOT EXISTS postgis;

Tools

Tool	Description
pg_postgis_create_extension	Enable PostGIS extension
pg_geometry_column	Add geometry/geography column
pg_point_in_polygon	Test if point is within polygon
pg_distance	Calculate distances between geometries
pg_buffer	Create buffer zones
pg_intersection	Compute geometry intersections
pg_bounding_box	Calculate bounding boxes
pg_spatial_index	Create GiST spatial index
pg_geocode	Geocoding operations
pg_geo_transform	Transform coordinate systems (SRID auto-detection)
pg_geo_index_optimize	Optimize spatial indexes
pg_geo_cluster	Spatial clustering
pg_geometry_buffer	Advanced geometry buffering
pg_geometry_intersection	Advanced intersection operations
pg_geometry_transform	Geometry coordinate transforms

Geometry vs Geography

Type	Best For	Distance Units
geometry	Flat/projected data, local areas	Projection units (meters, feet)
geography	Global data, spherical Earth	Meters (always)

Example: Create Spatial Table

-- Create table with geography column
CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  name TEXT,
  location geography(POINT, 4326)
);

-- Create spatial index
CREATE INDEX ON locations USING GIST (location);

Example: Find Nearby Points

-- Find locations within 1000 meters
SELECT name, ST_Distance(location, ST_MakePoint(-73.99, 40.73)::geography) AS distance
FROM locations
WHERE ST_DWithin(location, ST_MakePoint(-73.99, 40.73)::geography, 1000)
ORDER BY distance;

Common Spatial Functions

Function	Description
ST_Distance	Distance between geometries
ST_DWithin	Test if within distance
ST_Contains	Test containment
ST_Intersects	Test intersection
ST_Buffer	Create buffer zone
ST_Transform	Change coordinate system
ST_AsGeoJSON	Export as GeoJSON

---

ltree (8 Tools)

ltree provides a data type for hierarchical tree-like structures, ideal for categories, organizational hierarchies, and file paths.

Installation

CREATE EXTENSION IF NOT EXISTS ltree;

Tools

Tool	Description
pg_ltree_create_extension	Enable ltree extension
pg_ltree_query	Query tree structure
pg_ltree_subpath	Extract subpath from ltree
pg_ltree_lca	Find lowest common ancestor
pg_ltree_match	Match against lquery/ltxtquery patterns
pg_ltree_list_columns	List ltree columns in database
pg_ltree_convert_column	Convert column to ltree type
pg_ltree_create_index	Create GiST index for tree queries

Path Syntax

ltree paths use dot notation:

Top.Science.Astronomy
Top.Science.Biology
Top.Collections.Pictures.Astronomy

Example: Create Hierarchy Table

-- Create categories table
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  path ltree,
  name TEXT
);

-- Create index for fast tree queries
CREATE INDEX ON categories USING GIST (path);

Example: Query Hierarchy

-- Find all descendants of 'Top.Science'
SELECT * FROM categories
WHERE path <@ 'Top.Science';

-- Find ancestors of a node
SELECT * FROM categories
WHERE 'Top.Science.Astronomy' <@ path;

-- Find items matching pattern
SELECT * FROM categories
WHERE path ~ '*.Astronomy';

ltree Operators

Operator	Description
<@	Is left a descendant of right?
@>	Is left an ancestor of right?
~	Match against lquery pattern
?	Match against ltxtquery
||	Concatenate paths

---

Tool Filtering for Spatial Workloads

Both Extensions

--tool-filter postgis,ltree

PostGIS Only

--tool-filter postgis

Custom: ltree Only

--tool-filter ltree

---

Related Resources

Resource	URI	Description
PostGIS Status	postgres://postgis	Spatial columns, index status

---

Related Prompts

Prompt	Description
pg_setup_postgis	Complete PostGIS setup guide
pg_setup_ltree	Complete ltree setup guide

---

Related

• Extension-Overview - All supported extensions
• Tool-Filtering - Filter configuration