Extracting Location from JSON

Introduction

This article shows how to create point geometry from x, y coordinates extracted from JSON data. You will create a workspace that reads JSON data from the web, extracts coordinate data, and uses them to create a KML file in Google Earth with customized symbology.

Note: This article covers creating geometry from JSON location (x, y coordinates) data. If you have lines or polygons stored in JSON following GeoJSON or WKT format, you can extract those using a FeatureReader with GeoJSON or WKT format, or use the JSON reader Geometry Format and Geometry Key Name parameters, which let you extract GeoJSON or WKT.

Step-by-Step Instructions

1. Inspect Source Data in a Text Editor
This article uses an open dataset from the City of Vancouver, Rental Standards Current Issues, which is updated daily with a list of rental buildings with unresolved by-law issues. Download the source data and view it using a text editor like Notepad++. You will see non-minified (i.e. whitespace removed) JSON with entries that look something like this:

[{"datasetid": "rental-standards-current-issues", "recordid": "3aedcb4f13a34a3d75eae87999a925572c6edae6", "fields": {"totalunits": 60, "businessoperator": "0707892 BC Ltd", "detailurl": "http://app.vancouver.ca/RPS_Net/Default.aspx?num=320&street=ABBOTT%20STREET", "street": "ABBOTT STREET", "geom": {"type": "Point", "coordinates": [-123.10664756, 49.28261413]}, "streetnumber": "320", "totaloutstanding": 3, "geo_local_area": "Downtown"}, "record_timestamp": "2019-10-11T10:31:43.770Z"},{"datasetid": "rental-standards-current-issues", "recordid": "88ae3b9867f99c4319f3f43ccae6fece39301b19", "fields": {"totalunits": 71, "businessoperator": "Central City Foundation", "detailurl": "http://app.vancouver.ca/RPS_Net/Default.aspx?num=404&street=ABBOTT%20STREET", "street": "ABBOTT STREET", "geom": {"type": "Point", "coordinates": [-123.10713559, 49.2816849]}, "streetnumber": "404", "totaloutstanding": 1, "geo_local_area": "Downtown"}, "record_timestamp": "2019-10-11T10:31:43.770Z"}, … ]

You will notice that the JSON is nested in such a way we will have to extract the coordinates to store them as FME attributes.

Note: To see the options for reading JSON in FME, see Reading JSON.

2. Specify Reader Dataset

Open FME Workbench and click on Generate Workspace to create a new workspace. For Reader Format, select JSON (JavaScript Object Notation). For Dataset, navigate to the downloaded RentalStandardsCurrentIssues.json file or copy and paste the URL:

https://opendata.vancouver.ca/explore/dataset/rental-standards-current-issues/download/?format=json

3. Set Parameters for the Reader

Click the Reader Parameters button and confirm that the Schema Scan Mode is set to Auto. In this case, the nesting of our JSON means that Auto mode is sufficient (see Reading JSON for more details). Then click OK to close the parameters.

4. Set Writer Parameters

Select OGC / Google KML as the Writer Format and choose a destination for the output Dataset. Name the KML file RentalStandardsIssues.kml, then click OK to generate the workspace.

5. Inspect JSON in FME Workbench

Run your workspace with Feature Caching enabled. Once it has run successfully, select the reader feature type to inspect it using Visual Preview. You will notice the reader’s auto mode did not expose latitude and longitude attributes. If you select any row in the table and look in the Feature Information Window, you’ll see our longitude and latitude values are stored in fields.geom.coordinates{0} and fields.geom.coordinates{1}, respectively.
 

These attributes were not exposed because they are not at the top level of the JSON hierarchy.

We could expose them by using an AttributeExposer, but an AttributeCreator would be better because we can use it to both expose the attributes and rename them.

6. Add an AttributeCreator

Remove the connection line between the reader and writer feature type. Add an AttributeCreator transformer after the reader feature type. Create one new attribute called longitude and set its Attribute Value to fields.geom.coordinates{0} and another attribute called latitude and set its Attribute Value to fields.geom.coordinates{1}.

7. Add a VertexCreator

We have value for our coordinates now, but we still need to create geometry from them. Add a VertexCreator after the AttributeCreator. In the VertexCreator parameters, set the X Value to the attribute longitude and the Y Value to the attribute latitude.

Note: Depending on the state of the live JSON data, there might be features without coordinate values. If your workspace does not run successfully because of a feature in the VertexCreator’s <Rejected> port, this is likely the cause. You can either change your workspace parameter for Rejected Feature Handling or add Logger  transformer to the <Rejected> port to allow the translation to continue.

8. Add a CoordinateSystemSetter

Add a CoordinateSystemSetter to the canvas and connect it to the VertexCreator output port. Set the parameters of the Coordinate System to LL84.

9. Create issuepercentage Attribute

Open the AttributeCreator parameters again. We want to create a new attribute that will calculate the percentage of units at each location that have by-law issues. Under New Attribute, add issuepercentage attribute. Click in the Attribute Value field, and click on the arrow to select the drop-down list item ”Open Arithmetic Editor”. Double click on the FME Feature Attributes and enter the other characters to create:

@Value(fields.totaloutstanding)/@Value(fields.totalunits)*100

Note: If you create this equation in the TextEditor, it will show the literal values instread of the output of the equation. 

10. Add a KMLPropertySetter

Add a KMLPropertySetter and connect it after the CoordinateSystemSetter. In the parameters, under Navigation Tree, set Name to fields.totalunits. Now the KML will show the total number of rental units as the label for each point.

11. Add a KMLStyler

Add a KMLStyler. We will use this to style our points differently based on the value of issuepercentage. Expand the Icon section and set Scale to 0.5.

Then for Name, click the drop-down and select Conditional Value. Double-click in the first field under Test Condition.

• Left Value: issuepercentage

• Operator: <=

• Right Value: 5

• Name: C1

Click OK.
 

We’ve set up one range. Now double-click in the field next to Else If. Set the follow:

• Left Value: issuepercentage

• Operator: <=

• Right Value: 10

• Name: E2

Click OK.
 

For the last range, we can just set Name under the Else row to H1. 

Click OK twice to close the KML Styler.


12. Set Writer Definition to Automatic.
Connect the KMLStyler to the writer feature type. Open the writer feature type parameters, navigate to the User Attributes KML Writer, navigate to User Attributes tab, change the Attribute Definition to Automatic, this will remove any of the KML attributes that were automatically created when we generated the workspace.

Then, save and run your workspace.

13. Display the Classified Records in Google Earth

Right-click the writer feature type and select “Open containing folder” to find the KML file RentalStandardsIssues.kml. Open the KML file in Google Earth Pro. You will see the point features with number of units and color symbology displayed. The records will appear color-coded according to our symbology that we are setting in the KMLStyler.

Google Earth view of the current rental issues in Vancouver displaying the number of units color-coded according to the percent of units with by-law issues: green has 0 to 5, yellow has 5.01 to 10, and red has 10.01 to 100.

This article demonstrated one way to extract coordinates and create geometry from JSON data.

For more information on working with KML data in FME, see the Tutorial: KML Transformations article series.
  

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.