Azure / Azure/data-api-builder

[Enh]: Support `Geospatial` data type in MSSQL

Aperta
#3,219 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub
2.x mssql needs discussion
Lingua principale
C#
Stelle
1.5k
Fork
370
Merge medio
3g 17h
PR unite (30g)
8

Descrizione

# 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.

Guida per i contributori

Apri la guida per i contributori

Direzione di ricerca

Non vengono indicati file di implementazione o test. Inizia individuando la gestione dei tipi SQL Server di DAB e i percorsi REST per mutazioni e risposte, quindi verifica come vengono rappresentati i tipi di database esistenti. Il lavoro è completato quando le colonne GEOGRAPHY e GEOMETRY vengono esposte come stringhe WKT per le letture e accettate per le scritture, con una copertura del comportamento documentato di SQL Server.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Valutazione

Stack tecnologico
azure, sql
Ambito
api, databases
Tipo di issue
Funzionalità
Difficoltà
4/5
Tempo stimato
3-5 giorni
Stato di attività
Ferma
Chiarezza
Abbastanza chiara
Idoneità per principianti
45/100

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.