Writing /home/gribeiro/www/data/cache/3/309639449766bb12d28acad1a035c2aa.metadata failed
Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | |||
wiki:newbdgeo:consultas-espaciais [2018/06/29 07:51] gribeiro [Consultas Espaciais] |
wiki:newbdgeo:consultas-espaciais [2018/06/29 07:57] (current) gribeiro [Consultas Espaciais] |
||
---|---|---|---|
Line 107: | Line 107: | ||
</code> | </code> | ||
+ | Contando o número de focos de queimadas detectados em cada município do estado de Goiás: | ||
<code sql> | <code sql> | ||
- | SELECT co_5mu500gc.gid, co_5mu500gc.nome, COUNT(*) AS total_focos | + | SELECT co_5mu500gc.gid, co_5mu500gc.nome, COUNT(*) AS total_focos |
- | FROM co_5mu500gc, focos | + | FROM co_5mu500gc, focos |
- | WHERE ST_Contains(co_5mu500gc.geom, focos.geom) | + | WHERE ST_Contains(co_5mu500gc.geom, focos.geom) |
- | and co_5mu500gc.uf='GO' | + | AND co_5mu500gc.uf='GO' |
GROUP BY co_5mu500gc.gid, co_5mu500gc.nome | GROUP BY co_5mu500gc.gid, co_5mu500gc.nome | ||
- | ORDER BY COUNT(*) DESC | + | ORDER BY total_focos DESC |
</code> | </code> | ||
+ | Partindo da consulta anterior, podemos fazer uma nova consulta que inclua a geometria dos municípios: | ||
<code sql> | <code sql> | ||
- | SELECT co_5mu500gc.gid, co_5mu500gc.nome, total_focos , co_5mu500gc.geom | + | SELECT co_5mu500gc.gid, co_5mu500gc.nome, total_focos, co_5mu500gc.geom |
- | FROM co_5mu500gc LEFT JOIN | + | FROM co_5mu500gc LEFT JOIN |
- | ( SELECT co_5mu500gc.gid, co_5mu500gc.nome, COUNT(*) AS total_focos | + | ( SELECT co_5mu500gc.gid, co_5mu500gc.nome, COUNT(*) AS total_focos |
- | FROM co_5mu500gc, focos | + | FROM co_5mu500gc, focos |
- | WHERE ST_Contains(co_5mu500gc.geom, focos.geom) | + | WHERE ST_Contains(co_5mu500gc.geom, focos.geom) |
- | and co_5mu500gc.uf='GO' | + | AND co_5mu500gc.uf='GO' |
- | GROUP BY co_5mu500gc.gid, co_5mu500gc.nome | + | GROUP BY co_5mu500gc.gid, co_5mu500gc.nome |
- | ORDER BY COUNT(*) DESC ) AS result | + | ORDER BY total_focos DESC ) AS result |
ON (co_5mu500gc.gid = result.gid) | ON (co_5mu500gc.gid = result.gid) | ||
</code> | </code> | ||
+ | Calculando o comprimento das estradas que passam no município de ''Goiânia'': | ||
<code sql> | <code sql> | ||
SELECT malha_viaria.gid, malha_viaria.tipo, | SELECT malha_viaria.gid, malha_viaria.tipo, | ||
- | ST_LENGTH( | + | ST_LENGTH( |
- | ST_Transform( | + | ST_Transform( |
- | ST_Intersection(co_5mu500gc.geom, malha_viaria.geom), | + | ST_Intersection(co_5mu500gc.geom, malha_viaria.geom), |
- | 29101 ) ) / 1000.0 AS road_length | + | 29101 ) ) / 1000.0 AS comprimento |
- | FROM co_5mu500gc, malha_viaria | + | FROM co_5mu500gc, malha_viaria |
- | WHERE co_5mu500gc.nome='Goiânia' | + | WHERE co_5mu500gc.nome='Goiânia' |
- | AND ST_Intersects (co_5mu500gc.geom, malha_viaria.geom) | + | AND ST_Intersects(co_5mu500gc.geom, malha_viaria.geom) |
</code> | </code> |