Skip to content

archive3

Assessment of Spatial Asset Contents – basics:
What classifications are used in the Multi-Family CoStar sample ?

geo_datamine_f2=# select count(*) from costar_mf_pts0;
count =>  12333

geo_datamine_f2=# select distinct(property_type,secondary_type) from costar_mf_pts0;
                        row                        
---------------------------------------------------
 (Multi-Family,Apartments)
 (Multi-Family,Dormitory)
 ("Multi-Family (Community Center)",Apartments)
 ("Multi-Family (Lifestyle Center)",Apartments)
 ("Multi-Family (Neighborhood Center)",Apartments)
 ("Multi-Family (Power Center)",Apartments)
 ("Multi-Family (Regional Mall)",Apartments)
 ("Multi-Family (Strip Center)",Apartments)
 ("Multi-Family (Super Regional Mall)",Apartments)
(9 rows)

 
Next, classification of the la_bldgs_pt table. -query-

...
 (Residential,"Double, Duplex, or Two Units")
 (Residential,"Five or More Units or Apartments (Any Combination)")
 (Residential,"Four Units  (Any Combination)")
 (Residential,"Manufactured Home")
 (Residential,"Manufactured Home Park")
 (Residential,"Rooming/Boarding House")
 (Residential,"Single Family Residence")
 (Residential,"Three Units (Any Combination)")
 (Residential,)

--
geo_datamine_f2=# select * from la_bldgs_pt where 
generaluse = 'Residential' AND  specificus ~ '^Five';

...
-[ RECORD 143121 ]---------------------------------------------
gid        | 3120182
objectid_1 | 3120182
objectid   | 0
code       | Building
bld_id     | 517655867139
height     | 23.64000000000
elev       | 761.32000000000
area       | 0
lariac_sou | 
lariac_dat | 
ain        | 5324018017
status     | 
code_num   | 0
source     | LARIAC2
date_      | 2008
generaluse | Residential
specificus | Five or More Units or Apartments (Any Combination)
yearbuilt  | 1960
specific_1 | 4 Stories or Less
units      | 13
geom       | 0101000020E61000001760CC5D4D895DC0D98768F3BB0F4140

--

-- count buildings per subdivision

        cousub name           | count  
 ...
 Inglewood                    | 100623
 Agoura Hills-Malibu          |  27751
 Los Angeles                  | 706297
 San Fernando Valley          | 577742
 ...
(20 rows)

--
geo_datamine_f2=# select cs."name", count(b.*) 
from tl_2016_06_cousub cs,  la_bldgs_pt b
where
  cs.countyfp = '037'  AND
  st_intersects( cs.geom, b.geom)
group by
  cs."name";

-- Use Inglewood as an LA Example ...

 

Focus on Inglewood, Los Angeles

Inglewood is a US Census County subdivision -wikipedia-, and is a good sample area to use to become more familiar with the contents of 3.1 million building records in the LA Import resource. We shall pay particular attention to correlation, or lack, with the CoStar sample supplied by CEC.

* Use a spatial SQL analytics environment to select all RESIDENTIAL buildings in the Inglewood County Subdivision -img-

This image shows aprox 90,000 residential buildings (gray), 6956 “Residential - Five or more units” buildings (blue), and 86 CoStar listings in the CEC-supplied data sample. (any combination of attributes and spatial relationship can be used to generate these views)

* Use Openstreetmap for a cartographic impression of the area, landmarks, and structural composition -img-

* Use a desktop GIS client to show asset details — the area near the large freeways intersection is easy to identify, and has buildings in the class we are interested in .. -img-

In this layout, CoStar MF buildings are marked with a large green dot; “Residential - Five or more units” buildings are shown as black dots with labels indicating the supplied units count. County Blockgroups are identified as large IDs with light green backing. A single record is selected, with attributes shown. The record is notable because at that location there appears to be a very large residential complex, there is one CoStar marker there, yet there are two LA Buildings records shown.

Using SQL and converting the “units” field to an integer, generate a database table of Residential Units 17 or greater; display -img-

Notice that for each of the six visible CoStar points, there are LA_Bldgs points, but for four of the six CoStar points, there are clearly two LA_Bldgs points corresponding to one CoStar point. A closer look at one of those occurrences, West of the highway intersection -img-

 

Bakersfield MultiFamily Units over NAIP — Test Four

In order to define and scope the task of Machine Learning (ML) for this project in a job description, describe and execute a very short series of steps similar to the ML methods; examine and document test elements.

The following screen shots consist of:
* NAIP California_20090801
* Bakersfield City Building Footprints
* TIGER 2016 Block Groups, Kern County

Hint: Click on thumbnails to get full view; use your browser scaling to magnify

Inquiry: “given four CoStar-sourced multifamily dwelling units (MF) in a given BlockGroup in Bakersfield, what can be learned from examining aerial imagery”

mf_costar_bakersfield_b1

Bakersfield Test Case

Bakersfield Test Case

Observations

  • All four test points do correspond to clearly visible buildings in NAIP (dated third-quarter 2009)
  • MF Units in this test case correspond to multiple building footprints; none of the four MF units in BlockGroup 060290006004 are a single building
  • NAIP data alone cannot distinguish a MF complex in this test case — buildings within one MF complex and others nearby look similar, boundaries are not distinguishable from aerial imagery alone

mf_costar_bakersfield_b4

mf_costar_bakersfield_b4a

 

mf_costar_bakersfield_b5a

mf_costar_bakersfield_b5

 

mf_costar_bakersfield_b3

mf_costar_bakersfield_b2

 
California OSM Buildings Overview


    -- Count osm buildings per PlanningZone
    --  OSM CA Snapshot Sep16
/*
           pz       | count 
    ----------------+-------
     PZ_1 osm bldgs | 26276
     PZ_2 osm bldgs | 23568
     PZ_3 osm bldgs | 1262456
     PZ_4 osm bldgs | 78298
     PZ_5 osm bldgs | 598574
     PZ_6 osm bldgs | 404410
     PZ_7 osm bldgs | -NA-
     PZ_8 osm bldgs | 77696
*/

    SELECT 'PZ_8 osm bldgs' as pz, count(*)
    FROM
      tl_2016_us_county c, ca_osm_bldgs_pt b
    WHERE
      c.statefp = '06'  AND
      c.countyfp in (
        --- PZ_8
        -- 'San Diego'
         '073'
      )    AND
      st_intersects( c.geom, b.geom );

OSM Buildings Image Links Misc
Bldgs_osm_Alameda_Sep16.png
Bldgs_osm_SantaClara_Sep16.png
Bldgs_osm_SanMateo_Sep16.png
Bldgs_osm_PZ5_Sep16.pngg


-- SQL Query, once for each Subdivision

with b400 as (
  select 'East San Gabriel Valley' as csub, count(b.*) as bldg_cnt  
  from tl_2016_06_cousub cs, la_bldgs_pt b
  where 
    cs.geoid = '0603790810'   AND
    st_intersects( cs.geom, b.geom)
  group by
    cs.geoid
    ---------------------------------
    
),  r400 as (
  select 'res5p' as csub, count(b.*) as bldg_cnt  
  from tl_2016_06_cousub cs, la_bldgs_pt b
  where 
    cs.geoid = '0603790810'   AND
    st_intersects( cs.geom, b.geom)  AND
    b.specificus ~ '^Five'
  group by
    cs.geoid
)
---------------------------------
 SELECT
    b400.csub, b400.bldg_cnt, r400.csub as res5p, r400.bldg_cnt as res5p_cnt
FROM
    b400, r400 ;

-- County subdivisions for Los Angeles 06037


   geoid    |   short name (internal)       TIGER name             
------------+------------------------------------------------------
 0603791400 | inglewood                | Inglewood           
 0603792110 | newhall                  | Newhall             
 0603793380 | torrance                 | Torrance            
 0603792400 | pasadena                 | Pasadena            
 0603790015 | malibu                   | Agoura Hills-Malibu 
 0603792920 | santa_monica             | Santa Monica        
 0603793155 | east_la                  | South Gate-East Los Angeles
 0603790560 | compton                  | Compton             
 0603791730 | long_beach               | Long Beach-Lakewood 
 0603791750 | los_angeles              | Los Angeles         
 0603792360 | palos_verdes             | Palos Verdes        
 0603792785 | san_fernando_valley      | San Fernando Valley 
 0603793730 | whittier                 | Whittier            
 0603790730 | downey_norwalk           | Downey-Norwalk      
 0603793100 | south_bay_cities         | South Bay Cities    
 0603790810 | east_san_gabriel_valley  | East San Gabriel Valley
 0603793510 | upper_san_gabriel_valley | Upper San Gabriel Valley
 0603793200 | southwest_san_gabriel_valley | Southwest San Gabriel Valley
 0603792140 | north_antelope_valley    | North Antelope Valley
 0603793090 | south_antelope_valley    | South Antelope Valley