Using the InlineQuerier as a replacement for multiple FeatureMergers

Introduction

This article shows how a workspace that uses multiple FeatureMergers to analyze data can be streamlined by using an InlineQuerier to extend the number of source files available and the many queries which can be made against that data.

Examples

Example 1: Using Multiple FeatureMergers

Download the FeatureMergerExample.fmwt workspace from the Files section located on the right-hand side of the article. 

This workspace is used to calculate Average Property values for all properties based on Address. It merges data from the PropertyTax Excel Spreadsheet with a File Geodatabase feature class of address points. Merging the data and passing it through the StatisticsCalculator results in the required values. Then this merged data is further linked to a point shapefile of HeritageSites to refine the information to just the Average Property Value of the Heritage Sites. Again a StatisticsCalculator is used and the output logged.

This reading, merging, calculating process is often repeated a number of times on disparate datasets to arrive at a set of conclusions.

Example 2: Using an InlineQuerier 

Download the InlineQuerierExample.fmwt workspace from the Files section located on the right-hand side of the article. 

This workspace produces the same output as Example 1, however in a much more efficient manner. Here we have loaded the three source datasets into the InlineQuerier transformer and executed the following SQL statements on the data. Each SQL Statement output is directed through an appropriately named output port.

SQL Examples:

Average Property Values Vancouver

select avg(CURRENT_LAND_VALUE) from PropertyTax

Average Property Values for valid Addresses

select avg(CURRENT_LAND_VALUE) from PropertyTax where PID in 
	(select PID from AddressData)

Number of Properties in Average Tax Calculation

select count(*) from PropertyTax where PID in (select PID from AddressData)

Average Value of all Heritage Listings

SELECT AVG(CURRENT_LAND_VALUE) FROM PropertyTax P
	JOIN HeritageSites H ON A.FullAddress=H.ADDRESS
	JOIN AddressData A ON P.PID=A.PID
	WHERE H.ADDRESS IS NOT NULL
	

Property Information for Heritage Sites

SELECT P.*, H.ADDRESS, H.DESCRIPT, A.NeighborhoodCode
	FROM PropertyTax P
	JOIN HeritageSites H ON A.FullAddress=H.ADDRESS
	JOIN AddressData A ON P.PID=A.PID
	WHERE H.ADDRESS IS NOT NULL

Exercise 3:  Additional Query for Higher Valued Locations (optional)

Download the InlineQuerierExample-Optional.fmwt workspace from the Files section located on the right-hand side of the article. 

Another query was added to the InlineQuerier to merge the PropertyTax data with the AddressData table. The results include the AddressData point geometry, and the CURRENT_LAND_VALUE is sorted in descending value so that we can determine the location of the higher valued properties.
 

SQL Examples:

PropertyLocations

SELECT * FROM AddressData A
JOIN PropertyTax P ON A.PID=P.PID
ORDER BY CAST(CURRENT_LAND_VALUE as int) desc

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.