Azure / Azure/data-api-builder
[Enh]: Support `Geospatial` data type in MSSQL
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 370
- Avg merge
- 3d 17h
- Merged PRs (30d)
- 8
Description
# Geospatial data types
In SQL Server and Azure SQL Database, geospatial data is represented using the `geometry` and `geography` data types. These types store spatial data such as points, lines, and polygons, and support rich spatial operations like distance, containment, and intersection.
```sql
CREATE TABLE locations (
id INT PRIMARY KEY,
position GEOGRAPHY -- a latitude/longitude point
)
```
* `GEOGRAPHY` is used for Earth-based (ellipsoidal) coordinates (e.g., GPS).
* `GEOMETRY` is used for flat, projected coordinate systems (e.g., CAD/GIS).
## FOR JSON support
When used with `FOR JSON`, spatial columns are **serialized as strings** in SQL Server.
```sql
SELECT id, position FROM locations FOR JSON AUTO;
```
Returns:
```json
[
{
"id": 1,
"position": "POINT(-104.9903 39.7392)"
}
]
```
The output is a well-known text (WKT) representation of the spatial data.
## Inserting geospatial data
```sql
INSERT INTO locations (id, position)
VALUES (
1,
geography::STPointFromText('POINT(-104.9903 39.7392)', 4326)
);
```
* The `STPointFromText` method accepts WKT format and a spatial reference ID (SRID).
* `4326` is the most common SRID, representing GPS coordinates (WGS 84).
## Data API builder behavior
Data API builder (DAB) should treat geospatial columns as **WKT strings** for both reading and writing.
### Query operations
DAB will expose `GEOGRAPHY` or `GEOMETRY` values as WKT strings in REST responses:
```json
{
"value": [
{
"id": 1,
"position": "POINT(-104.9903 39.7392)"
}
]
}
```
### Mutation operations
When creating or updating rows, input should be a WKT string that SQL Server can parse:
```http
POST /locations
Content-Type: application/json
{
"id": 2,
"position": "POINT(-122.4194 37.7749)"
}
```
Internally, DAB should convert this into a call to `geography::STPointFromText(...)` with SRID `4326`.
### Geospatial Considerations
1. DAB must validate or wrap WKT strings using `STGeomFromText` or `STPointFromText`.
2. All values must include a valid SRID, typically `4326` for GPS.
3. DAB does not parse or visualize spatial data—clients are responsible for rendering.
Contributor guide
Assessment
This issue has not been assessed yet.