Research:
* County Online Data ECN
* LA Bldg Data Import / LA Buildings 2008
table: la_bldgs_raw; count: 3,120,189
* OSM data — focus on Hollywood Hills (-118.366 34.105 )
Infrastructure:
* Postgres / PostGIS
database geo_datamine_f2
* US Census TIGER — 2013,2014,2015,2016
* Geoserver
* Git
* Web Directory -ECN-
geo_datamine_f2=# \d la_bldgs_raw
Table "public.la_bldgs_raw"
Column | Type | Modifiers
------------+-----------------------------+--------------------------------------
gid | integer | not null default nextval('la_bldgs_raw_gid_seq'::regclass)
objectid_1 | numeric(10,0) |
objectid | numeric(10,0) |
code | character varying(80) |
bld_id | character varying(80) |
height | numeric |
elev | numeric |
area | numeric(10,0) |
lariac_sou | character varying(80) |
lariac_dat | character varying(80) |
ain | character varying(80) |
status | character varying(80) |
code_num | numeric(10,0) |
source | character varying(25) |
date_ | character varying(4) |
shape_leng | numeric |
shape_le_1 | numeric |
shape_area | numeric |
generaluse | character varying(254) |
specificus | character varying(254) |
yearbuilt | character varying(254) |
specific_1 | character varying(254) |
units | character varying(254) |
geom | geometry(MultiPolygon,4326) |
Indexes:
"la_bldgs_raw_pkey" PRIMARY KEY, btree (gid)
"la_bldgs_raw_geom_idx" gist (geom)
#-- Categorize and Count Bldgs in the set, by attribute
count | generaluse
---------+----------------
2791249 | Residential
128551 |
106015 | Commercial
63621 | Industrial
20707 | Institutional
4647 | Recreational
2287 | Dry Farm
1960 | Miscellaneous
1084 | Irrigated Farm
68 | (missing)
(10 rows)
geo_datamine_f2=#
select count(*),generaluse from la_bldgs_raw
group by generaluse order by count(*) desc;
#-- What Counties are included in this dataset ?
row
-------------------------
(395,Ventura)
(398,"Los Angeles")
(1111,"San Bernardino")
(2553,Orange)
(4 rows)
geo_datamine_f2=#
select distinct(c.gid, c.name) from
tl_2016_us_county c JOIN la_bldgs_pt p
ON (st_intersects( c.geom, p.geom));
#-- How Many bldgs in each County ?
name | count
----------------+---------
San Bernardino | 39
Orange | 43
Ventura | 1
Los Angeles | 3120105
(4 rows)
geo_datamine_f2=#
select c.name, count(*)
from tl_2016_us_county c, la_bldgs_pt p
where st_intersects( c.geom, p.geom)
group by c.name;
#-- How Many 5-or-more Apt Bldgs ?
count | 143121
geo_datamine_f2=#
select count(*) from la_bldgs_raw
where specificus = 'Five or More Units or Apartments (Any Combination)';
#-- Ingesting the Data
curl -L "http://latimes-graphics-media.s3.amazonaws.com/jon-temp/lariac_buildings_2008.zip"
ogr2ogr -f PostgreSQL PG:dbname=geo_datamine_f2 -nln la_bldgs_raw -nlt MULTIPOLYGON -geomfield geom \
-t_srs EPSG:4326 merged-buildings-state-plane.shp merged-buildings-state-plane
--
create table la_bldgs_pt as
select
ogc_fid as gid,
objectid_1,objectid,
code,bld_id,height,elev,area,
lariac_sou,lariac_dat,ain,status,
code_num,source,date_,
generaluse,specificus,yearbuilt,specific_1,units,
st_centroid(wkb_geometry) as geom
from
la_bldgs_raw;
--
alter table la_bldgs_pt add constraint la_bldgs_pt_uniq UNIQUE(gid);
alter table la_bldgs_pt add PRIMARY KEY (gid);
create index la_bldgs_pt_geom_idx on la_bldgs_pt using GIST (geom);
--
-- NOTE custom srs used for LA Master
-- https://github.com/osmlab/labuildings/blob/master/Makefile
-- -s_srs '+proj=lcc +lat_1=35.46666666666667 +lat_2=34.03333333333333 +lat_0=33.5 +lon_0=-118 +x_0=2000000.000101601 +y_0=500000.0001016002 +ellps=GRS80 +towgs84=-0.9956,1.9013,0.5215,0.025915,0.009426,0.011599,-0.00062 +units=us-ft +no_defs'
HOLLYWOOD_CA.osm Polygons => 11,368
osm2pgsql 0.88
count | building
-------+--------------
5057 | house
2704 | apartments
1413 | residential
531 | yes
435 | commercial
425 | retail
122 | industrial
67 | hotel
38 | warehouse
29 | school
12 | garages
4 | church
3 | roof
3 | hospital
1 | office
1 | construction
1 | college
1 | greenhouse
(18 rows)
test23=# select count(*),building from planet_osm_polygon
WHERE building is not null GROUP BY building ORDER BY count(*) desc;