Gis
Overview
Geographic information systems (GIS) combine geometric modeling with spatial reasoning to analyze location-based data. In this category, the central idea is planar computational geometry: representing shapes as points, lines, and polygons, then computing measurements and set relationships on those shapes. These operations matter in analytics and engineering workflows such as service-area studies, proximity checks, map cleaning, and spatial quality control. Even when data originates in spreadsheets, GIS functions make it possible to apply rigorous geometric logic to real-world coordinate data.
The shared foundation across these tools is Well-Known Text (WKT) geometry encoding and set-based geometry operations. Geometries are treated as sets of points in the plane, so area, distance, containment, intersection, and union can be expressed with precise mathematical semantics such as A \cap B, \bigcup_i G_i, and d(A,B)=\min\limits_{a\in A,\,b\in B}\lVert a-b\rVert_2. Another unifying concept is topology-aware transformation, where simplification or buffering changes coordinate detail while preserving intended spatial structure. Together, these ideas let analysts move from raw coordinates to interpretable spatial summaries and decisions.
Implementation is powered primarily by Shapely, a Python library for robust geometric predicates and operations built on GEOS, with visualization support via Matplotlib. Shapely provides the core geometry constructors, measurements, and overlay operations used throughout this category, while Matplotlib handles rendering when a chart image is required. This pairing is common in geospatial Python workflows because it separates geometric correctness from presentation concerns.
The geometry-construction tools create canonical spatial objects directly from tabular inputs. SHAPELY_POINT builds zero-dimensional point features from (x,y) coordinates, SHAPELY_LINESTRING builds ordered polylines for routes or traces, and SHAPELY_POLYGON builds areal boundaries with optional interior holes. These constructors are typically the first step in a pipeline, turning spreadsheet ranges into valid geometric primitives. In practice, they support tasks like converting survey coordinates to boundaries, modeling paths, or preparing input geometries for downstream predicates and overlays.
Measurement and proximity functions quantify geometric size and separation for reporting and threshold checks. SHAPELY_AREA computes planar area for polygons and multipolygons, while SHAPELY_DISTANCE returns minimum Cartesian separation between two geometries, including 0 when they overlap. SHAPELY_CONTAINS adds a directional spatial predicate for point-in-polygon and containment rules where boundary handling matters. These are core for compliance zones, nearest-feature logic, and validating whether candidate geometries satisfy spatial constraints.
Shape-transformation and generalization tools modify geometry while preserving analytical intent. SHAPELY_BUFFER creates offset regions via a distance parameter, supporting catchment modeling, tolerance envelopes, and erosion/dilation-style operations. SHAPELY_CONVEX_HULL wraps features in the smallest convex enclosure, useful for fast bounding approximations and footprint summarization. SHAPELY_SIMPLIFY reduces vertex complexity using tolerance-controlled Douglas–Peucker simplification, which is important for reducing noise and improving performance on dense geometries.
Overlay, aggregation, and visualization tools combine and communicate spatial results. SHAPELY_INTERSECT computes shared geometry between inputs, enabling overlap extraction such as clipping service areas to administrative boundaries. SHAPELY_UNION_ALL merges multiple geometries into one combined result, which is useful for dissolving adjacent zones or building composite footprints from many parts. SHAPELY_PLOT then renders one or more WKT geometries into a 2D image for rapid visual QA, reporting, and stakeholder communication. Used together, these functions support a full mini-workflow: construct geometries, measure and transform them, combine results, and produce visual output.
SHAPELY_AREA
Computes the planar area of a geometry provided as Well-Known Text (WKT). This is most meaningful for polygonal geometries; for points and lines, the area is 0.
For a polygonal region G, area is computed as:
A = \iint_G 1\, dA
The function parses the input WKT into a Shapely geometry and returns the resulting numeric area.
Excel Usage
=SHAPELY_AREA(geometry)geometry(str, required): WKT string representation of the geometry.
Returns (float): Area of the geometry.
Example 1: Area of a unit square
Inputs:
| geometry |
|---|
| POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0)) |
Excel formula:
=SHAPELY_AREA("POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))")Expected output:
1
Example 2: Area of a rectangle
Inputs:
| geometry |
|---|
| POLYGON ((0 0, 4 0, 4 2, 0 2, 0 0)) |
Excel formula:
=SHAPELY_AREA("POLYGON ((0 0, 4 0, 4 2, 0 2, 0 0))")Expected output:
8
Example 3: Area of a point
Inputs:
| geometry |
|---|
| POINT (10 20) |
Excel formula:
=SHAPELY_AREA("POINT (10 20)")Expected output:
0
Example 4: Area of a multipolygon
Inputs:
| geometry |
|---|
| MULTIPOLYGON (((0 0, 2 0, 2 2, 0 2, 0 0)), ((3 0, 4 0, 4 1, 3 1, 3 0))) |
Excel formula:
=SHAPELY_AREA("MULTIPOLYGON (((0 0, 2 0, 2 2, 0 2, 0 0)), ((3 0, 4 0, 4 1, 3 1, 3 0)))")Expected output:
5
Python Code
Show Code
from shapely import wkt
def shapely_area(geometry):
"""
Calculate the area of a geometry.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.area.html
This example function is provided as-is without any representation of accuracy.
Args:
geometry (str): WKT string representation of the geometry.
Returns:
float: Area of the geometry.
"""
try:
if not geometry:
return 0.0
geom = wkt.loads(geometry)
return float(geom.area)
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_BUFFER
Computes a buffer geometry around an input geometry from WKT using a specified buffer distance. Positive distance expands geometry and negative distance contracts it where possible.
Conceptually, the buffer is the Minkowski sum (or difference for negative distance) with a radius-d disk:
G \oplus B_d
The function returns an Excel data type with the buffered WKT as the primary value plus area and perimeter properties.
Excel Usage
=SHAPELY_BUFFER(geometry, distance, quad_segs)geometry(str, required): Input geometry (WKT).distance(float, required): The radius of the buffer.quad_segs(int, optional, default: 2): Number of segments used to approximate a quarter circle.
Returns (dict): Excel Entity representing the buffered geometry (Polygon).
Example 1: Buffer around a point
Inputs:
| geometry | distance | quad_segs |
|---|---|---|
| POINT (0 0) | 1 | 1 |
Excel formula:
=SHAPELY_BUFFER("POINT (0 0)", 1, 1)Expected output:
{"type":"String","basicValue":"POLYGON ((1 0, 0.0000000000000001 -1, -1 -0.0000000000000001, -0.0000000000000002 1, 1 0))","properties":{"Area":{"type":"Double","basicValue":2},"Perimeter":{"type":"Double","basicValue":5.65685},"WKT":{"type":"String","basicValue":"POLYGON ((1 0, 0.0000000000000001 -1, -1 -0.0000000000000001, -0.0000000000000002 1, 1 0))"}}}
Example 2: Buffer around a line
Inputs:
| geometry | distance | quad_segs |
|---|---|---|
| LINESTRING (0 0, 10 0) | 2 | 1 |
Excel formula:
=SHAPELY_BUFFER("LINESTRING (0 0, 10 0)", 2, 1)Expected output:
{"type":"String","basicValue":"POLYGON ((10 2, 12 0, 10 -2, 0 -2, -2 0.0000000000000002, 0 2, 10 2))","properties":{"Area":{"type":"Double","basicValue":48},"Perimeter":{"type":"Double","basicValue":31.3137},"WKT":{"type":"String","basicValue":"POLYGON ((10 2, 12 0, 10 -2, 0 -2, -2 0.0000000000000002, 0 2, 10 2))"}}}
Example 3: Buffer around a polygon
Inputs:
| geometry | distance | quad_segs |
|---|---|---|
| POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0)) | 0.5 | 1 |
Excel formula:
=SHAPELY_BUFFER("POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0))", 0.5, 1)Expected output:
{"type":"String","basicValue":"POLYGON ((-0.5 0, -0.5 2, 0 2.5, 2 2.5, 2.5 2, 2.5 0, 2 -0.5, 0 -0.5, -0.5 0))","properties":{"Area":{"type":"Double","basicValue":8.5},"Perimeter":{"type":"Double","basicValue":10.8284},"WKT":{"type":"String","basicValue":"POLYGON ((-0.5 0, -0.5 2, 0 2.5, 2 2.5, 2.5 2, 2.5 0, 2 -0.5, 0 -0.5, -0.5 0))"}}}
Example 4: Negative buffer on polygon
Inputs:
| geometry | distance |
|---|---|
| POLYGON ((0 0, 6 0, 6 6, 0 6, 0 0)) | -1 |
Excel formula:
=SHAPELY_BUFFER("POLYGON ((0 0, 6 0, 6 6, 0 6, 0 0))", -1)Expected output:
{"type":"String","basicValue":"POLYGON ((1 1, 1 5, 5 5, 5 1, 1 1))","properties":{"Area":{"type":"Double","basicValue":16},"Perimeter":{"type":"Double","basicValue":16},"WKT":{"type":"String","basicValue":"POLYGON ((1 1, 1 5, 5 5, 5 1, 1 1))"}}}
Python Code
Show Code
from shapely import wkt
def shapely_buffer(geometry, distance, quad_segs=2):
"""
Returns a representation of all points within a given distance of the this geometric object.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.buffer.html
This example function is provided as-is without any representation of accuracy.
Args:
geometry (str): Input geometry (WKT).
distance (float): The radius of the buffer.
quad_segs (int, optional): Number of segments used to approximate a quarter circle. Default is 2.
Returns:
dict: Excel Entity representing the buffered geometry (Polygon).
"""
try:
if not geometry:
return "Error: Geometry required"
geom = wkt.loads(geometry)
buffered = geom.buffer(distance, quad_segs=quad_segs)
wkt_str = buffered.wkt
return {
"type": "String",
"basicValue": wkt_str,
"properties": {
"Area": {"type": "Double", "basicValue": float(buffered.area)},
"Perimeter": {"type": "Double", "basicValue": float(buffered.length)},
"WKT": {"type": "String", "basicValue": wkt_str}
}
}
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_CONTAINS
Tests the spatial predicate “contains” between two geometries expressed as WKT. A geometry contains another if the second lies completely in the interior of the first, with no points in the exterior.
This predicate is directional and generally follows:
ext{contains}(A, B) \neq \text{contains}(B, A)
Boundary-only contact does not satisfy containment under this definition.
Excel Usage
=SHAPELY_CONTAINS(geom_one, geom_two)geom_one(str, required): Container geometry (WKT).geom_two(str, required): Content geometry (WKT).
Returns (bool): True if geom1 contains geom2.
Example 1: Point in Polygon
Inputs:
| geom_one | geom_two |
|---|---|
| POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0)) | POINT (5 5) |
Excel formula:
=SHAPELY_CONTAINS("POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))", "POINT (5 5)")Expected output:
true
Example 2: Point outside Polygon
Inputs:
| geom_one | geom_two |
|---|---|
| POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0)) | POINT (20 20) |
Excel formula:
=SHAPELY_CONTAINS("POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))", "POINT (20 20)")Expected output:
false
Example 3: Polygon contains inner line
Inputs:
| geom_one | geom_two |
|---|---|
| POLYGON ((0 0, 8 0, 8 8, 0 8, 0 0)) | LINESTRING (1 1, 7 7) |
Excel formula:
=SHAPELY_CONTAINS("POLYGON ((0 0, 8 0, 8 8, 0 8, 0 0))", "LINESTRING (1 1, 7 7)")Expected output:
true
Example 4: Polygon does not contain boundary point
Inputs:
| geom_one | geom_two |
|---|---|
| POLYGON ((0 0, 8 0, 8 8, 0 8, 0 0)) | POINT (0 0) |
Excel formula:
=SHAPELY_CONTAINS("POLYGON ((0 0, 8 0, 8 8, 0 8, 0 0))", "POINT (0 0)")Expected output:
false
Python Code
Show Code
from shapely import wkt
def shapely_contains(geom_one, geom_two):
"""
Returns True if the first geometry contains the second.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.contains.html
This example function is provided as-is without any representation of accuracy.
Args:
geom_one (str): Container geometry (WKT).
geom_two (str): Content geometry (WKT).
Returns:
bool: True if geom1 contains geom2.
"""
try:
if not geom_one or not geom_two:
return False
g_one = wkt.loads(geom_one)
g_two = wkt.loads(geom_two)
return bool(g_one.contains(g_two))
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_CONVEX_HULL
Computes the convex hull of an input geometry provided as WKT. The convex hull is the smallest convex geometry that encloses all points of the original geometry.
In optimization terms, it is the minimal convex set H such that:
G \subseteq H
The function returns the hull WKT as the primary value along with area and perimeter metadata.
Excel Usage
=SHAPELY_CONVEX_HULL(geometry)geometry(str, required): Input geometry (WKT).
Returns (dict): Excel Entity representing the convex hull.
Example 1: Convex hull of points
Inputs:
| geometry |
|---|
| MULTIPOINT (0 0, 10 0, 10 10, 0 10, 5 5) |
Excel formula:
=SHAPELY_CONVEX_HULL("MULTIPOINT (0 0, 10 0, 10 10, 0 10, 5 5)")Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0))","properties":{"Area":{"type":"Double","basicValue":100},"Perimeter":{"type":"Double","basicValue":40},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0))"}}}
Example 2: Convex hull of a line
Inputs:
| geometry |
|---|
| LINESTRING (0 0, 3 0, 6 0) |
Excel formula:
=SHAPELY_CONVEX_HULL("LINESTRING (0 0, 3 0, 6 0)")Expected output:
{"type":"String","basicValue":"LINESTRING (0 0, 6 0)","properties":{"Area":{"type":"Double","basicValue":0},"Perimeter":{"type":"Double","basicValue":6},"WKT":{"type":"String","basicValue":"LINESTRING (0 0, 6 0)"}}}
Example 3: Convex hull of concave polygon
Inputs:
| geometry |
|---|
| POLYGON ((0 0, 4 0, 4 4, 2 2, 0 4, 0 0)) |
Excel formula:
=SHAPELY_CONVEX_HULL("POLYGON ((0 0, 4 0, 4 4, 2 2, 0 4, 0 0))")Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 0 4, 4 4, 4 0, 0 0))","properties":{"Area":{"type":"Double","basicValue":16},"Perimeter":{"type":"Double","basicValue":16},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 0 4, 4 4, 4 0, 0 0))"}}}
Example 4: Convex hull of a point
Inputs:
| geometry |
|---|
| POINT (2 3) |
Excel formula:
=SHAPELY_CONVEX_HULL("POINT (2 3)")Expected output:
{"type":"String","basicValue":"POINT (2 3)","properties":{"Area":{"type":"Double","basicValue":0},"Perimeter":{"type":"Double","basicValue":0},"WKT":{"type":"String","basicValue":"POINT (2 3)"}}}
Python Code
Show Code
from shapely import wkt
def shapely_convex_hull(geometry):
"""
Returns the smallest convex polygon that contains all the points in the object.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.convex_hull.html
This example function is provided as-is without any representation of accuracy.
Args:
geometry (str): Input geometry (WKT).
Returns:
dict: Excel Entity representing the convex hull.
"""
try:
if not geometry:
return "Error: Geometry required"
geom = wkt.loads(geometry)
hull = geom.convex_hull
wkt_str = hull.wkt
return {
"type": "String",
"basicValue": wkt_str,
"properties": {
"Area": {"type": "Double", "basicValue": float(hull.area)},
"Perimeter": {"type": "Double", "basicValue": float(hull.length)},
"WKT": {"type": "String", "basicValue": wkt_str}
}
}
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_DISTANCE
Computes the minimum Cartesian distance between two geometries provided in WKT format.
The distance corresponds to the minimum point-to-point separation:
d(A,B) = \min_{a\in A,\, b\in B} \lVert a-b \rVert_2
Intersecting or overlapping geometries yield a distance of 0.
Excel Usage
=SHAPELY_DISTANCE(geom_one, geom_two)geom_one(str, required): First geometry (WKT).geom_two(str, required): Second geometry (WKT).
Returns (float): Minimum distance.
Example 1: Distance between two points
Inputs:
| geom_one | geom_two |
|---|---|
| POINT (0 0) | POINT (3 4) |
Excel formula:
=SHAPELY_DISTANCE("POINT (0 0)", "POINT (3 4)")Expected output:
5
Example 2: Distance between point and line
Inputs:
| geom_one | geom_two |
|---|---|
| POINT (0 0) | LINESTRING (10 0, 10 10) |
Excel formula:
=SHAPELY_DISTANCE("POINT (0 0)", "LINESTRING (10 0, 10 10)")Expected output:
10
Example 3: Distance between overlapping polygons
Inputs:
| geom_one | geom_two |
|---|---|
| POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0)) | POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1)) |
Excel formula:
=SHAPELY_DISTANCE("POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0))", "POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1))")Expected output:
0
Example 4: Distance between parallel lines
Inputs:
| geom_one | geom_two |
|---|---|
| LINESTRING (0 0, 4 0) | LINESTRING (0 3, 4 3) |
Excel formula:
=SHAPELY_DISTANCE("LINESTRING (0 0, 4 0)", "LINESTRING (0 3, 4 3)")Expected output:
3
Python Code
Show Code
from shapely import wkt
def shapely_distance(geom_one, geom_two):
"""
Calculate the minimum distance between two geometries.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.distance.html
This example function is provided as-is without any representation of accuracy.
Args:
geom_one (str): First geometry (WKT).
geom_two (str): Second geometry (WKT).
Returns:
float: Minimum distance.
"""
try:
if not geom_one or not geom_two:
return "Error: Both geometries must be provided"
g_one = wkt.loads(geom_one)
g_two = wkt.loads(geom_two)
return float(g_one.distance(g_two))
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_INTERSECT
Computes the geometric intersection between two WKT geometries, returning only the shared spatial portion.
Set-theoretically, the operation is:
A \cap B
The result may be empty or may have a different geometry type than either input. The function returns an Excel data type containing WKT and key metadata.
Excel Usage
=SHAPELY_INTERSECT(geom_one, geom_two)geom_one(str, required): First geometry (WKT).geom_two(str, required): Second geometry (WKT).
Returns (dict): Excel Entity representing the intersection geometry.
Example 1: Intersection of overlapping squares
Inputs:
| geom_one | geom_two |
|---|---|
| POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0)) | POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1)) |
Excel formula:
=SHAPELY_INTERSECT("POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0))", "POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1))")Expected output:
{"type":"String","basicValue":"POLYGON ((2 2, 2 1, 1 1, 1 2, 2 2))","properties":{"WKT":{"type":"String","basicValue":"POLYGON ((2 2, 2 1, 1 1, 1 2, 2 2))"},"Is Empty":{"type":"Boolean","basicValue":false},"Geometry Type":{"type":"String","basicValue":"Polygon"},"Area":{"type":"Double","basicValue":1},"Length":{"type":"Double","basicValue":4}}}
Example 2: Disjoint geometries (empty intersection)
Inputs:
| geom_one | geom_two |
|---|---|
| POINT (0 0) | POINT (10 10) |
Excel formula:
=SHAPELY_INTERSECT("POINT (0 0)", "POINT (10 10)")Expected output:
{"type":"String","basicValue":"POINT EMPTY","properties":{"WKT":{"type":"String","basicValue":"POINT EMPTY"},"Is Empty":{"type":"Boolean","basicValue":true},"Geometry Type":{"type":"String","basicValue":"Point"}}}
Example 3: Intersection of overlapping lines
Inputs:
| geom_one | geom_two |
|---|---|
| LINESTRING (0 0, 5 0) | LINESTRING (3 0, 8 0) |
Excel formula:
=SHAPELY_INTERSECT("LINESTRING (0 0, 5 0)", "LINESTRING (3 0, 8 0)")Expected output:
{"type":"String","basicValue":"LINESTRING (3 0, 5 0)","properties":{"WKT":{"type":"String","basicValue":"LINESTRING (3 0, 5 0)"},"Is Empty":{"type":"Boolean","basicValue":false},"Geometry Type":{"type":"String","basicValue":"LineString"},"Area":{"type":"Double","basicValue":0},"Length":{"type":"Double","basicValue":2}}}
Example 4: Point intersection with polygon
Inputs:
| geom_one | geom_two |
|---|---|
| POINT (1 1) | POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0)) |
Excel formula:
=SHAPELY_INTERSECT("POINT (1 1)", "POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0))")Expected output:
{"type":"String","basicValue":"POINT (1 1)","properties":{"WKT":{"type":"String","basicValue":"POINT (1 1)"},"Is Empty":{"type":"Boolean","basicValue":false},"Geometry Type":{"type":"String","basicValue":"Point"},"Area":{"type":"Double","basicValue":0},"Length":{"type":"Double","basicValue":0}}}
Python Code
Show Code
from shapely import wkt
def shapely_intersect(geom_one, geom_two):
"""
Returns a representation of the intersection of this object with another geometric object.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.intersection.html
This example function is provided as-is without any representation of accuracy.
Args:
geom_one (str): First geometry (WKT).
geom_two (str): Second geometry (WKT).
Returns:
dict: Excel Entity representing the intersection geometry.
"""
try:
if not geom_one or not geom_two:
return "Error: Both geometries required"
g_one = wkt.loads(geom_one)
g_two = wkt.loads(geom_two)
result = g_one.intersection(g_two)
wkt_str = result.wkt
props = {
"WKT": {"type": "String", "basicValue": wkt_str},
"Is Empty": {"type": "Boolean", "basicValue": bool(result.is_empty)},
"Geometry Type": {"type": "String", "basicValue": result.geom_type}
}
if not result.is_empty:
props["Area"] = {"type": "Double", "basicValue": float(result.area)}
props["Length"] = {"type": "Double", "basicValue": float(result.length)}
return {
"type": "String",
"basicValue": wkt_str,
"properties": props
}
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_LINESTRING
Creates a LineString geometry from a 2D coordinate table where each row contains an (x,y) pair.
A line string is an ordered polyline made of connected linear segments. Its total length is the sum of segment lengths:
L = \sum_{i=1}^{n-1} \lVert p_{i+1} - p_i \rVert_2
The function returns an Excel data type with WKT as the primary value and line length as a property.
Excel Usage
=SHAPELY_LINESTRING(points)points(list[list], required): List of [x, y] coordinates (e.g., [[0,0], [1,1]]).
Returns (dict): Excel Entity representing the line string (WKT).
Example 1: Simple Line
Inputs:
| points | |
|---|---|
| 0 | 0 |
| 1 | 1 |
Excel formula:
=SHAPELY_LINESTRING({0,0;1,1})Expected output:
{"type":"String","basicValue":"LINESTRING (0 0, 1 1)","properties":{"Length":{"type":"Double","basicValue":1.41421},"WKT":{"type":"String","basicValue":"LINESTRING (0 0, 1 1)"}}}
Example 2: Multi-segment Line
Inputs:
| points | |
|---|---|
| 0 | 0 |
| 10 | 0 |
| 10 | 10 |
Excel formula:
=SHAPELY_LINESTRING({0,0;10,0;10,10})Expected output:
{"type":"String","basicValue":"LINESTRING (0 0, 10 0, 10 10)","properties":{"Length":{"type":"Double","basicValue":20},"WKT":{"type":"String","basicValue":"LINESTRING (0 0, 10 0, 10 10)"}}}
Example 3: Descending line segment
Inputs:
| points | |
|---|---|
| 5 | 5 |
| 1 | 1 |
Excel formula:
=SHAPELY_LINESTRING({5,5;1,1})Expected output:
{"type":"String","basicValue":"LINESTRING (5 5, 1 1)","properties":{"Length":{"type":"Double","basicValue":5.65685},"WKT":{"type":"String","basicValue":"LINESTRING (5 5, 1 1)"}}}
Example 4: Polyline with four points
Inputs:
| points | |
|---|---|
| 0 | 0 |
| 2 | 1 |
| 4 | 1 |
| 6 | 0 |
Excel formula:
=SHAPELY_LINESTRING({0,0;2,1;4,1;6,0})Expected output:
{"type":"String","basicValue":"LINESTRING (0 0, 2 1, 4 1, 6 0)","properties":{"Length":{"type":"Double","basicValue":6.47214},"WKT":{"type":"String","basicValue":"LINESTRING (0 0, 2 1, 4 1, 6 0)"}}}
Python Code
Show Code
from shapely import LineString
def shapely_linestring(points):
"""
Create a geometric line string from a list of points.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.LineString.html
This example function is provided as-is without any representation of accuracy.
Args:
points (list[list]): List of [x, y] coordinates (e.g., [[0,0], [1,1]]).
Returns:
dict: Excel Entity representing the line string (WKT).
"""
try:
if not isinstance(points, list):
return "Error: points must be a list of coordinates"
clean_points = []
for row in points:
if isinstance(row, list) and len(row) >= 2:
try:
clean_points.append((float(row[0]), float(row[1])))
except (TypeError, ValueError):
continue
if len(clean_points) < 2:
return "Error: LineString requires at least 2 points"
line = LineString(clean_points)
wkt_str = line.wkt
return {
"type": "String",
"basicValue": wkt_str,
"properties": {
"Length": {"type": "Double", "basicValue": float(line.length)},
"WKT": {"type": "String", "basicValue": wkt_str}
}
}
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_PLOT
Renders one or more WKT geometries to a 2D plot and returns a base64-encoded PNG image string suitable for display in Excel.
The function parses geometry inputs, draws points/lines/polygons with optional color and transparency, then exports the figure as an inline image URI.
Transparency is controlled by an alpha parameter in the interval:
0 \leq \alpha \leq 1
Excel Usage
=SHAPELY_PLOT(geometries, color, alpha)geometries(list[list], required): List of WKT strings to plot.color(str, optional, default: null): Color of the plot elements.alpha(float, optional, default: 0.5): Transparency (0.0 to 1.0).
Returns (str): Base64 encoded PNG image.
Example 1: Plot a line
Inputs:
| geometries |
|---|
| LINESTRING (0 0, 2 2, 4 1) |
Excel formula:
=SHAPELY_PLOT(LINESTRING (0 0, 2 2, 4 1))Expected output:
"chart"
Example 2: Plot mixed geometries
Inputs:
| geometries | alpha |
|---|---|
| POINT (0 0),LINESTRING (0 0, 1 2),POLYGON ((2 0, 3 0, 3 1, 2 1, 2 0)) | 0.6 |
Excel formula:
=SHAPELY_PLOT(POINT (0 0),LINESTRING (0 0, 1 2),POLYGON ((2 0, 3 0, 3 1, 2 1, 2 0)), 0.6)Expected output:
"chart"
Example 3: Plot a square
Inputs:
| geometries |
|---|
| POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0)) |
Excel formula:
=SHAPELY_PLOT(POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0)))Expected output:
"chart"
Example 4: Plot points
Inputs:
| geometries | color |
|---|---|
| POINT (0 0),POINT (1 1) | red |
Excel formula:
=SHAPELY_PLOT(POINT (0 0),POINT (1 1), "red")Expected output:
"chart"
Python Code
Show Code
import sys
import matplotlib
if sys.platform == "emscripten":
matplotlib.use('Agg')
import matplotlib.pyplot as plt
from shapely import wkt
import io
import base64
import numpy as np
def shapely_plot(geometries, color=None, alpha=0.5):
"""
Plot geometries and return an image.
See: https://matplotlib.org/stable/api/_as_gen/matplotlib.pyplot.plot.html
This example function is provided as-is without any representation of accuracy.
Args:
geometries (list[list]): List of WKT strings to plot.
color (str, optional): Color of the plot elements. Default is None.
alpha (float, optional): Transparency (0.0 to 1.0). Default is 0.5.
Returns:
str: Base64 encoded PNG image.
"""
try:
# Helper to flatten inputs
def flatten(x):
if isinstance(x, list):
out = []
for item in x:
out.extend(flatten(item))
return out
return [x]
raw_list = flatten(geometries)
wkt_list = [str(s) for s in raw_list if s and isinstance(s, str)]
if not wkt_list:
return "Error: No geometries provided"
# Parse geometries
geoms = []
for s in wkt_list:
try:
geoms.append(wkt.loads(s))
except:
pass
if not geoms:
return "Error: No valid geometries found"
# Setup plot
plt.clf()
fig, ax = plt.subplots(figsize=(6, 6))
# Default colors cycle if not provided
prop_cycle = plt.rcParams['axes.prop_cycle']
colors = prop_cycle.by_key()['color']
# Plot helper
def plot_geom(g, c=None):
if g.is_empty: return
if g.geom_type == 'Point':
x, y = g.x, g.y
ax.plot(x, y, 'o', color=c, alpha=alpha)
elif g.geom_type == 'LineString':
x, y = g.xy
ax.plot(x, y, '-', color=c, alpha=alpha, linewidth=2)
elif g.geom_type == 'Polygon':
# Plot exterior
x, y = g.exterior.xy
ax.plot(x, y, '-', color=c, alpha=1.0) # Boundary solid
ax.fill(x, y, color=c, alpha=alpha) # Fill transparent
# Plot holes
for interior in g.interiors:
xi, yi = interior.xy
ax.plot(xi, yi, '-', color='w', linewidth=1) # Hole boundary
elif g.geom_type.startswith('Multi') or g.geom_type == 'GeometryCollection':
for part in g.geoms:
plot_geom(part, c)
for i, g in enumerate(geoms):
c = color if color else colors[i % len(colors)]
plot_geom(g, c)
ax.set_aspect('equal')
ax.grid(True, linestyle=':', alpha=0.6)
# Determine bounds logic? matplotlib handles auto-scaling usually.
# Return image
buf = io.BytesIO()
plt.savefig(buf, format='png', bbox_inches='tight')
plt.close(fig)
buf.seek(0)
img_str = base64.b64encode(buf.read()).decode('utf-8')
return f"data:image/png;base64,{img_str}"
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_POINT
Creates a 2D point geometry from numeric x and y coordinates.
A point is a zero-dimensional geometry represented as:
P = (x, y)
The function returns an Excel data type with the point WKT as the primary value and coordinate properties for direct access.
Excel Usage
=SHAPELY_POINT(x, y)x(float, required): X coordinate.y(float, required): Y coordinate.
Returns (dict): Excel Entity representing the point (WKT).
Example 1: Basic Point
Inputs:
| x | y |
|---|---|
| 10 | 20 |
Excel formula:
=SHAPELY_POINT(10, 20)Expected output:
{"type":"String","basicValue":"POINT (10 20)","properties":{"X":{"type":"Double","basicValue":10},"Y":{"type":"Double","basicValue":20},"WKT":{"type":"String","basicValue":"POINT (10 20)"}}}
Example 2: Floating Point Coordinates
Inputs:
| x | y |
|---|---|
| 1.5 | -3.2 |
Excel formula:
=SHAPELY_POINT(1.5, -3.2)Expected output:
{"type":"String","basicValue":"POINT (1.5 -3.2)","properties":{"X":{"type":"Double","basicValue":1.5},"Y":{"type":"Double","basicValue":-3.2},"WKT":{"type":"String","basicValue":"POINT (1.5 -3.2)"}}}
Example 3: Point at the origin
Inputs:
| x | y |
|---|---|
| 0 | 0 |
Excel formula:
=SHAPELY_POINT(0, 0)Expected output:
{"type":"String","basicValue":"POINT (0 0)","properties":{"X":{"type":"Double","basicValue":0},"Y":{"type":"Double","basicValue":0},"WKT":{"type":"String","basicValue":"POINT (0 0)"}}}
Example 4: Point with negative coordinates
Inputs:
| x | y |
|---|---|
| -12.75 | -4.25 |
Excel formula:
=SHAPELY_POINT(-12.75, -4.25)Expected output:
{"type":"String","basicValue":"POINT (-12.75 -4.25)","properties":{"X":{"type":"Double","basicValue":-12.75},"Y":{"type":"Double","basicValue":-4.25},"WKT":{"type":"String","basicValue":"POINT (-12.75 -4.25)"}}}
Python Code
Show Code
from shapely import Point
def shapely_point(x, y):
"""
Create a geometric point.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.Point.html
This example function is provided as-is without any representation of accuracy.
Args:
x (float): X coordinate.
y (float): Y coordinate.
Returns:
dict: Excel Entity representing the point (WKT).
"""
try:
pt = Point(x, y)
wkt_str = pt.wkt
return {
"type": "String",
"basicValue": wkt_str,
"properties": {
"X": {"type": "Double", "basicValue": float(x)},
"Y": {"type": "Double", "basicValue": float(y)},
"WKT": {"type": "String", "basicValue": wkt_str}
}
}
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_POLYGON
Constructs a polygon geometry from an outer shell of coordinate pairs and an optional inner hole boundary.
A polygon area with holes can be interpreted as outer area minus interior ring areas:
A_{polygon} = A_{shell} - \sum_i A_{hole,i}
The function returns polygon WKT as the primary value, with area, perimeter, and centroid metadata.
Excel Usage
=SHAPELY_POLYGON(shell, holes)shell(list[list], required): List of [x, y] coordinates for the outer boundary.holes(list[list], optional, default: null): Optional list of lists of coordinates for holes. (Currently treating as single hole or needing complex parsing if multiple holes are passed from Excel 2D range).
Returns (dict): Excel Entity representing the polygon (WKT).
Example 1: Simple Triangle
Inputs:
| shell | |
|---|---|
| 0 | 0 |
| 4 | 0 |
| 0 | 3 |
Excel formula:
=SHAPELY_POLYGON({0,0;4,0;0,3})Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 4 0, 0 3, 0 0))","properties":{"Area":{"type":"Double","basicValue":6},"Perimeter":{"type":"Double","basicValue":12},"Centroid":{"type":"String","basicValue":"POINT (1.3333333333333333 1)"},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 4 0, 0 3, 0 0))"}}}
Example 2: Square with Hole
Inputs:
| shell | holes | ||
|---|---|---|---|
| 0 | 0 | 2 | 2 |
| 10 | 0 | 8 | 2 |
| 10 | 10 | 8 | 8 |
| 0 | 10 | 2 | 8 |
Excel formula:
=SHAPELY_POLYGON({0,0;10,0;10,10;0,10}, {2,2;8,2;8,8;2,8})Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))","properties":{"Area":{"type":"Double","basicValue":64},"Perimeter":{"type":"Double","basicValue":64},"Centroid":{"type":"String","basicValue":"POINT (5 5)"},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))"}}}
Example 3: Simple square polygon
Inputs:
| shell | |
|---|---|
| 0 | 0 |
| 5 | 0 |
| 5 | 5 |
| 0 | 5 |
Excel formula:
=SHAPELY_POLYGON({0,0;5,0;5,5;0,5})Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 5 0, 5 5, 0 5, 0 0))","properties":{"Area":{"type":"Double","basicValue":25},"Perimeter":{"type":"Double","basicValue":20},"Centroid":{"type":"String","basicValue":"POINT (2.5 2.5)"},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 5 0, 5 5, 0 5, 0 0))"}}}
Example 4: Pentagon shell polygon
Inputs:
| shell | |
|---|---|
| 0 | 0 |
| 3 | 0 |
| 4 | 2 |
| 2 | 4 |
| 0 | 2 |
Excel formula:
=SHAPELY_POLYGON({0,0;3,0;4,2;2,4;0,2})Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 3 0, 4 2, 2 4, 0 2, 0 0))","properties":{"Area":{"type":"Double","basicValue":11},"Perimeter":{"type":"Double","basicValue":12.8929},"Centroid":{"type":"String","basicValue":"POINT (1.8484848484848484 1.6363636363636365)"},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 3 0, 4 2, 2 4, 0 2, 0 0))"}}}
Python Code
Show Code
from shapely import Polygon
def shapely_polygon(shell, holes=None):
"""
Create a geometric polygon from a shell of points and optional holes.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.Polygon.html
This example function is provided as-is without any representation of accuracy.
Args:
shell (list[list]): List of [x, y] coordinates for the outer boundary.
holes (list[list], optional): Optional list of lists of coordinates for holes. (Currently treating as single hole or needing complex parsing if multiple holes are passed from Excel 2D range). Default is None.
Returns:
dict: Excel Entity representing the polygon (WKT).
"""
try:
# Helper to extract points from a list of lists or 2D range
def get_points(data):
pts = []
if not isinstance(data, list):
return []
for row in data:
if isinstance(row, list) and len(row) >= 2:
try:
pts.append((float(row[0]), float(row[1])))
except:
pass
return pts
clean_shell = get_points(shell)
if len(clean_shell) < 3:
return "Error: Polygon shell requires at least 3 points"
# Handle holes.
# Note: Excel passing 3D structures (list of polygons) is hard.
# We will assume 'holes' input is EITHER:
# 1. A single list of points (one hole)
# 2. None/Empty
clean_holes = []
if holes is not None:
# Ideally we'd support multiple holes, but representing list of lists of lists in Excel arguments is tricky.
# For now, let's assume 'holes' is one hole if it looks like a list of points.
one_hole = get_points(holes)
if len(one_hole) >= 3:
clean_holes.append(one_hole)
poly = Polygon(clean_shell, holes=clean_holes)
wkt_str = poly.wkt
return {
"type": "String",
"basicValue": wkt_str,
"properties": {
"Area": {"type": "Double", "basicValue": float(poly.area)},
"Perimeter": {"type": "Double", "basicValue": float(poly.length)},
"Centroid": {"type": "String", "basicValue": poly.centroid.wkt},
"WKT": {"type": "String", "basicValue": wkt_str}
}
}
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_SIMPLIFY
Simplifies an input WKT geometry using the Douglas–Peucker algorithm with a user-specified tolerance.
The tolerance controls maximum allowed geometric displacement; larger values usually reduce vertex count more aggressively.
Informally, the simplified geometry G' satisfies a bounded deviation from G under the chosen tolerance \varepsilon.
This implementation preserves topology to reduce invalid geometry artifacts during simplification.
Excel Usage
=SHAPELY_SIMPLIFY(geometry, tolerance)geometry(str, required): Input geometry (WKT).tolerance(float, required): All points in the simplified object will be within the tolerance distance of the original geometry.
Returns (dict): Excel Entity representing the simplified geometry.
Example 1: Simplify a line
Inputs:
| geometry | tolerance |
|---|---|
| LINESTRING (0 0, 1 0.1, 2 0) | 0.2 |
Excel formula:
=SHAPELY_SIMPLIFY("LINESTRING (0 0, 1 0.1, 2 0)", 0.2)Expected output:
{"type":"String","basicValue":"LINESTRING (0 0, 2 0)","properties":{"WKT":{"type":"String","basicValue":"LINESTRING (0 0, 2 0)"}}}
Example 2: Simplify a polyline
Inputs:
| geometry | tolerance |
|---|---|
| LINESTRING (0 0, 1 0.2, 2 0.1, 3 0) | 0.15 |
Excel formula:
=SHAPELY_SIMPLIFY("LINESTRING (0 0, 1 0.2, 2 0.1, 3 0)", 0.15)Expected output:
{"type":"String","basicValue":"LINESTRING (0 0, 1 0.2, 3 0)","properties":{"WKT":{"type":"String","basicValue":"LINESTRING (0 0, 1 0.2, 3 0)"}}}
Example 3: Simplify a polygon
Inputs:
| geometry | tolerance |
|---|---|
| POLYGON ((0 0, 2 0, 2 1, 1.2 1.2, 2 2, 0 2, 0 0)) | 0.3 |
Excel formula:
=SHAPELY_SIMPLIFY("POLYGON ((0 0, 2 0, 2 1, 1.2 1.2, 2 2, 0 2, 0 0))", 0.3)Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 2 0, 2 1, 1.2 1.2, 2 2, 0 2, 0 0))","properties":{"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 2 0, 2 1, 1.2 1.2, 2 2, 0 2, 0 0))"}}}
Example 4: Simplify a multipoint geometry
Inputs:
| geometry | tolerance |
|---|---|
| MULTIPOINT (0 0, 1 1, 2 2) | 0.5 |
Excel formula:
=SHAPELY_SIMPLIFY("MULTIPOINT (0 0, 1 1, 2 2)", 0.5)Expected output:
{"type":"String","basicValue":"MULTIPOINT ((0 0), (1 1), (2 2))","properties":{"WKT":{"type":"String","basicValue":"MULTIPOINT ((0 0), (1 1), (2 2))"}}}
Python Code
Show Code
from shapely import wkt
def shapely_simplify(geometry, tolerance):
"""
Returns a simplified representation of the geometric object.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.simplify.html
This example function is provided as-is without any representation of accuracy.
Args:
geometry (str): Input geometry (WKT).
tolerance (float): All points in the simplified object will be within the tolerance distance of the original geometry.
Returns:
dict: Excel Entity representing the simplified geometry.
"""
try:
if not geometry:
return "Error: Geometry required"
geom = wkt.loads(geometry)
simplified = geom.simplify(tolerance, preserve_topology=True)
wkt_str = simplified.wkt
return {
"type": "String",
"basicValue": wkt_str,
"properties": {
"WKT": {"type": "String", "basicValue": wkt_str}
}
}
except Exception as e:
return f"Error: {str(e)}"Online Calculator
SHAPELY_UNION_ALL
Computes the geometric union of multiple WKT geometries, combining overlapping or adjacent regions into a single resulting geometry where applicable.
The operation is the set union across all input geometries:
G = \bigcup_{i=1}^{n} G_i
The function returns an Excel data type whose primary value is the union WKT, plus area and boundary length metadata.
Excel Usage
=SHAPELY_UNION_ALL(geometries)geometries(list[list], required): List of WKT strings.
Returns (dict): Excel Entity representing the unioned geometry.
Example 1: Union of two squares
Inputs:
| geometries |
|---|
| POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0)),POLYGON ((1 0, 2 0, 2 1, 1 1, 1 0)) |
Excel formula:
=SHAPELY_UNION_ALL(POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0)),POLYGON ((1 0, 2 0, 2 1, 1 1, 1 0)))Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 0 1, 1 1, 2 1, 2 0, 1 0, 0 0))","properties":{"Area":{"type":"Double","basicValue":2},"Length":{"type":"Double","basicValue":6},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 0 1, 1 1, 2 1, 2 0, 1 0, 0 0))"}}}
Example 2: Union of overlapping polygons
Inputs:
| geometries |
|---|
| POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0)),POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1)) |
Excel formula:
=SHAPELY_UNION_ALL(POLYGON ((0 0, 2 0, 2 2, 0 2, 0 0)),POLYGON ((1 1, 3 1, 3 3, 1 3, 1 1)))Expected output:
{"type":"String","basicValue":"POLYGON ((2 0, 0 0, 0 2, 1 2, 1 3, 3 3, 3 1, 2 1, 2 0))","properties":{"Area":{"type":"Double","basicValue":7},"Length":{"type":"Double","basicValue":12},"WKT":{"type":"String","basicValue":"POLYGON ((2 0, 0 0, 0 2, 1 2, 1 3, 3 3, 3 1, 2 1, 2 0))"}}}
Example 3: Union of points
Inputs:
| geometries |
|---|
| POINT (0 0),POINT (1 1),POINT (2 2) |
Excel formula:
=SHAPELY_UNION_ALL(POINT (0 0),POINT (1 1),POINT (2 2))Expected output:
{"type":"String","basicValue":"MULTIPOINT ((0 0), (1 1), (2 2))","properties":{"Area":{"type":"Double","basicValue":0},"Length":{"type":"Double","basicValue":0},"WKT":{"type":"String","basicValue":"MULTIPOINT ((0 0), (1 1), (2 2))"}}}
Example 4: Union of lines
Inputs:
| geometries |
|---|
| LINESTRING (0 0, 2 0),LINESTRING (2 0, 4 0) |
Excel formula:
=SHAPELY_UNION_ALL(LINESTRING (0 0, 2 0),LINESTRING (2 0, 4 0))Expected output:
{"type":"String","basicValue":"MULTILINESTRING ((0 0, 2 0), (2 0, 4 0))","properties":{"Area":{"type":"Double","basicValue":0},"Length":{"type":"Double","basicValue":4},"WKT":{"type":"String","basicValue":"MULTILINESTRING ((0 0, 2 0), (2 0, 4 0))"}}}
Python Code
Show Code
from shapely import wkt
from shapely.ops import unary_union
def shapely_union_all(geometries):
"""
Returns the union of all geometries in the input list.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.unary_union.html
This example function is provided as-is without any representation of accuracy.
Args:
geometries (list[list]): List of WKT strings.
Returns:
dict: Excel Entity representing the unioned geometry.
"""
try:
# Normalize input list
def flatten(x):
if isinstance(x, list):
out = []
for item in x:
out.extend(flatten(item))
return out
return [x]
raw_list = flatten(geometries)
wkt_list = [str(s) for s in raw_list if s]
if not wkt_list:
return "Error: No geometries provided"
geoms = []
for s in wkt_list:
try:
geoms.append(wkt.loads(s))
except:
pass # Skip invalid WKT
if not geoms:
return "Error: No valid geometries found"
union_geom = unary_union(geoms)
wkt_str = union_geom.wkt
return {
"type": "String",
"basicValue": wkt_str,
"properties": {
"Area": {"type": "Double", "basicValue": float(union_geom.area)},
"Length": {"type": "Double", "basicValue": float(union_geom.length)},
"WKT": {"type": "String", "basicValue": wkt_str}
}
}
except Exception as e:
return f"Error: {str(e)}"Online Calculator