Spatial Attributes Assignment

About

Spatial Attributes can be put into two categories, geometries (geom, longtitude, latitude) and spatial boundaries (geo_cd, geo_censusblock2010, geo_censustract2010, etc). You can find all final consolidated geospatial fields from the table SPATIAL_devdb

    CREATE TABLE SPATIAL_devdb (
        uid integer,
        geo_bbl text,
        geo_bin text,
        geo_address_numbr text,
        geo_address_street text,
        geo_address text,
        geo_zipcode text,
        geo_boro text,
        geo_cd text,
        geo_council text,
        geo_ntacode2010 text,
        geo_ntaname2010 character varying,
        geo_censusblock2010 text,
        geo_censustract2010 text,
        geo_csd text,
        geo_policeprct text,
        geo_firedivision text,
        geo_firebattalion text,
        geo_firecompany text,
        geo_puma text,
        geo_schoolelmntry character varying,
        geo_schoolmiddle character varying,
        geo_schoolsubdist character varying,
        geo_latitude double precision,
        geo_longitude double precision,
        latitude double precision,
        longitude double precision,
        geom geometry,
        geomsource text
    );

Geosupport Logic

1. Input:

-- for Devdb main table
    SELECT
        distinct uid,
        address_numbr as house_number,
        address_street as street_name,
        boro as borough
    FROM _INIT_devdb

2. Pass through 1B regular mode
3. If 2 failed, then pass through 1B tpad mode
4. If 3 failed, then mark status as failure

Geom Assignment

Relevant Files

• db-developments/developments_build/python/geocode.py
• db-developments/developments_build/sql/_geo.sql

1. For every job in _INIT_devdb we will pass address_numbr,address_street and boro. Geocoded results will be recorded in _GEO_devdb. Here are the columns included below:

    CREATE TABLE _GEO_devdb (
        geo_address_street text,
        geo_address_numbr text,
        latitude text,
        longitude text,
        geo_bin text,
        geo_bbl text,
        geo_boro text,
        geo_cd text,
        geo_puma text,
        geo_firedivision text,
        geo_firebattalion text,
        geo_firecompany text,
        geo_censustract2010 text,
        geo_censusblock2010 text,
        geo_council text,
        geo_csd text,
        geo_policeprct text,
        geo_zipcode text,
        geo_ntacode2010 text,
        grc text,
        grc2 text,
        msg text,
        msg2 text,
        uid text,
        mode text,
        func text,
        status text
    );

2. To create the field geom, we use longitude and latitude from _GEO_devdb as default. For all records, we will create geom using the following hierachy:

   1. Take geometry from using DoITT building footprints centroid using bin as a join_key (note bin from dob_jobapplications directly)
   2. Take geometry from using DoITT building footprints centroid using geo_bin as a join_key (note that geo_bin are bins from geosupport, taking from _GEO_devdb)
   3. Then take lat / long from BBL point via GeoSupport
   4. Lastly, take BBL centroid from shoreline clipped MapPLUTO using bbl as join_key (note bbl from dob_jobapplications directly)

3. Table GEO_devdb will be created with consolidated geometries with the following schema.

    CREATE TABLE geo_devdb (
        uid integer,
        job_number character varying,
        bbl text,
        bin character varying,
        date_lastupdt character varying,
        job_desc character varying,
        geo_bbl text,
        geo_bin text,
        geo_address_numbr text,
        geo_address_street text,
        geo_address text,
        geo_zipcode text,
        geo_boro text,
        geo_cd text,
        geo_council text,
        geo_ntacode2010 text,
        geo_censusblock2010 text,
        geo_censustract2010 text,
        geo_csd text,
        geo_policeprct text,
        geo_puma text,
        geo_firedivision text,
        geo_firebattalion text,
        geo_firecompany text,
        geo_latitude double precision,
        geo_longitude double precision,
        mode text,
        latitude double precision,
        longitude double precision,
        geom geometry,
        geomsource text
    );

4. Corrections are applied on GEO_devdb with manually researched longitude, latitude and geom. Note that you can find where geoms are coming from by looking at geomsource. e.g.

   BIN DOB buildingfootprints BBL DOB MapPLUTO Lat/Long geosupport Lat/Long DCP

Spatial Attributes

Relevant Files

• db-developments/developments_build/sql/_spatial.sql

1. Building on GEO_devdb, we will use the geom field to extract the spatial boundary fields using spatial join and create _SPATIAL_devdb. This table is created to help filling NULLs in any of the spatial attributes. Note that geo_schoolelmntry, geo_schoolmiddle, and geo_schoolsubdist are only coming from spatial joins.

    CREATE TABLE _SPATIAL_devdb (
        uid integer,
        geo_cd character varying,
        geo_ntacode2010 character varying,
        geo_censusblock2010 character varying,
        geo_censustract2010 character varying,
        geo_csd character varying,
        geo_boro character varying,
        geo_council character varying,
        geo_bbl character varying,
        geo_zipcode character varying,
        geo_policeprct character varying,
        geo_firecompany character varying,
        geo_firebattalion character varying,
        geo_firedivision character varying,
        geo_puma character varying,
        geo_bin character varying,
        base_bbl character varying,
        geo_schoolelmntry character varying,
        geo_schoolmiddle character varying,
        geo_schoolsubdist character varying
    );

2. Merging _SPATIAL_devdb and GEO_devdb we will create a consolidated spatial attributes with both geom and other spatial boundaries.

    CREATE TABLE SPATIAL_devdb (
        uid integer,
        geo_bbl text,
        geo_bin text,
        geo_address_numbr text,
        geo_address_street text,
        geo_address text,
        geo_zipcode text,
        geo_boro text,
        geo_cd text,
        geo_council text,
        geo_ntacode2010 text,
        geo_ntaname2010 character varying,
        geo_censusblock2010 text,
        geo_censustract2010 text,
        geo_csd text,
        geo_policeprct text,
        geo_firedivision text,
        geo_firebattalion text,
        geo_firecompany text,
        geo_puma text,
        geo_schoolelmntry character varying,
        geo_schoolmiddle character varying,
        geo_schoolsubdist character varying,
        geo_latitude double precision,
        geo_longitude double precision,
        latitude double precision,
        longitude double precision,
        geom geometry,
        geomsource text
    );