Save table that contains MultiLineString features to GeoJSON file

[jjcfrancisco]

This is connected to this previous issue.

I have a table in which many rows contains geometries that are MultiLineString. When saving such table to a GeoJSON file, the resulting file does not follow OGC standard and instead creates a new member inside properties called geom:

{
    "type": "Feature",
    "properties": {
        "name": "Willoughby Avenue",
        "geom": "MULTILINESTRING ((-73.97354 40.691707, -73.973465 40.691704900000005, -73.9726547 40.6916828, -73.972553 40.691680000000005, -73.97246600000001 40.6916765, -73.9716568 40.6916439, -73.97156000000001 40.69164, -73.9714699 40.6916366, -73.9706544 40.691606, -73.970549 40.691602, -73.97044410000001 40.6915981, -73.96962880000001 40.691568000000004, -73.969521 40.691564, -73.96942920000001 40.6915623, -73.9694039 40.6915618, -73.9691207 40.691556600000006, -73.96860980000001 40.6915472, -73.96849300000001 40.691545000000005, -73.9683997 40.6915545, -73.9676135 40.6916346, -73.96753100000001 40.691643, -73.9674554 40.691651900000004, -73.9666735 40.691742000000005, -73.966566 40.691756000000005, -73.96647540000001 40.6917654, -73.9660829 40.691813, -73.9656904 40.691860500000004, -73.96560000000001 40.691871, -73.9655029 40.6918827, -73.96484380000001 40.6919618, -73.96467460000001 40.6919818, -73.96444530000001 40.6920085, -73.9639095 40.692070400000006, -73.963749 40.692089, -73.9634781 40.692123200000005, -73.9630022 40.6921837, -73.9628172 40.6922032, -73.96221820000001 40.6922663, -73.9620141 40.692287900000004, -73.961899 40.6923, -73.9618232 40.692308100000005, -73.9608238 40.6924148, -73.960738 40.692424, -73.96065870000001 40.692433400000006, -73.9599113 40.692522000000004, -73.959844 40.692530000000005, -73.9597753 40.692537900000005, -73.9590781 40.692618200000005, -73.958993 40.692628, -73.95891370000001 40.692637100000006, -73.9577745 40.6927682, -73.95768100000001 40.692779, -73.9575958 40.6927888, -73.95684750000001 40.692875400000005, -73.956773 40.692884, -73.95670720000001 40.6928916, -73.9559275 40.6929804, -73.955826 40.692993, -73.95572080000001 40.693004300000005, -73.9549571 40.693093100000006, -73.95488 40.693102, -73.9548152 40.693109500000006, -73.9545435 40.693141000000004, -73.9540642 40.693196300000004, -73.953989 40.693205000000006, -73.9539268 40.693212200000005, -73.953547 40.6932561, -73.9531738 40.693299200000006, -73.95309800000001 40.693308, -73.9530388 40.693315000000005, -73.9523358 40.693398800000004, -73.9522761 40.6934059, -73.9521857 40.6934649, -73.9520943 40.6935245, -73.95207040000001 40.6935401, -73.9490184 40.6938929, -73.948914 40.693905, -73.94882240000001 40.6939161, -73.94617910000001 40.694218400000004, -73.946078 40.694230000000005, -73.9459932 40.694239800000005, -73.94334330000001 40.694547, -73.94323440000001 40.694559000000005, -73.94315830000001 40.694568600000004, -73.9405164 40.694869000000004, -73.9404133 40.694880700000006, -73.9402967 40.6948961, -73.9375809 40.695207800000006, -73.93749030000001 40.6952183, -73.93739430000001 40.6952293, -73.9346498 40.6955435, -73.9345667 40.695553000000004, -73.934476 40.695564000000005, -73.93326640000001 40.6957104, -73.9332313 40.695729, -73.9331377 40.6957953, -73.93305840000001 40.6958526, -73.93193020000001 40.697018500000006, -73.9318458 40.6970814, -73.9317676 40.697138800000005, -73.931537 40.6972833, -73.9314554 40.697335200000005, -73.9313132 40.697422800000005), (-73.927797 40.699585, -73.9278654 40.699542900000004, -73.92990400000001 40.698287, -73.929964 40.69825, -73.93003200000001 40.6982081, -73.9310956 40.6975532, -73.931137 40.6975101, -73.9311498 40.697440900000004), (-73.924636 40.702388, -73.9246961 40.702327100000005, -73.9259961 40.7009988, -73.926024 40.7009703, -73.926083 40.70091, -73.9261359 40.700856300000005, -73.9274424 40.6995299, -73.92747630000001 40.699495500000005, -73.927532 40.699439000000005), (-73.916002 40.710967000000004, -73.91605220000001 40.710917900000005, -73.9160954 40.7108756, -73.91705590000001 40.709935300000005, -73.9170871 40.709904800000004, -73.917142 40.709851, -73.91719810000001 40.7097964, -73.9172262 40.7097691, -73.9175388 40.7094648, -73.918073 40.708945, -73.9183481 40.7086746, -73.91913050000001 40.707905800000006, -73.919167 40.7078696, -73.9192201 40.7078177, -73.91927150000001 40.7077673, -73.92000970000001 40.7070458, -73.9202217 40.706836200000005, -73.9202581 40.706800300000005, -73.9203148 40.7067442, -73.92036370000001 40.7066962, -73.92132430000001 40.7057541, -73.92135350000001 40.705725400000006, -73.92141000000001 40.705670000000005, -73.921465 40.705614000000004, -73.92298670000001 40.704065400000005, -73.92302500000001 40.7040257, -73.9230792 40.7039695))"
    },
    "geometry": null
},

I've used this:

COPY st_linemerge_table TO 'merged_geom.geojson' WITH (FORMAT 'GDAL', DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_NAME=NO');

For clarity, I am wondering if it is possible to end with a GeoJSON where its features as like this:

  {
   "type": "Feature",
   "geometry": {
       "type": "MultiLineString",
       "coordinates": [
           [
               [170.0, 45.0], [180.0, 45.0]
           ], [
               [-180.0, 45.0], [-170.0, 45.0]
           ]
       ]
   },
   "properties": {
    "hello": "world",
   }
  }

[Maxxen]

Hi! Thanks for reporting this issue! This definitely looks like a bug to me. I'll have a look.

Are you didn't accidentally create VARCHAR's out of your linestrings (with ST_AsText)? What is the output of:

DESCRIBE st_linemerge_table;

??