{"id":2409,"date":"2016-11-03T10:33:56","date_gmt":"2016-11-03T17:33:56","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?page_id=2409"},"modified":"2016-11-03T10:33:56","modified_gmt":"2016-11-03T17:33:56","slug":"01sep-archive","status":"publish","type":"page","link":"http:\/\/blog.light42.com\/wordpress\/?page_id=2409","title":{"rendered":"01Sep-Archive"},"content":{"rendered":"

Research:<\/strong>
\n * County Online Data ECN<\/a>
\n * LA Bldg Data Import \/ LA Buildings 2008
\n    table: la_bldgs_raw<\/code>; count: 3,120,189
\n * OSM data — focus on Hollywood Hills (-118.366 34.105 )<\/p>\n

Infrastructure:<\/strong>
\n * Postgres \/ PostGIS
\n    database geo_datamine_f2<\/code>
\n * US Census TIGER — 2013,2014,2015,2016
\n * Geoserver
\n * Git
\n * Web Directory -ECN-<\/a><\/p>\n

 <\/p>\n

\n

\"OSM_LA_bldgs_raw_test1\"<\/a><\/p>\n

\r\ngeo_datamine_f2=# \\d la_bldgs_raw\r\n                           Table \"public.la_bldgs_raw\"\r\n   Column   |            Type             |              Modifiers                          \r\n------------+-----------------------------+--------------------------------------\r\n gid        | integer                     | not null default nextval('la_bldgs_raw_gid_seq'::regclass)\r\n objectid_1 | numeric(10,0)               | \r\n objectid   | numeric(10,0)               | \r\n code       | character varying(80)       | \r\n bld_id     | character varying(80)       | \r\n height     | numeric                     | \r\n elev       | numeric                     | \r\n area       | numeric(10,0)               | \r\n lariac_sou | character varying(80)       | \r\n lariac_dat | character varying(80)       | \r\n ain        | character varying(80)       | \r\n status     | character varying(80)       | \r\n code_num   | numeric(10,0)               | \r\n source     | character varying(25)       | \r\n date_      | character varying(4)        | \r\n shape_leng | numeric                     | \r\n shape_le_1 | numeric                     | \r\n shape_area | numeric                     | \r\n generaluse | character varying(254)      | \r\n specificus | character varying(254)      | \r\n yearbuilt  | character varying(254)      | \r\n specific_1 | character varying(254)      | \r\n units      | character varying(254)      | \r\n geom       | geometry(MultiPolygon,4326) | \r\nIndexes:\r\n    \"la_bldgs_raw_pkey\" PRIMARY KEY, btree (gid)\r\n    \"la_bldgs_raw_geom_idx\" gist (geom)\r\n\r\n#-- Categorize and Count Bldgs in the set, by attribute\r\n\r\n  count  |   generaluse   \r\n---------+----------------\r\n 2791249 | Residential\r\n  128551 | \r\n  106015 | Commercial\r\n   63621 | Industrial\r\n   20707 | Institutional\r\n    4647 | Recreational\r\n    2287 | Dry Farm\r\n    1960 | Miscellaneous\r\n    1084 | Irrigated Farm\r\n      68 | (missing)\r\n(10 rows)\r\n\r\ngeo_datamine_f2=# \r\nselect count(*),generaluse from la_bldgs_raw \r\ngroup by generaluse order by count(*) desc;\r\n\r\n\r\n#-- What Counties are included in this dataset ?\r\n           row           \r\n-------------------------\r\n (395,Ventura)\r\n (398,\"Los Angeles\")\r\n (1111,\"San Bernardino\")\r\n (2553,Orange)\r\n(4 rows)\r\n\r\ngeo_datamine_f2=# \r\nselect distinct(c.gid, c.name) from \r\ntl_2016_us_county c JOIN la_bldgs_pt p\r\n ON (st_intersects( c.geom, p.geom));\r\n\r\n#-- How Many bldgs in each County ?\r\n\r\n      name      |  count  \r\n----------------+---------\r\n San Bernardino |      39\r\n Orange         |      43\r\n Ventura        |       1\r\n Los Angeles    | 3120105\r\n(4 rows)\r\n\r\ngeo_datamine_f2=# \r\nselect c.name, count(*) \r\nfrom tl_2016_us_county c, la_bldgs_pt p\r\nwhere st_intersects( c.geom, p.geom)\r\ngroup by c.name;\r\n\r\n#-- How Many 5-or-more Apt Bldgs ?\r\n\r\ncount | 143121\r\n\r\ngeo_datamine_f2=# \r\nselect count(*) from la_bldgs_raw \r\n where specificus = 'Five or More Units or Apartments (Any Combination)';\r\n\r\n#-- Ingesting the Data\r\ncurl -L \"http:\/\/latimes-graphics-media.s3.amazonaws.com\/jon-temp\/lariac_buildings_2008.zip\" \r\n\r\nogr2ogr -f PostgreSQL PG:dbname=geo_datamine_f2 -nln la_bldgs_raw -nlt MULTIPOLYGON -geomfield geom \\\r\n  -t_srs EPSG:4326 merged-buildings-state-plane.shp merged-buildings-state-plane\r\n\r\n--\r\ncreate table la_bldgs_pt as\r\nselect \r\n  ogc_fid as gid,\r\n  objectid_1,objectid,\r\n  code,bld_id,height,elev,area,\r\n  lariac_sou,lariac_dat,ain,status,\r\n  code_num,source,date_,\r\n  generaluse,specificus,yearbuilt,specific_1,units,\r\n  st_centroid(wkb_geometry) as geom\r\nfrom \r\n  la_bldgs_raw;\r\n--\r\nalter table la_bldgs_pt add constraint la_bldgs_pt_uniq UNIQUE(gid);\r\nalter table la_bldgs_pt add PRIMARY KEY (gid);\r\ncreate index la_bldgs_pt_geom_idx on la_bldgs_pt using GIST (geom);\r\n\r\n\r\n--\r\n-- NOTE custom srs used for LA Master<\/a>\r\n--  https:\/\/github.com\/osmlab\/labuildings\/blob\/master\/Makefile\r\n-- -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'\r\n\r\n<\/pre>\n
\n
 <\/p>\n
\"test23-scrn2\"<\/a><\/p>\n
\r\nHOLLYWOOD_CA.osm  Polygons => 11,368\r\n osm2pgsql 0.88\r\n\r\n count |   building   \r\n-------+--------------\r\n  5057 | house\r\n  2704 | apartments\r\n  1413 | residential\r\n   531 | yes\r\n   435 | commercial\r\n   425 | retail\r\n   122 | industrial\r\n    67 | hotel\r\n    38 | warehouse\r\n    29 | school\r\n    12 | garages\r\n     4 | church\r\n     3 | roof\r\n     3 | hospital\r\n     1 | office\r\n     1 | construction\r\n     1 | college\r\n     1 | greenhouse\r\n(18 rows)\r\n\r\ntest23=# select count(*),building from planet_osm_polygon \r\nWHERE building is not null GROUP BY building ORDER BY count(*) desc;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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=# […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1166,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"_links":{"self":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/pages\/2409"}],"collection":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2409"}],"version-history":[{"count":1,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/pages\/2409\/revisions"}],"predecessor-version":[{"id":2410,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/pages\/2409\/revisions\/2410"}],"up":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/pages\/1166"}],"wp:attachment":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2409"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}