Skip to content

Instantly share code, notes, and snippets.

@AlexArcPy

AlexArcPy/sql_server_spatial_functions_p2.ipynb

Created February 17, 2018 07:24
Show Gist options
  • Save AlexArcPy/b1c3709a0d8faa5c7a157840ce6e66ba to your computer and use it in GitHub Desktop.
Save AlexArcPy/b1c3709a0d8faa5c7a157840ce6e66ba to your computer and use it in GitHub Desktop.
Download ZIP
SQL Server spatial functions for GIS users: part 2
Display the source blob
Display the rendered blob
Raw
sql_server_spatial_functions_p2.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Find points that have the same same coordinates."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To find coincident points (often referred to as duplicates), you could use various SQL Server spatial functions."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"Table1.Shape.STDistance(Table2.Shape) < 1000 --distance value\n",
"Table1.Shape.STEquals(Table2.Shape) = 1 --whether shapes are identical\n",
"Table1.SHAPE.STX and Table1.SHAPE.STY --compare points XY coordinates\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"You can find the SQL snippets for each of the spatial function below."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Find using `STDistance`"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>FirstPoint</th>\n",
" <th>SecondPoint</th>\n",
" <th>Distance</th>\n",
" </tr>\n",
" <tr>\n",
" <td>41</td>\n",
" <td>2556</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>454</td>\n",
" <td>1199</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>667</td>\n",
" <td>1853</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>783</td>\n",
" <td>1937</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>907</td>\n",
" <td>1158</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1282</td>\n",
" <td>1544</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1690</td>\n",
" <td>1834</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2264</td>\n",
" <td>2344</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2513</td>\n",
" <td>3350</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2664</td>\n",
" <td>3304</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3148</td>\n",
" <td>3448</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(41, 2556, 0.0),\n",
" (454, 1199, 0.0),\n",
" (667, 1853, 0.0),\n",
" (783, 1937, 0.0),\n",
" (907, 1158, 0.0),\n",
" (1282, 1544, 0.0),\n",
" (1690, 1834, 0.0),\n",
" (2264, 2344, 0.0),\n",
" (2513, 3350, 0.0),\n",
" (2664, 3304, 0.0),\n",
" (3148, 3448, 0.0)]"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"--Find duplicate points within a certain borough\n",
"SELECT CAST(T1.ID AS INT) AS FirstPoint, \n",
" CAST(T2.ID AS INT) SecondPoint,\n",
" T1.Shape.STDistance(T2.Shape) Distance\n",
"FROM \n",
" dbo.Homicides T1\n",
"JOIN \n",
" dbo.Homicides T2\n",
"ON \n",
" T1.ID < T2.ID \n",
"and \n",
" T1.Shape.STDistance(T2.Shape) = 0\n",
"and\n",
" T1.BORONAME = 'Queens'\n",
"ORDER BY \n",
" Distance, FirstPoint"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Find using `STEquals`"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>FirstPointId</th>\n",
" <th>SecondPointId</th>\n",
" <th>Distance</th>\n",
" </tr>\n",
" <tr>\n",
" <td>41</td>\n",
" <td>2556</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>454</td>\n",
" <td>1199</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>667</td>\n",
" <td>1853</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>783</td>\n",
" <td>1937</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>907</td>\n",
" <td>1158</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1282</td>\n",
" <td>1544</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1690</td>\n",
" <td>1834</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1954</td>\n",
" <td>2016</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2264</td>\n",
" <td>2344</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2513</td>\n",
" <td>3350</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2664</td>\n",
" <td>3304</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3148</td>\n",
" <td>3448</td>\n",
" <td>0.0</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(41, 2556, 0.0),\n",
" (454, 1199, 0.0),\n",
" (667, 1853, 0.0),\n",
" (783, 1937, 0.0),\n",
" (907, 1158, 0.0),\n",
" (1282, 1544, 0.0),\n",
" (1690, 1834, 0.0),\n",
" (1954, 2016, None),\n",
" (2264, 2344, 0.0),\n",
" (2513, 3350, 0.0),\n",
" (2664, 3304, 0.0),\n",
" (3148, 3448, 0.0)]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT CAST(T1.ID AS INT) AS FirstPointId, \n",
" CAST(T2.ID AS INT) SecondPointId,\n",
" T1.Shape.STDistance(T2.Shape) Distance\n",
"FROM \n",
" dbo.Homicides T1\n",
"JOIN \n",
" dbo.Homicides T2\n",
"ON \n",
" T1.ID < T2.ID \n",
"and \n",
" T1.Shape.STEquals(T2.Shape) = 1\n",
"and\n",
" T1.BORONAME = 'Queens'\n",
"ORDER BY \n",
" FirstPointId, SecondPointId"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Find using `STX` and `STY`"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>STX</th>\n",
" <th>STY</th>\n",
" <th>COUNT</th>\n",
" </tr>\n",
" <tr>\n",
" <td>602774.302</td>\n",
" <td>4493937.5897</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>602825.2922</td>\n",
" <td>4494640.0928</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>596550.9795</td>\n",
" <td>4502936.1456</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>605123.2532</td>\n",
" <td>4503245.6874</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>602838.3109</td>\n",
" <td>4504421.8048</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>600693.1724</td>\n",
" <td>4504890.5882</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>602224.2498</td>\n",
" <td>4505601.7733</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>600910.3951</td>\n",
" <td>4506349.8886</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>595719.1188</td>\n",
" <td>4509615.4108</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>595286.8248</td>\n",
" <td>4511502.8696</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>592796.9457</td>\n",
" <td>4512679.5541</td>\n",
" <td>2</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(602774.3020000001, 4493937.5897, 2),\n",
" (602825.2922, 4494640.092800001, 2),\n",
" (596550.9795000004, 4502936.1456, 2),\n",
" (605123.2532000002, 4503245.6874, 2),\n",
" (602838.3108999999, 4504421.8048, 2),\n",
" (600693.1723999996, 4504890.588199999, 2),\n",
" (602224.2498000003, 4505601.7733, 2),\n",
" (600910.3951000003, 4506349.888599999, 2),\n",
" (595719.1188000003, 4509615.410800001, 2),\n",
" (595286.8247999996, 4511502.8696, 2),\n",
" (592796.9457, 4512679.554099999, 2)]"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT T1.SHAPE.STX, T1.SHAPE.STY, COUNT(*) AS COUNT\n",
"FROM \n",
" dbo.Homicides T1\n",
"WHERE\n",
" T1.BORONAME = 'Queens'\n",
"GROUP BY\n",
" T1.SHAPE.STX, T1.SHAPE.STY \n",
"HAVING\n",
" COUNT(*) > 1"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Calculating distances between points stored in the same table\n",
"The `STDistance` function could be used to find the plain distance between the points stored within the same table."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Calculating distances between points stored in the same table"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>FirstPointId</th>\n",
" <th>SecondPointId</th>\n",
" <th>Distance</th>\n",
" </tr>\n",
" <tr>\n",
" <td>972</td>\n",
" <td>3003</td>\n",
" <td>10.3773202898</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2598</td>\n",
" <td>3641</td>\n",
" <td>11.18755367</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1097</td>\n",
" <td>1387</td>\n",
" <td>11.4740440673</td>\n",
" </tr>\n",
" <tr>\n",
" <td>75</td>\n",
" <td>867</td>\n",
" <td>12.3301276201</td>\n",
" </tr>\n",
" <tr>\n",
" <td>480</td>\n",
" <td>2040</td>\n",
" <td>12.770305058</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2323</td>\n",
" <td>3470</td>\n",
" <td>14.1666929496</td>\n",
" </tr>\n",
" <tr>\n",
" <td>650</td>\n",
" <td>2950</td>\n",
" <td>14.4003381708</td>\n",
" </tr>\n",
" <tr>\n",
" <td>517</td>\n",
" <td>599</td>\n",
" <td>15.4921521419</td>\n",
" </tr>\n",
" <tr>\n",
" <td>431</td>\n",
" <td>1734</td>\n",
" <td>16.4238534264</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1506</td>\n",
" <td>3125</td>\n",
" <td>17.1821124569</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1278</td>\n",
" <td>4030</td>\n",
" <td>17.691336767</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(972, 3003, 10.377320289834147),\n",
" (2598, 3641, 11.187553669957039),\n",
" (1097, 1387, 11.47404406734983),\n",
" (75, 867, 12.330127620112625),\n",
" (480, 2040, 12.770305058004253),\n",
" (2323, 3470, 14.166692949561648),\n",
" (650, 2950, 14.400338170755509),\n",
" (517, 599, 15.492152141922965),\n",
" (431, 1734, 16.423853426430476),\n",
" (1506, 3125, 17.182112456944918),\n",
" (1278, 4030, 17.69133676698391)]"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT CAST(T1.ID AS INT) AS FirstPointId\n",
" ,CAST(T2.ID AS INT) SecondPointId\n",
" ,T1.Shape.STDistance(T2.Shape) Distance\n",
"FROM \n",
" dbo.Homicides T1\n",
"JOIN dbo.Homicides T2\n",
" ON T1.ID < T2.ID \n",
"and \n",
" T1.Shape.STDistance(T2.Shape) BETWEEN 10 AND 20\n",
"and\n",
" T1.BORONAME = 'Queens'\n",
"ORDER BY \n",
" Distance"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Calculating distances between points stored in two tables\n",
"Find homicide points that are located within the specified number of meters to the subway stations points. ArcGIS tool: Point Distance (Analysis)"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>HomicideId</th>\n",
" <th>Weapon</th>\n",
" <th>SubwayId</th>\n",
" <th>Name</th>\n",
" <th>Distance</th>\n",
" </tr>\n",
" <tr>\n",
" <td>786</td>\n",
" <td>knife</td>\n",
" <td>40</td>\n",
" <td>116th St</td>\n",
" <td>0.816725052629</td>\n",
" </tr>\n",
" <tr>\n",
" <td>822</td>\n",
" <td>knife</td>\n",
" <td>40</td>\n",
" <td>116th St</td>\n",
" <td>0.816725052629</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1212</td>\n",
" <td>gun</td>\n",
" <td>11</td>\n",
" <td>23rd St</td>\n",
" <td>1.12019912609</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2442</td>\n",
" <td> </td>\n",
" <td>109</td>\n",
" <td>Astor Pl</td>\n",
" <td>1.76846230372</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3691</td>\n",
" <td>knife</td>\n",
" <td>365</td>\n",
" <td>57th St</td>\n",
" <td>2.26028966743</td>\n",
" </tr>\n",
" <tr>\n",
" <td>130</td>\n",
" <td>other</td>\n",
" <td>122</td>\n",
" <td>110th St</td>\n",
" <td>2.54464224595</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3609</td>\n",
" <td>knife</td>\n",
" <td>121</td>\n",
" <td>103rd St</td>\n",
" <td>2.93132472018</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2139</td>\n",
" <td>blunt_instrument</td>\n",
" <td>194</td>\n",
" <td>116th St</td>\n",
" <td>3.14927126175</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2787</td>\n",
" <td>gun</td>\n",
" <td>122</td>\n",
" <td>110th St</td>\n",
" <td>3.31737575926</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1577</td>\n",
" <td>gun</td>\n",
" <td>10</td>\n",
" <td>18th St</td>\n",
" <td>3.97940337807</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3373</td>\n",
" <td>knife</td>\n",
" <td>197</td>\n",
" <td>145th St</td>\n",
" <td>5.5006227465</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3833</td>\n",
" <td>knife</td>\n",
" <td>7</td>\n",
" <td>Houston St</td>\n",
" <td>10.3234191074</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(786, u'knife', 40, u'116th St', 0.8167250526287405),\n",
" (822, u'knife', 40, u'116th St', 0.8167250526287405),\n",
" (1212, u'gun', 11, u'23rd St', 1.1201991260949775),\n",
" (2442, u' ', 109, u'Astor Pl', 1.7684623037161311),\n",
" (3691, u'knife', 365, u'57th St', 2.2602896674283146),\n",
" (130, u'other', 122, u'110th St', 2.5446422459498343),\n",
" (3609, u'knife', 121, u'103rd St', 2.931324720179847),\n",
" (2139, u'blunt_instrument', 194, u'116th St', 3.149271261745439),\n",
" (2787, u'gun', 122, u'110th St', 3.31737575926436),\n",
" (1577, u'gun', 10, u'18th St', 3.9794033780719094),\n",
" (3373, u'knife', 197, u'145th St', 5.500622746504136),\n",
" (3833, u'knife', 7, u'Houston St', 10.323419107439927)]"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT * FROM\n",
"(SELECT CAST(Homi.ID AS int) AS HomicideId\n",
" ,Homi.WEAPON AS Weapon\n",
" ,CAST(Subway.ID AS int) AS SubwayId\n",
" ,Subway.NAME AS Name\n",
" ,Homi.Shape.STDistance(Subway.Shape) AS Distance\n",
" FROM dbo.HOMICIDES Homi\n",
" cross join dbo.Subway_stations Subway \n",
" where Homi.BORONAME = 'Manhattan' AND Subway.BOROUGH = 'Manhattan') \n",
"AS\n",
" data\n",
"WHERE \n",
" Distance < 20\n",
"ORDER BY\n",
" Distance"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Counting points in polygons"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To count points in polygons, you could use the `STContains` function."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```\n",
"Table1.Shape.STContains(Table2.Shape) -> 0/1\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Find neighborhoods with the largest number of crimes commited (count number of homicides in each neighborhood)."
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>NeighborhoodName</th>\n",
" <th>CrimeCount</th>\n",
" </tr>\n",
" <tr>\n",
" <td>Bedford-Stuyvesant</td>\n",
" <td>375</td>\n",
" </tr>\n",
" <tr>\n",
" <td>South Bronx</td>\n",
" <td>191</td>\n",
" </tr>\n",
" <tr>\n",
" <td>East Brooklyn</td>\n",
" <td>162</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Brownsville</td>\n",
" <td>149</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Harlem</td>\n",
" <td>145</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Jamaica</td>\n",
" <td>128</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Tremont</td>\n",
" <td>104</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Bushwick</td>\n",
" <td>96</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Fort Green</td>\n",
" <td>93</td>\n",
" </tr>\n",
" <tr>\n",
" <td>Wakefield-Williamsbridge</td>\n",
" <td>92</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(u'Bedford-Stuyvesant', 375),\n",
" (u'South Bronx', 191),\n",
" (u'East Brooklyn', 162),\n",
" (u'Brownsville', 149),\n",
" (u'Harlem', 145),\n",
" (u'Jamaica', 128),\n",
" (u'Tremont', 104),\n",
" (u'Bushwick', 96),\n",
" (u'Fort Green', 93),\n",
" (u'Wakefield-Williamsbridge', 92)]"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT TOP 10 \n",
" Polys.Name AS NeighborhoodName, Count(*) AS CrimeCount\n",
"FROM \n",
" dbo.Homicides AS Points\n",
"JOIN \n",
" dbo.Neighborhoods AS Polys\n",
"ON \n",
" Polys.Shape.STContains(Points.Shape) = 1\n",
"GROUP BY \n",
" Polys.Name\n",
"ORDER BY\n",
" CrimeCount DESC"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can also calculate a column in the Neighborhoods table to contain the number of points within each neighborhood. For that, we will first need to add a new column to the table, then populate it, and then drop to leave the data clean for the further queries.\n",
"\n",
"This query adding a new fields and calculating the number of points located within each polygon is what is done by the ArcGIS GP tool Spatial Join."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Add a column to be populated"
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"ALTER TABLE dbo.Neighborhoods\n",
"ADD PointCount int;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Update the column"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"107 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"UPDATE \n",
" Polys\n",
"SET \n",
" [PointCount] = COUNTS.CrimeCount\n",
"FROM \n",
" dbo.Neighborhoods AS Polys\n",
"JOIN\n",
"(\n",
" SELECT\n",
" Polys.Name AS NeighborhoodName, Count(*) AS CrimeCount\n",
" FROM \n",
" dbo.Homicides AS Points\n",
" JOIN \n",
" dbo.Neighborhoods AS Polys\n",
" ON \n",
" Polys.Shape.STContains(Points.Shape) = 1\n",
" GROUP BY \n",
" Polys.Name\n",
" ) AS COUNTS \n",
"ON \n",
" Polys.Name = COUNTS.NeighborhoodName"
]
},
{
"cell_type": "code",
"execution_count": 37,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>OBJECTID</th>\n",
" <th>Name</th>\n",
" <th>PointCount</th>\n",
" </tr>\n",
" <tr>\n",
" <td>116</td>\n",
" <td>Bedford-Stuyvesant</td>\n",
" <td>375</td>\n",
" </tr>\n",
" <tr>\n",
" <td>111</td>\n",
" <td>South Bronx</td>\n",
" <td>191</td>\n",
" </tr>\n",
" <tr>\n",
" <td>43</td>\n",
" <td>East Brooklyn</td>\n",
" <td>162</td>\n",
" </tr>\n",
" <tr>\n",
" <td>87</td>\n",
" <td>Brownsville</td>\n",
" <td>149</td>\n",
" </tr>\n",
" <tr>\n",
" <td>11</td>\n",
" <td>Harlem</td>\n",
" <td>145</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(116, u'Bedford-Stuyvesant', 375),\n",
" (111, u'South Bronx', 191),\n",
" (43, u'East Brooklyn', 162),\n",
" (87, u'Brownsville', 149),\n",
" (11, u'Harlem', 145)]"
]
},
"execution_count": 37,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"select Top 5 OBJECTID, Name, PointCount from dbo.NEIGHBORHOODS\n",
"where PointCount is not null\n",
"order by PointCount desc"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Drop the column"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql \n",
"ALTER TABLE dbo.Neighborhoods \n",
"DROP COLUMN PointCount"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Add polygon name to points located within the polygon"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To enrich the points layer with the information what polygon each point is located within you would need to use the `STWithin` function. In this example, we will add a new column to the homicides table so we know what neighborhood the crime has been commited. \n",
"\n",
"Again, this query adding a new field and calculating the neighborhood name for the points located within each polygon is what is done by the ArcGIS GP tool Spatial Join."
]
},
{
"cell_type": "code",
"execution_count": 42,
"metadata": {
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>OBJECTID</th>\n",
" <th>INCIDENT_D</th>\n",
" <th>BORONAME</th>\n",
" <th>NUM_VICTIM</th>\n",
" <th>PRIMARY_MO</th>\n",
" <th>ID</th>\n",
" <th>WEAPON</th>\n",
" <th>LIGHT_DARK</th>\n",
" <th>YEAR</th>\n",
" <th>NeighborhoodName</th>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>2008-01-01 00:00:00.0000000</td>\n",
" <td>Brooklyn</td>\n",
" <td>1</td>\n",
" <td> </td>\n",
" <td>7</td>\n",
" <td>gun</td>\n",
" <td>D</td>\n",
" <td>2008</td>\n",
" <td>Brownsville</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2008-01-04 00:00:00.0000000</td>\n",
" <td>Manhattan</td>\n",
" <td>1</td>\n",
" <td> </td>\n",
" <td>14</td>\n",
" <td>gun</td>\n",
" <td>D</td>\n",
" <td>2008</td>\n",
" <td>Harlem</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3</td>\n",
" <td>2008-01-05 00:00:00.0000000</td>\n",
" <td>Queens</td>\n",
" <td>1</td>\n",
" <td> </td>\n",
" <td>15</td>\n",
" <td>gun</td>\n",
" <td>D</td>\n",
" <td>2008</td>\n",
" <td>Saintalbans</td>\n",
" </tr>\n",
" <tr>\n",
" <td>4</td>\n",
" <td>2008-01-04 00:00:00.0000000</td>\n",
" <td>Queens</td>\n",
" <td>1</td>\n",
" <td> </td>\n",
" <td>16</td>\n",
" <td>knife</td>\n",
" <td>D</td>\n",
" <td>2008</td>\n",
" <td>Jackson Heights</td>\n",
" </tr>\n",
" <tr>\n",
" <td>5</td>\n",
" <td>2008-01-05 00:00:00.0000000</td>\n",
" <td>Queens</td>\n",
" <td>1</td>\n",
" <td> </td>\n",
" <td>18</td>\n",
" <td>gun</td>\n",
" <td>D</td>\n",
" <td>2008</td>\n",
" <td>The Rockaways</td>\n",
" </tr>\n",
" <tr>\n",
" <td>6</td>\n",
" <td>2008-01-07 00:00:00.0000000</td>\n",
" <td>Brooklyn</td>\n",
" <td>1</td>\n",
" <td> </td>\n",
" <td>20</td>\n",
" <td>gun</td>\n",
" <td>D</td>\n",
" <td>2008</td>\n",
" <td>Bushwick</td>\n",
" </tr>\n",
" <tr>\n",
" <td>7</td>\n",
" <td>2008-01-10 00:00:00.0000000</td>\n",
" <td>Manhattan</td>\n",
" <td>1</td>\n",
" <td> </td>\n",
" <td>22</td>\n",
" <td>gun</td>\n",
" <td>D</td>\n",
" <td>2008</td>\n",
" <td>Chelsea</td>\n",
" </tr>\n",
" <tr>\n",
" <td>8</td>\n",
" <td>2008-01-10 00:00:00.0000000</td>\n",
" <td>Manhattan</td>\n",
" <td>1</td>\n",
" <td> </td>\n",
" <td>23</td>\n",
" <td>gun</td>\n",
" <td>D</td>\n",
" <td>2008</td>\n",
" <td>Upper West Side</td>\n",
" </tr>\n",
" <tr>\n",
" <td>9</td>\n",
" <td>2008-01-13 00:00:00.0000000</td>\n",
" <td>Staten Island</td>\n",
" <td>1</td>\n",
" <td> </td>\n",
" <td>25</td>\n",
" <td>gun</td>\n",
" <td>D</td>\n",
" <td>2008</td>\n",
" <td>Mariners Harbor</td>\n",
" </tr>\n",
" <tr>\n",
" <td>11</td>\n",
" <td>2008-01-21 00:00:00.0000000</td>\n",
" <td>Manhattan</td>\n",
" <td>1</td>\n",
" <td> </td>\n",
" <td>30</td>\n",
" <td>knife</td>\n",
" <td>D</td>\n",
" <td>2008</td>\n",
" <td>Midtown</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(1, u'2008-01-01 00:00:00.0000000', u'Brooklyn', u'1', u' ', Decimal('7'), u'gun', u'D', Decimal('2008'), u'Brownsville'),\n",
" (2, u'2008-01-04 00:00:00.0000000', u'Manhattan', u'1', u' ', Decimal('14'), u'gun', u'D', Decimal('2008'), u'Harlem'),\n",
" (3, u'2008-01-05 00:00:00.0000000', u'Queens', u'1', u' ', Decimal('15'), u'gun', u'D', Decimal('2008'), u'Saintalbans'),\n",
" (4, u'2008-01-04 00:00:00.0000000', u'Queens', u'1', u' ', Decimal('16'), u'knife', u'D', Decimal('2008'), u'Jackson Heights'),\n",
" (5, u'2008-01-05 00:00:00.0000000', u'Queens', u'1', u' ', Decimal('18'), u'gun', u'D', Decimal('2008'), u'The Rockaways'),\n",
" (6, u'2008-01-07 00:00:00.0000000', u'Brooklyn', u'1', u' ', Decimal('20'), u'gun', u'D', Decimal('2008'), u'Bushwick'),\n",
" (7, u'2008-01-10 00:00:00.0000000', u'Manhattan', u'1', u' ', Decimal('22'), u'gun', u'D', Decimal('2008'), u'Chelsea'),\n",
" (8, u'2008-01-10 00:00:00.0000000', u'Manhattan', u'1', u' ', Decimal('23'), u'gun', u'D', Decimal('2008'), u'Upper West Side'),\n",
" (9, u'2008-01-13 00:00:00.0000000', u'Staten Island', u'1', u' ', Decimal('25'), u'gun', u'D', Decimal('2008'), u'Mariners Harbor'),\n",
" (11, u'2008-01-21 00:00:00.0000000', u'Manhattan', u'1', u' ', Decimal('30'), u'knife', u'D', Decimal('2008'), u'Midtown')]"
]
},
"execution_count": 42,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT TOP 10 \n",
" Points.OBJECTID\n",
" ,Points.INCIDENT_D\n",
" ,Points.BORONAME\n",
" ,Points.NUM_VICTIM\n",
" ,Points.PRIMARY_MO\n",
" ,Points.ID\n",
" ,Points.WEAPON\n",
" ,Points.LIGHT_DARK\n",
" ,Points.YEAR \n",
" ,Polys.Name AS NeighborhoodName\n",
"FROM \n",
" dbo.Neighborhoods AS Polys\n",
"JOIN \n",
" dbo.Homicides AS Points\n",
"ON \n",
" Points.Shape.STWithin(Polys.Shape) = 1\n",
"ORDER BY \n",
" OBJECTID"
]
},
{
"cell_type": "code",
"execution_count": 50,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 50,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"ALTER TABLE dbo.Homicides\n",
"ADD NeighborhoodName varchar(50);"
]
},
{
"cell_type": "code",
"execution_count": 52,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"3496 rows affected.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 52,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"UPDATE \n",
" Points\n",
"SET \n",
" [NeighborhoodName] = PointsInPolys.NeighborhoodName\n",
"FROM \n",
" dbo.Homicides AS Points\n",
"JOIN\n",
"(\n",
" SELECT \n",
" Points.OBJECTID\n",
" ,Points.INCIDENT_D\n",
" ,Points.BORONAME\n",
" ,Points.NUM_VICTIM\n",
" ,Points.PRIMARY_MO\n",
" ,Points.ID\n",
" ,Points.WEAPON\n",
" ,Points.LIGHT_DARK\n",
" ,Points.YEAR \n",
" ,Polys.Name AS NeighborhoodName\n",
" FROM \n",
" dbo.Neighborhoods AS Polys\n",
" JOIN \n",
" dbo.Homicides AS Points\n",
" ON \n",
" Points.Shape.STWithin(Polys.Shape) = 1\n",
" ) AS PointsInPolys\n",
"ON \n",
" PointsInPolys.ID = Points.ID"
]
},
{
"cell_type": "code",
"execution_count": 53,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>OBJECTID</th>\n",
" <th>ID</th>\n",
" <th>NeighborhoodName</th>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>7</td>\n",
" <td>Brownsville</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>14</td>\n",
" <td>Harlem</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3</td>\n",
" <td>15</td>\n",
" <td>Saintalbans</td>\n",
" </tr>\n",
" <tr>\n",
" <td>4</td>\n",
" <td>16</td>\n",
" <td>Jackson Heights</td>\n",
" </tr>\n",
" <tr>\n",
" <td>5</td>\n",
" <td>18</td>\n",
" <td>The Rockaways</td>\n",
" </tr>\n",
" <tr>\n",
" <td>6</td>\n",
" <td>20</td>\n",
" <td>Bushwick</td>\n",
" </tr>\n",
" <tr>\n",
" <td>7</td>\n",
" <td>22</td>\n",
" <td>Chelsea</td>\n",
" </tr>\n",
" <tr>\n",
" <td>8</td>\n",
" <td>23</td>\n",
" <td>Upper West Side</td>\n",
" </tr>\n",
" <tr>\n",
" <td>9</td>\n",
" <td>25</td>\n",
" <td>Mariners Harbor</td>\n",
" </tr>\n",
" <tr>\n",
" <td>10</td>\n",
" <td>27</td>\n",
" <td>None</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(1, Decimal('7'), u'Brownsville'),\n",
" (2, Decimal('14'), u'Harlem'),\n",
" (3, Decimal('15'), u'Saintalbans'),\n",
" (4, Decimal('16'), u'Jackson Heights'),\n",
" (5, Decimal('18'), u'The Rockaways'),\n",
" (6, Decimal('20'), u'Bushwick'),\n",
" (7, Decimal('22'), u'Chelsea'),\n",
" (8, Decimal('23'), u'Upper West Side'),\n",
" (9, Decimal('25'), u'Mariners Harbor'),\n",
" (10, Decimal('27'), None)]"
]
},
"execution_count": 53,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT TOP 10 OBJECTID, ID, NeighborhoodName\n",
"FROM dbo.Homicides"
]
},
{
"cell_type": "code",
"execution_count": 49,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 49,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"ALTER TABLE dbo.Homicides\n",
"DROP COLUMN NeighborhoodName;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Summary statistics and frequency\n",
"Frequency GP tool in ArcGIS using a stored procedure"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```sql\n",
"ALTER PROCEDURE dbo.FrequencyTable \n",
" @Columns varchar(500)\n",
"AS\n",
"BEGIN\n",
" EXEC ('SELECT COUNT(*) AS ' + @Columns + \n",
" ' FROM dbo.HOMICIDES \n",
" WHERE WEAPON <> '''' AND LIGHT_DARK <> '''' \n",
" GROUP BY WEAPON, \n",
" LIGHT_DARK ORDER BY FREQUENCY DESC;');\n",
"END\n",
"GO\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Done.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>FREQUENCY</th>\n",
" <th>WEAPON</th>\n",
" <th>LIGHT_DARK</th>\n",
" </tr>\n",
" <tr>\n",
" <td>787</td>\n",
" <td>gun</td>\n",
" <td>D</td>\n",
" </tr>\n",
" <tr>\n",
" <td>287</td>\n",
" <td>gun</td>\n",
" <td>L</td>\n",
" </tr>\n",
" <tr>\n",
" <td>211</td>\n",
" <td>knife</td>\n",
" <td>D</td>\n",
" </tr>\n",
" <tr>\n",
" <td>141</td>\n",
" <td>knife</td>\n",
" <td>L</td>\n",
" </tr>\n",
" <tr>\n",
" <td>43</td>\n",
" <td>blunt_instrument</td>\n",
" <td>D</td>\n",
" </tr>\n",
" <tr>\n",
" <td>27</td>\n",
" <td>blunt_instrument</td>\n",
" <td>L</td>\n",
" </tr>\n",
" <tr>\n",
" <td>16</td>\n",
" <td>other</td>\n",
" <td>D</td>\n",
" </tr>\n",
" <tr>\n",
" <td>16</td>\n",
" <td>other</td>\n",
" <td>L</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(787, u'gun', u'D'),\n",
" (287, u'gun', u'L'),\n",
" (211, u'knife', u'D'),\n",
" (141, u'knife', u'L'),\n",
" (43, u'blunt_instrument', u'D'),\n",
" (27, u'blunt_instrument', u'L'),\n",
" (16, u'other', u'D'),\n",
" (16, u'other', u'L')]"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"EXEC dbo.FrequencyTable 'dbo.HOMICIDES', 'FREQUENCY, WEAPON, LIGHT_DARK';"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 2",
"language": "python",
"name": "python2"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.13"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment