repository for geography work and other things completed by kufre u.
/*finding possible join fields and determining how to join*/
select count(distinct gisjoin),count(gisjoin) from table1940
select count(distinct, gisjoin),count(gisjoin) from tracts1940
/*joining table1940 to tracts1940*/
select*
from tracts1940 AS a left outer join table1940 as b
on a.gisjoin = b.gisjoin
/*joining table1940 to tracts1940 while selecting only new fields from table1940 to prevent duplicates
joins are given aliases (a and b)*/
select a.*, b.poptotal, b.white, b.nonwhite, b.medgrossrent
from tracts1940 as a left outer join table1940 as b
on a.gisjoin = b.gisjoin
/*ordering results*/
select a.*, b.poptotal, b.white, b.nonwhite, b.medgrossrent
from tracts1940 AS a left outer join table1940 as b
on a.gisjoin = b.gisjoin
order by medgrossrent desc
/*removing zero-rent values*/
select a.*, b.poptotal, b.white, b.nonwhite, b.medgrossrent
from tracts1940 as a left outer join table1940 AS b
on a.gisjoin = b.gisjoin
where medgrossrent>0
order by medgrossrent desc
/*creating a view of the query which can be viewed in qgis*/
create view join1940 as
select a.*, b.poptotal, b.white, b.nonwhite, b.medgrossrent
from tracts1940 as a LEFT OUTER join table1940 as b
on a.gisjoin = b.gisjoin
where medgrossrent>0
order by medgrossrent desc
/*calculating direction*/
select *,
degrees(ST_AZIMUTH((select st_centroid(st_transform(geom, 3395)) from cbd), st_centroid(st_transform(geom,3395)))) as cbddir
from tracts1940
/*calculating geodesic distance with geography data type*/
select *,
st_distance((select st_centroid(geography(st_transform(geom, 4326))) from cbd), st_centroid(geography(st_transform(geom,4326)))) as cbddist
from tracts1940
/*switching around the points for whatever reason while calculating distance*/
select *,
st_distance(st_centroid(geography(st_transform(geom,4326))),(select st_centroid(geography(ST_TRANSFORM(geom, 4326))) from cbd)) as cbddist
from tracts1940
/*combining distance and direction calculations in one query and creating a layer*/
select *,
degrees(ST_AZIMUTH((select st_centroid(st_transform(geom, 3395)) from cbd), st_centroid(st_transform(geom,3395)))) as cbddir,
st_distance((select st_centroid(geography(st_transform(geom, 4326))) from cbd), st_centroid(geography(st_transform(geom,4326)))) as cbddist
from tracts1940
/*selecting an new version of cbd with proper projection*/
select id, st_transform(geom,3528) as geom
from cbd
/*makng selection a permenant layer*/
create table cbd3528 as
select id, st_transform(geom,3528) as geom
from cbd
/*creating 5 km buffer around cbd*/
create view cbd5km as
select id, st_buffer(geom,5000) as geom
from cbd3528
/*seeing which tracts intersect with the 5km buffer*/
select*
from join1940
where st_intersects(geom,(st_buffer((select geom from cbd3528),5000)))
/*practicing aggregate functions*/
select count(id) as countID, min(medgrossrent) as minRent, avg(medgrossrent) as avgRent, max(medgrossrent) as maxRent
from cbd1940
/*union*/
create view agg_cbd1940 as
select 1 as id, count(id) as countID, min(medgrossrent) as minRent, avg(medgrossrent) as avgRent, max(medgrossrent) as maxRent, st_union(geom) as geom
from cbd1940