Map of the Week: Making Rent

Property values are a persistent obsession for New Yorkers as are the arbitrary constraints to dwelling well (dwelling? yes). Between navigating the rental quagmire of online listings to toggling the various Trulia StreetEasy and Zillow helper services you can tumble down a rabbit hole of the worst options imaginable (or at least publishable). In the bizzare world of renting we can help you through the looking glass.

This Map of the Week features the work of a familiar face to CartoDB [Chris Henrick] who between juggling GIS projects and grad school also co-organizes Maptime NYC. His MFA thesis project "Am I Rent Stabilized?" combines code maps and critical thinking about urban planning and renters rights - all of which we love and welcome at CartoDB! He currently lives in Brooklyn NY where we locals join him in an obsession with rent rates and regulations. Read on to discover more of the mechanics behind his impressive web app!

Am I Rent Stabilized? is a web application that encourages New York City tenants to find out if their landlord may be illegally overcharging them for a rent stabilized apartment and if so motivates them to take action. The development of the app was spearheaded by the lack of enforcement of rent regulation laws in NYC by the local and state government. It is an attempt at using open data as a prompt for civic action rather than solely for visualization and analysis. The app asks the user to input their address and borough then checks it against a database of properties that are likely to have rent stabilized apartments. From here the app recommends a course of action and informs the user of their nearest tenants rights group so they can get help. The app features a responsive UI that is mobile friendly and its content can be toggled to either Spanish or Chinese for non-english speakers.

DHCR & MapPLUTO Data Processing

Am I Rent Stabilized? uses a database I created that lives on CartoDB which enables the website to work as a fully funcitonal web-app using the CartoDB SQL API and CartoDB.js library. However I did a lot of data processing on my local machine before importing the data into CartoDB.

1. Processing the DHCR Rent Stabilized Building List

   Excel workbooks obtained from a Freedom of Information Law request by the NY Department of Housing and Community Renewal were normalized stacked and converted to a Comma Separated Value (CSV) file format using a Node JS script. This allowed the data to be geocoded at once and then imported into a PostgreSQL database where it could be analyzed with the NYC MapPLUTO GIS tax lot data.

2. Geocoding the Processed DHCR data

   A Python script was then used to obtain values for each property's Borough - Block - Lot number (BBL) Building Identificaiton Number (BIN) and latitude - longitude coordinates from the NYC GeoClient API. A property's street address and borough are passed to the GeoClient API which then returns a plethora of useful information about the property such as the BBL BIN latitude and logitude values.

3. Determining NYC Properties That Are Likely Rent Stabilized

   After processing and geocoding the DHCR data it was imported into a PostgreSQL database using CSVkit's csvsql command as follows: csvsql --db:///nyc_pluto --insert dhcr_rs_geocoded.csv --table dhcr_rs

   From here PostgreSQL was then used to analyze the data. Here is a link to the entire SQL code but the most important queries are the following:

   ##_INIT_REPLACE_ME_PRE_##

   -- select the number of properties in the dhcr list the nyc map pluto data
   -- returns 47 130 rows
   SELECT Count(a.bbl) FROM
       (
           SELECT DISTINCT bbl FROM map_pluto2014v2
       ) AS a
       INNER JOIN
       (
       SELECT DISTINCT ON (bbl) bbl FROM dhcr_rs_w_bbls
         WHERE bbl IS NOT NULL
       ) AS b
       ON a.bbl = b.bbl;

   -- select the number of properties in the dhcr list not in the map pluto "likely rent-stabilized" query -- returns 12 549 rows

   CREATE TABLE map_pluto2014v2_likely_rs AS SELECT COUNT(a.bbl) FROM ( SELECT DISTINCT bbl FROM map_pluto2014v2 WHERE yearbuilt < 1974 AND unitsres >= 6 AND (ownername NOT ILIKE 'new york city housing authority' or ownername NOT ILIKE 'nycha') AND bldgclASs NOT ILIKE 'r%' ) AS a LEFT JOIN ( SELECT DISTINCT bbl FROM dhcr_rs_w_bbls WHERE bbl IS NOT NULL ) AS b ON a.bbl = b.bbl WHERE b.bbl IS NULL; ##_END_REPLACE_ME_PRE_##

These two queries tell us: A. what properties in the MapPLUTO tax lot data match the DHCR's rent-stabilized building list and B. what other properties are likely to have rent-stabilized apartments but aren't on the DHCR list. From here I created a table that combines data from both queries as well as a flag that states whether or not the property is listed in the DHCR data.

##_INIT_REPLACE_ME_PRE_##

    CREATE TABLE map_pluto_not_dhcr AS
        SELECT not_dhcr.address 
          not_dhcr.unitsres 
          not_dhcr.borough 
          not_dhcr.ownername 
          not_dhcr.zipcode 
          not_dhcr.yearbuilt 
          not_dhcr.geom 
          not_dhcr.cd 
          not_dhcr.council 
          not_dhcr.bbl::bigint
        FROM
          (
              SELECT a.*
              FROM
              (
                  SELECT * FROM map_pluto2014v2
                  WHERE yearbuilt < 1974 AND unitsres >= 6
                      AND (ownername not ILIKE 'new york city housing authority' or ownername not ILIKE 'nycha')
                      AND bldgclass not ILIKE 'r%'
              ) AS a
              LEFT JOIN
              (
              SELECT * FROM dhcr_rs_w_bbls
              WHERE bbl IS NOT NULL
              ) AS b
              ON a.bbl = b.bbl
              WHERE b.bbl IS NULL
          ) AS not_dhcr;

CREATE TABLE map_pluto_dhcr_rs AS
    SELECT dhcr.address 
        dhcr.unitsres 
        dhcr.borough 
        dhcr.ownername 
        dhcr.zipcode 
        dhcr.yearbuilt 
        dhcr.geom 
        dhcr.cd 
        dhcr.council 
        dhcr.bbl::bigint
    FROM
        (SELECT c.address 
            c.unitsres 
            c.borough 
            c.ownername 
            c.zipcode 
            c.yearbuilt 
            c.bbl 
            c.cd 
            c.council 
            c.geom
          FROM
          map_pluto2014v2 c 
          (
          SELECT DISTINCT bbl FROM dhcr_rs_w_bbls
          WHERE bbl IS NOT NULL
          ) d
          WHERE c.bbl = d.bbl
        ) AS dhcr;


-- I then added a column to identify properties that are registered or not registered with the DHCR:
ALTER TABLE map_pluto_not_dhcr add column registered boolean;
UPDATE map_pluto_not_dhcr set registered = false;

ALTER TABLE map_pluto_dhcr_rs add column registered boolean;
UPDATE map_pluto_dhcr_rs set registered = true;


-- now these two tables can be combined AND have a boolean value for whether or not they are in the DHCR's rent-stabilized buildings list.
-- 59 679 rows total.
DROP TABLE map_pluto2014v2_likely_rs;
CREATE TABLE map_pluto_likely_rs AS
    SELECT *
    FROM
        map_pluto_not_dhcr
    UNION
    SELECT *
    FROM
        map_pluto_dhcr_rs;


-- check to make sure the data looks good:
SELECT Count(*) FROM map_pluto_likely_rs WHERE registered IS NULL;
    -- returns 0 rows
SELECT Count(DISTINCT bbl) FROM map_pluto_likely_rs;
  -- returns 59 679 rows
SELECT Count(*) FROM map_pluto_likely_rs WHERE geom IS NULL;
    -- returns 0 rows
SELECT Sum(unitsres) AS total_res_units FROM map_pluto_likely_rs;
    -- returns 1 962 469

##_END_REPLACE_ME_PRE_##

4. Further Data Processing Using CartoDB Lastly the data was imported into CartoDB and some final tweaks to the data were made.

   ##_INIT_REPLACE_ME_PRE_##

   --  Remove all properties owned by the NYC Housing Authority that were missed.
   --  This involved doing a spatial intersect with polygon centroids created from
   -- a shapefile of NYCHA properties from 2011 to determine all spellings of "NYCHA"

   SELECT DISTINCT a.ownername FROM map_pluto_likely_rs a nycha_centroids b where ST_Intersects( a.the_geom b.the_geom ) ORDER BY ownername

   -- remove properties that are obviously owned by NYCHA DELETE FROM map_pluto_likely_rs WHERE ownername LIKE 'NYC HOUSING%'; DELETE FROM map_pluto_likely_rs WHERE ownername ILIKE 'new york city%'; DELETE FROM map_pluto_likely_rs WHERE ownername LIKE 'NYC CITY HSG%'; DELETE FROM map_pluto_likely_rs WHERE ownername = 'CITY OF NEW YORK'; DELETE FROM map_pluto_likely_rs WHERE ownername LIKE 'N Y C H A%'; DELETE FROM map_pluto_likely_rs WHERE ownername LIKE 'N.Y.C. HOUSING AUTHOR%'; DELETE FROM map_pluto_likely_rs WHERE ownername LIKE 'N Y C HOUSING AUTHORI%'; DELETE FROM map_pluto_likely_rs WHERE ownername = 'NY HOUSING AUTHORITY'; DELETE FROM map_pluto_likely_rs WHERE ownername = 'NEW YRK CTY HSG AUTHR';

   -- pgsql2shp converted boolean value of the "registered" column to T / F -- so I changed the valuse to 'yes' / 'no' for CartoDB Infowindows UPDATE map_pluto_likely_rs set registered = 'no' WHERE registered = 'F'; UPDATE map_pluto_likely_rs set registered = 'yes' WHERE registered = 'T';

   -- change boro codes to actual names for CartoDB Infowindows UPDATE map_pluto_likely_rs set borough = 'Queens' WHERE borough = 'QN'; UPDATE map_pluto_likely_rs set borough = 'Brooklyn' WHERE borough = 'BK'; UPDATE map_pluto_likely_rs set borough = 'Staten Island' WHERE borough = 'SI'; UPDATE map_pluto_likely_rs set borough = 'Bronx' WHERE borough = 'BX'; UPDATE map_pluto_likely_rs set borough = 'Manhattan' WHERE borough = 'MN'; ##_END_REPLACE_ME_PRE_##

Creating Catchment Area Polygons For Local Tenants Rights Organizations

In order to inform a user as to whether or not any local tenants rights organizations are operating within their neighborhood custom polygon geospatial data was created to respresent each of the 94 organization's service areas.

First a list of [Community Based Housing Organizations] was scraped from an HTML table on the DHCR's website using a Python script. Organizations that operate in the boroughs / counties that make up NYC were pulled out from the scraped data into a new table.

For these 94 organizations polygon data was manually created representing each organization's service area. Reference polygon geospatial data sources used to create the service areas include Pediatcities NYC Neighborhood boundaries NYC Planning Neighborhood Tabulation Areas U.S. Census Zipcode Tabulation Areas and NYC Planning Community District boundaries. This data was copied and in some cases aggregated (dissolved) into a new dataset using MAPublisher a GIS plug-in for Adobe Illustrator. In some cases boundaries had to be drawn by hand such as for the Cooper Square Committee which operates within a very specific area in the East Village of Manhattan. Once completed the polygon data was joined to the DHCR Community Housing Based Organizations for NYC and then exported to a shapefile format.

The data was then imported into CartoDB for use with Am I Rent Stabilized. When a user's address is geocoded a point in polygon SQL query is made via PostGIS to the data in CartoDB.

For example:

##_INIT_REPLACE_ME_PRE_##

  SELECT * FROM nyc_tenants_rights_service_areas
  WHERE
  ST_Contains(
    nyc_tenants_rights_service_areas.the_geom 
    ST_GeomFromText(
     'Point(-73.917104 40.694827)'  4326
    )
  );
##_END_REPLACE_ME_PRE_## 

If a user's address is within a group's cachment area that group's information is passed into a modal in the app that displays information such as the group's website url phone number contact person and/or address. As this data varies from group to group a Handlebars.js helper function is used to check if the data exists before passing it to the Handlebars HTML template.

{% highlight js %} var H = Handlebars;

H.registerHelper('each' function(context options) { var ret = ""; for(var i=0 j=context.length; i<j; i++) { ret = ret + options.fn(context[i]); } return ret; });

H.registerHelper('if' function(conditional options) { if (conditional) { return options.fn(this); } else { return options.inverse(this); } }); ##_END_REPLACE_ME_PRE_##

Learn More!

Interested in learning more about the project? Tweet Chris (@chrishenrick) your thoughts and questions check out the links below and review his cross-posting on Urban Omnibus!

• Github Repo for Am I Rent Stabilized
• Visualization of NYC properties that likely have rent-stabilized apartments
• DHCR Rent Stabilized Building List (note: this is just a list of addresses that have one or more rent stabilized apartments not the actual apartment numbers)
• NYC Likely Rent-Stabilized GIS data
• NYC Local Tenants Rights Groups Service Areas

Thanks for reading and happy renting!