13. Spatiala sammanfogningar¶
Spatial joins är själva grundbulten i spatiala databaser. De gör att du kan kombinera information från olika tabeller genom att använda spatiala relationer som länknyckel. Mycket av det vi tänker på som ”standard GIS-analys” kan uttryckas som spatiala joins.
I föregående avsnitt utforskade vi spatiala relationer med hjälp av en tvåstegsprocess: först extraherade vi en tunnelbanestationspunkt för ”Broad St”; sedan använde vi den punkten för att ställa ytterligare frågor som ”i vilket område ligger stationen ’Broad St’?”
Med hjälp av en spatial join kan vi besvara frågan i ett steg och hämta information om tunnelbanestationen och det område där den ligger:
SELECT
subways.name AS subway_name,
neighborhoods.name AS neighborhood_name,
neighborhoods.boroname AS borough
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_subway_stations AS subways
ON ST_Contains(neighborhoods.geom, subways.geom)
WHERE subways.name = 'Broad St';
subway_name | neighborhood_name | borough
-------------+--------------------+-----------
Broad St | Financial District | Manhattan
Vi kunde ha länkat varje tunnelbanestation till dess närområde, men i det här fallet ville vi bara ha information om en station. Alla funktioner som ger en sann/falsk relation mellan två tabeller kan användas för att driva en spatial join, men de vanligaste är: ST_Intersects, ST_Contains och ST_DWithin.
13.1. Sammanfoga och sammanfatta¶
Kombinationen av en JOIN med en GROUP BY ger den typ av analys som vanligtvis görs i ett GIS-system.
Till exempel: ”Hur ser befolkningen och rassammansättningen ut i stadsdelarna på Manhattan?” Här har vi en fråga som kombinerar information om befolkning från folkräkningen med stadsdelarnas gränser, med en begränsning till bara en stadsdel på Manhattan.
SELECT
neighborhoods.name AS neighborhood_name,
Sum(census.popn_total) AS population,
100.0 * Sum(census.popn_white) / Sum(census.popn_total) AS white_pct,
100.0 * Sum(census.popn_black) / Sum(census.popn_total) AS black_pct
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_census_blocks AS census
ON ST_Intersects(neighborhoods.geom, census.geom)
WHERE neighborhoods.boroname = 'Manhattan'
GROUP BY neighborhoods.name
ORDER BY white_pct DESC;
neighborhood_name | population | white_pct | black_pct
---------------------+------------+-----------+-----------
Carnegie Hill | 18763 | 90.1 | 1.4
North Sutton Area | 22460 | 87.6 | 1.6
West Village | 26718 | 87.6 | 2.2
Upper East Side | 203741 | 85.0 | 2.7
Soho | 15436 | 84.6 | 2.2
Greenwich Village | 57224 | 82.0 | 2.4
Central Park | 46600 | 79.5 | 8.0
Tribeca | 20908 | 79.1 | 3.5
Gramercy | 104876 | 75.5 | 4.7
Murray Hill | 29655 | 75.0 | 2.5
Chelsea | 61340 | 74.8 | 6.4
Upper West Side | 214761 | 74.6 | 9.2
Midtown | 76840 | 72.6 | 5.2
Battery Park | 17153 | 71.8 | 3.4
Financial District | 34807 | 69.9 | 3.8
Clinton | 32201 | 65.3 | 7.9
East Village | 82266 | 63.3 | 8.8
Garment District | 10539 | 55.2 | 7.1
Morningside Heights | 42844 | 52.7 | 19.4
Little Italy | 12568 | 49.0 | 1.8
Yorkville | 58450 | 35.6 | 29.7
Inwood | 50047 | 35.2 | 16.8
Washington Heights | 169013 | 34.9 | 16.8
Lower East Side | 96156 | 33.5 | 9.1
East Harlem | 60576 | 26.4 | 40.4
Hamilton Heights | 67432 | 23.9 | 35.8
Chinatown | 16209 | 15.2 | 3.8
Harlem | 134955 | 15.1 | 67.1
Vad är det som händer här? Notionellt (den faktiska utvärderingsordningen optimeras under täckmantel av databasen) är detta vad som händer:
Klausulen
JOINskapar en virtuell tabell som innehåller kolumner från både grannskaps- och folkräkningstabellerna.Klausulen
WHEREfiltrerar vår virtuella tabell till bara rader på Manhattan.De återstående raderna grupperas efter grannskapsnamnet och matas genom aggregeringsfunktionen till Sum() populationsvärdena.
Efter lite aritmetik och formatering (t.ex.
GROUP BY,ORDER BY) på de slutliga siffrorna spottar vår fråga ut procentsatserna.
Observera
Klausulen JOIN kombinerar två FROM-objekt. Som standard använder vi en INNER JOIN, men det finns fyra andra typer av sammanfogningar. För mer information se definitionen av join_type i PostgreSQL-dokumentationen.
Vi kan också använda avståndstester som en join-nyckel för att skapa sammanfattade ”alla objekt inom en radie”-frågor. Låt oss utforska New Yorks rasgeografi med hjälp av avståndsfrågor.
Låt oss först ta reda på hur stadens rasmässiga sammansättning ser ut.
SELECT
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
Sum(popn_total) AS popn_total
FROM nyc_census_blocks;
white_pct | black_pct | popn_total
------------------+------------------+------------
44.0039500762811 | 25.5465789002416 | 8175032
Så av de 8 miljoner människorna i New York registreras cirka 44% som ”vita” och 26% som ”svarta”.
Duke Ellington sjöng en gång att ”Du / måste ta A-tåget / för att / åka till Sugar Hill långt uppe i Harlem.” Som vi såg tidigare har Harlem den överlägset högsta andelen afroamerikaner på Manhattan (80,5%). Gäller samma sak för Dukes A-train?
Observera först att det är innehållet i fältet routes i tabellen nyc_subway_stations som vi är intresserade av för att hitta A-tåget. Värdena där inne är lite komplexa.
SELECT DISTINCT routes FROM nyc_subway_stations;
A,C,G
4,5
D,F,N,Q
5
E,F
E,J,Z
R,W
Observera
Nyckelordet DISTINCT eliminerar duplicerade rader från resultatet. Utan nyckelordet DISTINCT identifierar frågan ovan 491 resultat istället för 73.
Så för att hitta A-tåget vill vi ha alla rader i routes som har ett ’A’ i sig. Vi kan göra detta på ett antal olika sätt, men idag kommer vi att använda det faktum att strpos(routes,'A') kommer att returnera ett tal som inte är noll endast om ’A’ finns i fältet routes.
SELECT DISTINCT routes
FROM nyc_subway_stations AS subways
WHERE strpos(subways.routes,'A') > 0;
A,B,C
A,C
A
A,C,G
A,C,E,L
A,S
A,C,F
A,B,C,D
A,C,E
Låt oss sammanfatta rasblandningen inom 200 meter från A-tågslinjen.
SELECT
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
WHERE strpos(subways.routes,'A') > 0;
white_pct | black_pct | popn_total
------------------+------------------+------------
45.5901255900202 | 22.0936235670937 | 189824
Så rassammansättningen längs A-train skiljer sig inte radikalt från sammansättningen i New York City som helhet.
13.2. Avancerad join¶
I förra avsnittet såg vi att A-tåget inte trafikerade en befolkning som skilde sig mycket från rassammansättningen i resten av staden. Finns det några tåg som har en rassammansättning som avviker från genomsnittet?
För att svara på den frågan lägger vi till ytterligare en länk i vår fråga, så att vi samtidigt kan beräkna sammansättningen av många tunnelbanelinjer på en gång. För att göra det måste vi skapa en ny tabell som räknar upp alla de linjer vi vill sammanfatta.
CREATE TABLE subway_lines ( route char(1) );
INSERT INTO subway_lines (route) VALUES
('A'),('B'),('C'),('D'),('E'),('F'),('G'),
('J'),('L'),('M'),('N'),('Q'),('R'),('S'),
('Z'),('1'),('2'),('3'),('4'),('5'),('6'),
('7');
Nu kan vi koppla tabellen med tunnelbanelinjer till vår ursprungliga fråga.
SELECT
lines.route,
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
JOIN subway_lines AS lines
ON strpos(subways.routes, lines.route) > 0
GROUP BY lines.route
ORDER BY black_pct DESC;
route | white_pct | black_pct | popn_total
-------+-----------+-----------+------------
S | 39.8 | 46.5 | 33301
3 | 42.7 | 42.1 | 223047
5 | 33.8 | 41.4 | 218919
2 | 39.3 | 38.4 | 291661
C | 46.9 | 30.6 | 224411
4 | 37.6 | 27.4 | 174998
B | 40.0 | 26.9 | 256583
A | 45.6 | 22.1 | 189824
J | 37.6 | 21.6 | 132861
Q | 56.9 | 20.6 | 127112
Z | 38.4 | 20.2 | 87131
D | 39.5 | 19.4 | 234931
L | 57.6 | 16.8 | 110118
G | 49.6 | 16.1 | 135012
6 | 52.3 | 15.7 | 260240
1 | 59.1 | 11.3 | 327742
F | 60.9 | 7.5 | 229439
M | 56.5 | 6.4 | 174196
E | 66.8 | 4.7 | 90958
R | 58.5 | 4.0 | 196999
N | 59.7 | 3.5 | 147792
7 | 35.7 | 3.5 | 102401
Som tidigare skapar sammanfogningarna en virtuell tabell med alla möjliga kombinationer som är tillgängliga inom ramen för begränsningarna i JOIN ON, och dessa rader matas sedan in i en GROUP-sammanfattning. Den spatiala magin ligger i funktionen ST_DWithin, som säkerställer att endast folkbokföringsblock nära de lämpliga tunnelbanestationerna ingår i beräkningen.
13.3. Funktionslista¶
ST_Contains(geometri A, geometri B): Returnerar sant om och endast om inga punkter i B ligger i utsidan av A, och minst en punkt i insidan av B ligger i insidan av A.
ST_DWithin(geometri A, geometri B, radie): Returnerar sant om geometrierna ligger inom det angivna avståndet från varandra.
ST_Intersects(geometri A, geometri B): Returnerar TRUE om geometrierna/geografin ”spatialt korsar varandra” - (delar någon del av utrymmet) och FALSE om de inte gör det (de är disjunkta).
round(v numerisk, s heltal): PostgreSQL-matematisk funktion som avrundar till s decimaler
strpos (sträng, delsträng): PostgreSQL-strängfunktion som returnerar en heltalsplats för en angiven delsträng.
sum(uttryck): PostgreSQL-aggregeringsfunktion som returnerar summan av poster i en uppsättning poster.
