TOP SQL SERVER SPATIAL FUNCTIONS

 

        ⭐ TOP SQL SERVER SPATIAL FUNCTIONS

With Detailed Descriptions + Beginner Examples + Professional Use Cases

---

✅ 1️⃣ STDistance() — Measure Distance Between Two Locations

📘 Description

STDistance() calculates how far one spatial object is from another.
It works similar to the distance you see in Google Maps, but computed inside SQL.

• Using geography → distance in meters
• Using geometry → units depend on coordinate system

---

🌱 BEGINNER EXAMPLE

DECLARE @a geography = geography::Point(17.385, 78.4867, 4326); 

DECLARE @b geography = geography::Point(12.9716, 77.5946, 4326);

 

SELECT @a.STDistance(@b) AS DistanceInMeters;

✔ Output

Distance between Hyderabad and Bengaluru in meters.

---

🧠 PROFESSIONAL EXAMPLE (Nearest Hospital Search)

SELECT TOP 1

    h.HospitalName,

    p.PatientLocation.STDistance(h.Location) AS Distance

FROM Hospitals h

ORDER BY p.PatientLocation.STDistance(h.Location);

✔ Real Use Case

Emergency system: find the nearest hospital to a patient.

---

---

✅ 2️⃣ STBuffer() — Create a Safety Zone / Radius Around a Feature

📘 Description

STBuffer() creates a circle or polygon around a location.
Used for influence zones, danger zones, service area coverage, etc.

Think:
👉 Create a 500m radius around a school.

---

🌱 BEGINNER EXAMPLE (500m Radius)

DECLARE @loc geography = geography::Point(17.38, 78.48, 4326);

 

SELECT @loc.STBuffer(500);  -- 500 meters

✔ Output

A circular polygon (500m radius).

---

🧠 PROFESSIONAL EXAMPLE (Road Noise Buffer Zone)

SELECT RoadID,

       geom.STBuffer(200) AS NoiseZone

FROM Roads;

✔ Real Use Case

Urban planning → identify houses within 200 meters of highways.

---

---

✅ 3️⃣ STIntersects() — Do Two Geometries Touch or Overlap?

📘 Description

STIntersects() checks whether two shapes touch, cross, or overlap.

• Returns 1 → if they interact
• Returns 0 → if completely separate

---

🌱 BEGINNER EXAMPLE

SELECT geom1.STIntersects(geom2);

---

🧠 PROFESSIONAL EXAMPLE (Parcels Affected by Flood Zones)

SELECT p.ParcelID

FROM Parcels p

JOIN FloodZones f

ON p.geom.STIntersects(f.geom) = 1;

✔ Real Use Case

Flood modelling → identify parcels affected by flood risk areas.

---

---

✅ 4️⃣ STContains() — Large Geometry Fully Contains Smaller Geometry

📘 Description

A contains B
→ B is entirely inside A (not touching boundary).

Example:
A land parcel contains a building.

---

🌱 BEGINNER EXAMPLE

SELECT p.geom.STContains(b.geom);

---

🧠 PROFESSIONAL EXAMPLE (Link Buildings to Parcels)

SELECT p.ParcelID, b.BuildingID

FROM Parcels p

JOIN Buildings b

ON p.geom.STContains(b.geom) = 1;

✔ Real Use Case

Useful for land records, property tax mapping, asset management.

---

---

✅ 5️⃣ STWithin() — Opposite of STContains

📘 Description

A is within B
→ A lies fully inside B.

Example:
A building is within the parcel.

---

🌱 BEGINNER EXAMPLE

SELECT b.Location.STWithin(city.geom);

---

🧠 PROFESSIONAL EXAMPLE (Find Restaurants Inside City Limits)

SELECT r.RestaurantName

FROM Restaurants r

JOIN City c

ON r.Location.STWithin(c.geom) = 1;

✔ Real Use Case

Useful for zoning, business licensing, and city-level planning.

---

---

✅ 6️⃣ STOverlaps() — Partial Shared Area (No Full Containment)

📘 Description

Two geometries overlap, but neither one fully contains the other.

Great for comparing polygon datasets.

---

🌱 BEGINNER EXAMPLE

SELECT a.geom.STOverlaps(b.geom);

---

🧠 PROFESSIONAL EXAMPLE (Forest Zone Overlap Analysis)

SELECT f1.Type, f2.Type

FROM ForestA f1

JOIN ForestB f2

ON f1.geom.STOverlaps(f2.geom) = 1;

✔ Real Use Case

Environmental analysis: forest types overlapping each other.

---

---

✅ 7️⃣ STDifference() — Remove Area (Erase Operation)

📘 Description

STDifference() subtracts one geometry from another.

Think:

• LAND − WATER = usable land
• CITY − FOREST = cleared area

---

🌱 BEGINNER EXAMPLE

geomA.STDifference(geomB);

---

🧠 PROFESSIONAL EXAMPLE (Remove Water Bodies from Land)

SELECT land.geom.STDifference(water.geom) AS UsableLand

FROM Land land, WaterBodies water;

✔ Real Use Case

Used for construction suitability, agriculture planning, and hazard removal.

---

---

✅ 8️⃣ STIntersection() — Get the Overlapping Area Only

📘 Description

STIntersection() returns the common overlapping area of two shapes.

---

🌱 BEGINNER EXAMPLE

geom1.STIntersection(geom2);

---

🧠 PROFESSIONAL EXAMPLE (Parcels Under Flood Influence)

SELECT p.ParcelID,

       p.geom.STIntersection(f.geom) AS FloodArea

FROM Parcels p

JOIN FloodZones f

ON p.geom.STIntersects(f.geom) = 1;

✔ Real Use Case

Calculate actual affected area of each parcel.

---

---

✅ 9️⃣ STUnion() — Merge Geometries (Dissolve / Combine)

📘 Description

STUnion() merges two or more geometries into one.
Useful for:

• Merging boundaries
• Creating administrative layers
• Removing gaps

---

🌱 BEGINNER EXAMPLE

geom1.STUnion(geom2);

---

🧠 PROFESSIONAL EXAMPLE (Dissolve Districts into State Boundary)

SELECT State,

       geometry::UnionAggregate(geom) AS StateBoundary

FROM Districts

GROUP BY State;

✔ Real Use Case

Used in creating regional boundaries, land-use layers, and composite maps.

---