Kategorien:: Geodatenfunktionen

ST_ASGEOJSON¶

Given a value of type GEOGRAPHY or GEOMETRY, return the GeoJSON representation of that value.

Syntax¶

ST_ASGEOJSON( <geography_or_geometry_expression> )

Argumente¶

geography_or_geometry_expression: The argument must be an expression of type GEOGRAPHY or GEOMETRY.

Rückgabewerte¶

Ein OBJECT im GeoJSON-Format.

Usage Notes¶

For GEOMETRY objects:

The returned GEOMETRY object uses the same coordinate system as the input GEOMETRY object.

Note that the GeoJSON specification requires that geometry be in the WGS84 coordinate system (SRID = 4326). However, the ST_ASGEOJSON function does not enforce this.
The function does not add the SRID or any other CRS information to the output.

Beispiele¶

GEOGRAPHY Examples¶

Das folgende Beispiel veranschaulicht die Funktion ST_ASGEOJSON:

create table geospatial_table (id INTEGER, g GEOGRAPHY);
insert into geospatial_table values
    (1, 'POINT(-122.35 37.55)'), (2, 'LINESTRING(-124.20 42.00, -120.01 41.99)');

select st_asgeojson(g)
    from geospatial_table
    order by id;
+------------------------+
| ST_ASGEOJSON(G)        |
|------------------------|
| {                      |
|   "coordinates": [     |
|     -122.35,           |
|     37.55              |
|   ],                   |
|   "type": "Point"      |
| }                      |
| {                      |
|   "coordinates": [     |
|     [                  |
|       -124.2,          |
|       42               |
|     ],                 |
|     [                  |
|       -120.01,         |
|       41.99            |
|     ]                  |
|   ],                   |
|   "type": "LineString" |
| }                      |
+------------------------+

Das Umwandeln der VARIANTAusgabe in VARCHAR führt zu folgenden Ergebnissen:

select st_asgeojson(g)::varchar
    from geospatial_table
    order by id;
+-------------------------------------------------------------------+
| ST_ASGEOJSON(G)::VARCHAR                                          |
|-------------------------------------------------------------------|
| {"coordinates":[-122.35,37.55],"type":"Point"}                    |
| {"coordinates":[[-124.2,42],[-120.01,41.99]],"type":"LineString"} |
+-------------------------------------------------------------------+

GEOMETRY Examples¶

The following example demonstrates the ST_ASGEOJSON function with a GEOMETRY object as input:

SELECT ST_ASGEOJSON(TO_GEOMETRY('SRID=4326;LINESTRING(389866 5819003, 390000 5830000)')) AS geojson;

+------------------------+
| GEOJSON                |
|------------------------|
|{                       |
|  "coordinates": [      |
|    [                   |
|      389866,           |
|      5819003           |
|    ],                  |
|    [                   |
|      390000,           |
|      5830000           |
|    ]                   |
|  ],                    |
|  "type": "LineString"  |
|}                       |
+------------------------+