-1

i have a set of data that needs to be in linestring only before being converted to geojson, tried using st_dump to convert the st_makeline portion of the code which works fine but then i am having an issue converting it back to geojson, here is the code " limited to select only on this dataset"

select geo from ( 
SELECT
routeID ,  json_extract(st_asgeojson(st_makeline(array_agg(st_geogpoint(locs.lon, 
locs.lat) order by locs.date))),'$.coordinates' )as geo,
FROM 
howardcounty.routebatches
cross join UNNEST(locations) as locs
where  locs.date between {{start_date}} and {{end_date}} 
group by routeID 
order by routeID
limit 100
)where length(geo) -length(replace(geo,"[","")) > 1+2

this is the error when inserting st_dump

json_extract(st_asgeojson(st_dump(st_makeline(array_agg(st_geogpoint(locs.lon, locs.lat) order by locs.date)))),'$.coordinates' )as geo,

st_dump_error

before json_extract before json extract after json_extract json extract from geojson

AlphaOmega
  • 59
  • 7

2 Answers2

0

ST_Dump returns an array of GEOGRAPHY objects, while ST_AsGeoJson converts a single GEOGRAPHY to string. If the desired output shape is for each line in the multilinestring to be on a separate row, the query needs to flatten that array using UNNEST, similar to how locations are UNNEST'ed here.

Sample query

with sample_data as (
    select 1 id, st_geogfromtext('multilinestring((1 2, 3 4), (5 6, 7 8))') lines
)
select id, line
from sample_data d, unnest(st_dump(d.lines)) line

Result:

id  line
1   "LINESTRING(1 2, 3 4)"
1   "LINESTRING(5 6, 7 8)"
Michael Entin
  • 7,189
  • 3
  • 21
  • 26
  • I am using the deck.gl path chart in superset, multiline strings have a format that is unrecognized so i need to have it changed to linestring, or anyother method that would work, have included a screen shot of what the results are from the above query, the red arrow is a multiline string which is causing an issue – AlphaOmega Aug 03 '21 at 20:05
  • Linestrings and multilinestrings are different types, it is not possible to "change" a multilinestring to a linestring. What you can do is represent a multilinestring as several linestrings (see my answer above) and draw each linestring separately. – Michael Entin Aug 04 '21 at 06:12
  • Ok, i tried unnest but getting errors, would you be able to show me in code how to unnest the st_dump and then convert that to geojson? like i said before i am limited to select only – AlphaOmega Aug 04 '21 at 13:22
  • Added sample query, hope it helps – Michael Entin Apr 03 '22 at 10:34
0

Although it isn't clear from the documentation, the flatten function in the PD Turfjs library does a very good job of this. Pass any "multi" class to it and it will simplify the array structure. It returns a feature collection with the modified record as a member. Example of application here:

   'var pobject=JSON.parse(rec);
//
// flatten and stringify if MultiLineString
//
   if (pobject.geometry.type=="MultiLineString")
   {
      var tmpobj=turf.flatten(pobject);
      rec=JSON.stringify(tmpobj.features[0]);   
   }'
ed granzow
  • 1