0

I have a multipart M-enabled SDE.ST_GEOMETRY polyline FC (in an Oracle 18c EGDB):

enter image description here


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:


To be honest, the above attempt is getting pretty ugly. Is there a better way to convert ST_GEOMETRY to JSON using SQL?

Related:

1
  • You might be over-thinking this. ArcGIS Server will return JSON in a feature service from a feature class.
    – Vince
    Apr 29, 2022 at 19:42

1 Answer 1

0

I suppose one option would be to populate a text column with JSON using an ArcGIS attribute rule. And then simply select that column using SQL.

The problem with that option is: Not all of our users have switched from ArcMap to ArcGIS Pro. So attribute rules are still off limits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.