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-
geo_datamine_f2=# select distinct (generaluse,specificus,specific_1)
from la_bldgs_pt order by (generaluse,specificus,specific_1);
-
(Commercial,"Animal Kennel",)
(Commercial,"Auto, Recreation Equipment, Construction Equipment Sales and Service","Auto Body Repair Shop")
(Commercial,"Auto, Recreation Equipment, Construction Equipment Sales and Service","Auto Service Centers (No Gasoline)")
(Commercial,"Auto, Recreation Equipment, Construction Equipment Sales and Service","Car Wash Only")
(Commercial,"Auto, Recreation Equipment, Construction Equipment Sales and Service","Car Wash Only, Self-service Type")
(Commercial,"Auto, Recreation Equipment, Construction Equipment Sales and Service","Farm and Construction Equipment Sales and Service")
(Commercial,"Auto, Recreation Equipment, Construction Equipment Sales and Service","New Car Sales and Service")
(Commercial,"Auto, Recreation Equipment, Construction Equipment Sales and Service","Recreation Equipment Sales and Service (Campers, Motor Homes and Boats)")
(Commercial,"Auto, Recreation Equipment, Construction Equipment Sales and Service","Used Car Sales")
(Commercial,"Auto, Recreation Equipment, Construction Equipment Sales and Service","Wireless Communication Tower")
(Commercial,"Bank, Savings and Loan","Wireless Communication Tower")
(Commercial,"Bank, Savings and Loan",)
(Commercial,Commercial,"Artist in Residence")
(Commercial,Commercial,"Miscellaneous Commercial")
(Commercial,Commercial,"Wireless Communication Tower")
(Commercial,Commercial,)
(Commercial,"Department Store","Building Supplies (Home Depot, etc.)")
...
geo_datamine_f2=# select * from la_bldgs_pt where
generaluse = 'Residential' ;
(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
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
–
* 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 ..
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
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
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”
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
060290006004are 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
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
-- 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_bldgs14_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_bldgs14_pt b
where
cs.geoid = '0603790810' AND
st_intersects( cs.geom, b.geom) AND
b.usedescription ~ '^Five'
group by
cs.geoid
)
---------------------------------
SELECT
b400.csub, b400.bldg_cnt, r400.csub as res5p, r400.bldg_cnt as res5p_cnt
FROM
b400, r400 ;
#-
csub | bldg_cnt | res5p | res5p_cnt
-------------------------+----------+-------+-----------
East San Gabriel Valley | 284344 | res5p | 7401
(1 row)
-- 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