![\"building_81719\"](\"http:\/\/www.fernandoquadro.com.br\/html\/wp-content\/uploads\/2016\/07\/building_81719.png\")
<\/p>\nPara o nosso exemplo de hoje vamos dar um zoom e encontrar um edif\u00edcio para analisar (se voc\u00ea aumenta o zoom, os edif\u00edcios se tornam clic\u00e1veis).<\/p>\n
<\/p>\n
Podemos ver que o edif\u00edcio #81719 (identificador da chave prim\u00e1ria do banco de dados) tem um n\u00famero limitado de atributos, incluindo uma eleva\u00e7\u00e3o de 1.446,43! Nossos dados LIDAR variam cerca de 450 metros, de modo que a eleva\u00e7\u00e3o nos edif\u00edcios \u00e9 provavelmente medida em p\u00e9s.<\/p>\n
Como podemos calcular a eleva\u00e7\u00e3o para o edif\u00edcio #81719 utilizando a tabela LIDAR? Veja como a l\u00f3gica funciona visualmente:<\/p>\n
1. Comece com o edif\u00edcio.<\/p>\n
![\"pc_analysis_1\"](\"http:\/\/www.fernandoquadro.com.br\/html\/wp-content\/uploads\/2016\/07\/pc_analysis_1.png\")
<\/p>\n
2. Localize todos os patches que se cruzam naquele edif\u00edcio.<\/p>\n
![\"pc_analysis_2\"](\"http:\/\/www.fernandoquadro.com.br\/html\/wp-content\/uploads\/2016\/07\/pc_analysis_2.png\")
<\/p>\n
3. Transforme essas manchas em pontos individuais.<\/p>\n
![\"pc_analysis_3\"](\"http:\/\/www.fernandoquadro.com.br\/html\/wp-content\/uploads\/2016\/07\/pc_analysis_3.png\")
<\/p>\n
4. Filtre esses pontos usando o contorno do edif\u00edcio.<\/p>\n
![\"pc_analysis_4\"](\"http:\/\/www.fernandoquadro.com.br\/html\/wp-content\/uploads\/2016\/07\/pc_analysis_4.png\")
<\/p>\n
5. Finalmente a m\u00e9dia dos pontos de eleva\u00e7\u00f5es dar\u00e1 um valor para o edif\u00edcio.<\/p>\n
Veja o procedimento acima em SQL:<\/p>\n
-- We run a set of subqueries sequentially using\r\n-- the \"with\" keyword\r\nWITH\r\n-- Get the one building we are interested in\r\nbuilding AS (\r\n SELECT geom FROM buildings\r\n WHERE buildings.gid = 81719\r\n),\r\n-- All the patches that intersect that building\r\npatches AS (\r\n SELECT pa FROM medford\r\n JOIN building ON PC_Intersects(pa, geom)\r\n),\r\n-- All the points in that patch\r\npa_pts AS (\r\n SELECT PC_Explode(pa) AS pts FROM patches\r\n),\r\n-- All the points in our one building\r\nbuilding_pts AS (\r\n SELECT pts FROM pa_pts JOIN building\r\n ON ST_Intersects(geom, pts::geometry)\r\n)\r\n-- Summarize those points by elevation\r\nSELECT\r\n Avg(PC_Get(pts, 'z')) AS lidar_meters\r\nFROM building_pts;<\/pre>\nO resultado do SQL \u00e9 441.075 metros, que em p\u00e9s \u00e9 1447.1, quase exatamente o mesmo valor do arquivo de edif\u00edcios, 1.446,43!<\/p>\n
Ok! Por\u00e9m podemos acrescentar a eleva\u00e7\u00e3o a partir da nossa imagem LIDAR para todos os nossos edif\u00edcios? Sim, mas vai demorar algum tempo de processamento. Primeiro vamos adicionar uma coluna para aceitar o valor, e ent\u00e3o executar uma atualiza\u00e7\u00e3o na nossa tabela do banco de dados.<\/p>\n
-- Add column for our calculate Z value\r\nALTER TABLE buildings ADD COLUMN z real;\r\n-- Update into the column\r\nUPDATE buildings SET z = elevs.z\r\nFROM (\r\n -- For every building, all intersecting patches\r\n WITH patches AS (\r\n SELECT\r\n buildings.gid AS buildings_gid,\r\n medford.id AS medford_id,\r\n medford.pa AS pa\r\n FROM medford\r\n JOIN buildings\r\n ON PC_Intersects(pa, geom)\r\n ),\r\n -- Explode those patches into points, remembering\r\n -- which building they were associated with\r\n pa_pts AS (\r\n SELECT buildings_gid, PC_Explode(pa) AS pts FROM patches\r\n )\r\n -- Use the building associations to efficiently\r\n -- spatially test the points against the building footprints\r\n -- Summarize per building\r\n SELECT\r\n buildings_gid,\r\n Avg(PC_Get(pts, 'z')) AS z\r\n FROM pa_pts\r\n JOIN buildings\r\n ON buildings.gid = buildings_gid\r\n WHERE ST_Intersects(buildings.geom, pts::geometry)\r\n GROUP BY buildings_gid\r\n) AS elevs\r\n-- Join calculated elevations to original buildings table\r\nWHERE elevs.buildings_gid = gid;<\/pre>\nNossa tabela est\u00e1 atualizada! Confira as eleva\u00e7\u00f5es originais e calculadas (temos que converter a coluna z de metros para p\u00e9s para compar\u00e1-la com a coluna eleva\u00e7\u00e3o):<\/p>\n
SELECT z AS z_m, z*3.28084 AS z_ft, elevation AS elevation_ft\r\nFROM buildings;<\/pre>\nOs valores est\u00e3o bem pr\u00f3ximos, veja:<\/p>\n
z_m | z_ft | elevation_ft \r\n--------- + ------------------ + --------------- \r\n 438,128 | 1.437,42663560303 | 1434.43000000 \r\n 433,556 | 1.422,4291678418 | 1413.63200000 \r\n 435,489 | 1.428,76987573853 | 1406.25400000 \r\n 439,244 | 1.441,09014682129 | 1422.58200000 \r\n 433,738 | 1.423,02460105347 | 1416.93600000 \r\n 429,648 | 1.409,60687857788 | 1403.92400000 \r\n 437,264 | 1.434,59264585083 | 1425.84000000 \r\n 430,607 | 1.412,75115040894 | 1404.03675300<\/pre>\nVamos colocar a diferen\u00e7a de eleva\u00e7\u00e3o (em metros) na tabela, veja como:<\/p>\n
-- Add column for our calculated height\r\nALTER TABLE buildings ADD COLUMN height real;\r\n-- Update the building heights by subtracting elevation of\r\n-- the nearest street from the elevation of the building\r\nUPDATE buildings SET height = heights.height\r\nFROM (\r\n WITH candidates AS (\r\n SELECT\r\n b.gid AS building_gid,\r\n s.gid AS street_gid,\r\n s.z AS streets_z,\r\n b.z as buildings_z\r\n FROM buildings b, streets s\r\n WHERE ST_DWithin(b.geom, s.geom, 0.001)\r\n ORDER BY\r\n building_gid,\r\n ST_Distance(b.geom, s.geom)\r\n )\r\n SELECT DISTINCT ON (building_gid)\r\n building_gid, street_gid,\r\n buildings_z - streets_z AS height\r\n FROM candidates\r\n) AS heights\r\nWHERE heights.building_gid = buildings.gid;<\/pre>\nNo pr\u00f3ximo post, finalizaremos nossa s\u00e9rie de posts sobre LiDAR, n\u00e3o perca!<\/p>\n","protected":false},"excerpt":{"rendered":"
Para o nosso exemplo de hoje vamos dar um zoom e encontrar um edif\u00edcio para analisar (se voc\u00ea aumenta o zoom, os edif\u00edcios se tornam clic\u00e1veis). Podemos ver que o edif\u00edcio #81719 (identificador da chave prim\u00e1ria do banco de dados)… Continue Reading →<\/a><\/p>\n","protected":false},"author":275,"featured_media":6134,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[268],"class_list":["post-6116","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-gis","tag-lidar"],"_links":{"self":[{"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/posts\/6116","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/users\/275"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/comments?post=6116"}],"version-history":[{"count":14,"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/posts\/6116\/revisions"}],"predecessor-version":[{"id":6204,"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/posts\/6116\/revisions\/6204"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/media\/6134"}],"wp:attachment":[{"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/media?parent=6116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/categories?post=6116"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fernandoquadro.com.br\/html\/wp-json\/wp\/v2\/tags?post=6116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}