Azure / Azure/data-api-builder
[Enh]: Support `Geospatial` data type in MSSQL
- Lenguaje dominante
- C#
- Estrellas
- 1.5k
- Forks
- 370
- Merge medio
- 3 d 22 h
- PR fusionados (30 d)
- 9
Descripción
# 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.
Guía de contribución
Línea de trabajo
No se nombran archivos de implementación ni pruebas. Empieza por localizar el manejo de tipos de SQL Server de DAB y las rutas de mutación y respuesta de REST; después, verifica cómo se representan los tipos de base de datos existentes. Se considera terminado cuando las columnas GEOGRAPHY y GEOMETRY se exponen como cadenas WKT en las lecturas y se aceptan en las escrituras, con cobertura para el comportamiento documentado de SQL Server.
Escrito por el modelo de indexación a partir del texto del issue.
Evaluación
- Stack tecnológico
- azure, sql
- Área
- api, databases
- Tipo de issue
- Nueva funcionalidad
- Dificultad
- 4/5
- Tiempo estimado
- 3-5 días
- Estado de actividad
- Estancado
- Claridad
- Bastante claro
- Aptitud para principiantes
- 45/100