2

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.