Subscribe to this thread
Home - General / All posts - JSON to Drawing
grmapper
245 post(s)
#04-Jan-22 15:16

Hello,

I have table I believe was imported from a JSON file. I have two records that clearly have the information I want but now I need to reformat them to something that I can create a drawing from.

I suspect the use of a transform to break each record up into parts including the LAT and LONG but not sure of the reformatting process. If anyone can point me to first steps or an example video I'd appreciate it.

Here is an example of what went into MF9:

{"MMSI":224738000,"TIMESTAMP":"2019-10-03 20:54:54 UTC","LATITUDE":43.47953,"LONGITUDE":-8.25235,"COURSE":246.3,"SPEED":0.1,"HEADING":169,"NAVSTAT":5,"IMO":9237694,"NAME":"BOAT1","CALLSIGN":"AAAA","TYPE":99,"A":25,"B":49,"C":6,"D":10,"DRAUGHT":4.4,"DESTINATION":"ES FRO","LOCODE":"ESFRO","ETA_AIS":"12-17 17:15","ETA":"2019-12-17 17:15:00","SRC":"TER","ZONE":"North East Atlantic Ocean","ECA":false}

I have attached my .map file which has the data loaded as a table.

Best,

GRMapper

Attachments:
BoatMap.map

Mike Pelletier

2,122 post(s)
#04-Jan-22 16:01

Looks like you could export it to a .csv and then import the .csv to do an initial parsing of the data. Add an identity field in the Schema dialogue to make it editable. Then use the transform tool and use reduce, trim, replace, etc. as needed.

oeaulong

521 post(s)
#04-Jan-22 16:02

Check out the Copy transform for Text fields. With this you can step through and extract individual json named values like the lat and long from the json string field. I was able to extract the LATITUDE and LONGITUDE values in a matter of seconds. The results are stored in a varchar or nvarchar field type, so once completed another round of transformation into proper data value types would be needed.

https://manifold.net/doc/mfd9/transform_-_text__copy.htm

grmapper
245 post(s)
#04-Jan-22 16:11

Thanks to you both for getting me started. Much appreciated.

mlinth
447 post(s)
#04-Jan-22 18:14

This is easy to do in SQL. Here's an example query - worth reading the fine manual on the StringJson functions...

M

select 

StringJsonValue(AIS,'MMSI',falseas mmsi,

StringJsonNumber(AIS,'LONGITUDE',falseas longitude,

StringJsonNumber(AIS,'LATITUDE',falseas latitude,

GeomMakePoint(VectorMakeX2(StringJsonNumber(AIS,'LONGITUDE',false), StringJsonNumber(AIS,'LATITUDE',false) )) as [geom]

 from

 Points;

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.