I have a multipart M-enabled SDE.ST_GEOMETRY polyline FC (in an Oracle 18c EGDB):
Using SQL, I want to select the geometry as JSON text:
{"hasM":true,"paths":[[[0,5,0],[10,10,11.179999999993015],[30,0,33.539999999993597]],[[50,10,33.539999999993597],[60,10,43.539999999993597]]],"spatialReference":{"wkid":26917,"latestWkid":26917}}
--Source: https://gis.stackexchange.com/questions/423161/field-calculator-to-get-json-text
Purpose: Integrating to a remote system (options are limited).
Here's what I have so far:
select
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(shape_text
, '\(\s*\(\s*', '[[[')
, '\s*\)\s*\)', ']]]')
, '\s*\)\s*,\s*\(\s*', '],[')
, '\s*,\s*', '],[')
, '\s+', ',') as json
from
(select
replace(sde.st_astext(shape),'MULTILINESTRING M ') shape_text
from
my_lines)
Output:
[[[0.0,5.0,0.0],[10.0,10.0,11.17999999999302],[30.0,0.0,33.5399999999936]],[[50.0,10.0,33.5399999999936],[60.0,10.0,43.5399999999936]]]
Alternative option:
- https://dbfiddle.uk/?rdbms=oracle_21&fiddle=18728ae9959c08d18b645e9751448abf
- Source: https://stackoverflow.com/a/72060487/5576771
To be honest, the above attempt is getting pretty ugly. Is there a better way to convert ST_GEOMETRY to JSON using SQL?
Related:
