Querying datasets with "block ranges"

Some datasets, like this Code Enforcement - Building and Property Violations dataset from the City of Boston, represent data that can be either at a specific address or within a block of addresses. This dataset has three fields that are used for the address:

• stno, for either the exact street address or the start of the range
• sthigh, for the upper end of a “block range” when set
• street, for the street name

To query this dataset, we want to:

• Match the exact street address if there’s only stno
• Match the range between stno and sthigh if there is a range

With SoQL, this is fairly straightforward. By using the boolean AND and OR operators, we can set up a $where query like street = '$street_name' AND (stno = '$street_name' OR (stno <= '$street_name' AND sthigh >= '$street_name')) (where $street_name and $street_name represent our input street name and number).

For example, here’s the query for “228 Market”:

https://data.cityofboston.gov/resource/8sq6-p7et.json?$where=street = 'Market' AND (stno = '228' OR (stno <= '228' AND sthigh >= '228'))