cambridgema-gov/land-use-6erf-wujk
Icon for Socrata external plugin

Query the Data Delivery Network

Query the DDN

The easiest way to query any data on Splitgraph is via the "Data Delivery Network" (DDN). The DDN is a single endpoint that speaks the PostgreSQL wire protocol. Any Splitgraph user can connect to it at data.splitgraph.com:5432 and query any version of over 40,000 datasets that are hosted or proxied by Splitgraph.

For example, you can query the land_use table in this repository, by referencing it like:

"cambridgema-gov/land-use-6erf-wujk:latest"."land_use"

or in a full query, like:

SELECT
    ":id", -- Socrata column ID
    "lot", -- This is the Lot portion of the unique parcel identifier found in the deed and used by the Assessing data system. In a few cases, where parcels have been subdivided subsequent to January 1, 2012, a placeholder Map-Lot number is assigned that differs from that used elsewhere.
    "lucode", -- The four digit text string in this field indicates the primary usage of a parcel. While the codes are based on the standard Massachusetts assessing land use classification system, they differ in a number of cases; the coding system used here is unique to this data set. Note that other minor uses may occur on a property and, in some cases, tenants may introduce additional uses not reflected here (eg, office space used as a medical office, home based businesses).
    "ludesc", -- The short description gives more detail about the specific use indicated by the Land Use Code. Most descriptions are taken from the standard Massachusetts assessing land use classification system.
    "existunits", -- This value indicates the number of existing residential units as of July 1, 2016. A residential unit may be a house, an apartment, a mobile home, a group of rooms or a single room that is occupied (or, if vacant, intended for occupancy) as separate living quarters. This includes units found in apartment style graduate student housing residences and rooms in assisted living facilities and boarding houses are treated as also housing units. The unit count does not include college or graduate student dormitories, nursing home rooms, group homes, or other group quarters living arrangements.
    "gradstuden", -- A value of “Yes” indicates the parcel is used to house graduate students in apartment style units. Graduate student dormitories are treated as a higher education land use.
    "gqlodgingh", -- A value of “Yes” indicates that the primary use of the property is as a group quarters living arrangement. Group quarters are a place where people live or stay, in a group living arrangement, that is owned or managed by an entity or organization providing housing and/or services for the residents. Group quarters include such places as college residence halls, residential treatment centers, skilled nursing facilities, group homes, military barracks, correctional facilities, and workers’ dormitories. Most university dormitories are included under the broader higher education land use code, as most dormitories are included in the larger parcels comprising the bulk of higher education campuses.
    "map", -- This Map portion of the unique parcel identifier found in the deed and used by the Assessing data system. In a few cases, where parcels have been subdivided subsequent to January 1, 2012, a placeholder Map-Lot number is assigned that differs from that used elsewhere.
    "condoflag", -- “Yes” indicates that the parcel is owned as a condominium. Condo properties can include one or more uses, including residential, commercial, and parking. The great majority of such properties in Cambridge are residential only.
    "landarea", -- Square Feet
    "the_geom", -- Polygon layer geometry. The latitude and longitude data for the underlying map shapes.
    "mixedusety", -- Two flags are used for this field. “Groundfloor” indicates that a commercial use is found on the ground floor of the primary building, and upper floors are used for residential purposes. “Mixed” indicates that two or more uses are found throughout the structure or multiple structures on the parcel, one of which is residential.
    "location", -- In the great majority of cases this is the street address of the parcel as it is recorded in the Registry of Deed record. In instances where edits were made to the base parcel layer the best address available at the time is employed.
    "ml", -- Map-Lot: This a unique parcel identifier found in the deed and used by the Assessing data system. In a few cases, where parcels have been subdivided subsequent to January 1, 2012, a placeholder Map-Lot number is assigned that differs from that used elsewhere.
    "category", -- This broader grouping of land uses can be used to map land use data. You can find the land use data mapped at: http://www.cambridgema.gov/~/media/Files/CDD/Maps/LandUse/cddmap_land_use_20160701.pdf?la=en
    "taxstatus" -- A value indicates that the parcel is not subject to local property taxes. The following general rules are employed to assign properties to subcategories, though special situations exist in a number of cases. o Authority: Properties owned the Cambridge Redevelopment Authority and Cambridge Housing Authority. o City: Properties owned by the City of Cambridge or cemetery land owned by the Town of Belmont. o Educ: Includes properties used for education purposes, ranging from pre-schools to university research facilities. (More detail about the level of education can be found using the Land Use Code.) o Federal: Properties owned by the federal government, including the Post Office. Certain properties with assessing data indicating Cambridge Redevelopment Authority ownership are in fact owned by the federal government as part of the Volpe Transportation Research Center and are so treated here. o Other: Nontaxable properties owned by a nonprofit organization and not falling within one of the other categories. o Religious: Properties owned by religious organizations. Parochial schools located on separate parcels from other uses are included in the Educ Nontax category. o State: Properties owned by the State of Massachusetts, including those owned by the MBTA, Middlesex County and DCR parklands.
FROM
    "cambridgema-gov/land-use-6erf-wujk:latest"."land_use"
LIMIT 100;

Connecting to the DDN is easy. All you need is an existing SQL client that can connect to Postgres. As long as you have a SQL client ready, you'll be able to query cambridgema-gov/land-use-6erf-wujk with SQL in under 60 seconds.

This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.cambridgema.gov. When you querycambridgema-gov/land-use-6erf-wujk:latest on the DDN, we "mount" the repository using the socrata mount handler. The mount handler proxies your SQL query to the upstream data source, translating it from SQL to the relevant language (in this case SoQL).

We also cache query responses on the DDN, but we run the DDN on multiple nodes so a CACHE_HIT is only guaranteed for subsequent queries that land on the same node.

Query Your Local Engine

Install Splitgraph Locally
bash -c "$(curl -sL https://github.com/splitgraph/splitgraph/releases/latest/download/install.sh)"
 

Read the installation docs.

Splitgraph Cloud is built around Splitgraph Core (GitHub), which includes a local Splitgraph Engine packaged as a Docker image. Splitgraph Cloud is basically a scaled-up version of that local Engine. When you query the Data Delivery Network or the REST API, we mount the relevant datasets in an Engine on our servers and execute your query on it.

It's possible to run this engine locally. You'll need a Mac, Windows or Linux system to install sgr, and a Docker installation to run the engine. You don't need to know how to actually use Docker; sgrcan manage the image, container and volume for you.

There are a few ways to ingest data into the local engine.

For external repositories (like this repository), the Splitgraph Engine can "mount" upstream data sources by using sgr mount. This feature is built around Postgres Foreign Data Wrappers (FDW). You can write custom "mount handlers" for any upstream data source. For an example, we blogged about making a custom mount handler for HackerNews stories.

For hosted datasets, where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.

Mounting Data

This repository is an external repository. It's not hosted by Splitgraph. It is hosted by data.cambridgema.gov, and Splitgraph indexes it. This means it is not an actual Splitgraph image, so you cannot use sgr clone to get the data. Instead, you can use the socrata adapter with the sgr mount command. Then, if you want, you can import the data and turn it into a Splitgraph image that others can clone.

First, install Splitgraph if you haven't already.

Mount the table with sgr mount

sgr mount socrata \
  "cambridgema-gov/land-use-6erf-wujk" \
  --handler-options '{
    "domain": "data.cambridgema.gov",
    "tables": {
        "land_use": "6erf-wujk"
    }
}'

That's it! Now you can query the data in the mounted table like any other Postgres table.

Query the data with your existing tools

Once you've loaded the data into your local Splitgraph engine, you can query it with any of your existing tools. As far as they're concerned, cambridgema-gov/land-use-6erf-wujk is just another Postgres schema.