locked
Select Features Within N Distance of Each Other RRS feed

  • Question

  • I have a spatial table containing polygons that represent buildings in my county.  There are over 255,000 polygons in my table.

    I would simply like to query that table to find all buildings within n distance of each other.  This seems like it should be fairly basic, but the solution has eluded me.

    I am looking to answer questions such as:  

    How many buildings are within 5 or n feet of each other?

    Any suggestions are appreciated.  Thanks.

    JP

    Wednesday, October 9, 2013 3:47 PM

Answers

  • Some sample data might have been helpful. ;)  I'm posting this example using points instead of polygons for simplicity, but the query is the same.  This example returns buildings in downtown Seattle within 150 meters of each other.

    DECLARE @building TABLE (
    	 location geography
    	,name varchar(254)
    	)
    
    INSERT @building VALUES
     (geography::Point(47.6051, -122.3298, 4326), 'Seattle Municipal Tower')
    ,(geography::Point(47.6061, -122.3341, 4326), 'Safeco Plaza')
    ,(geography::Point(47.60583, -122.3320, 4326), '901 5th Ave')
    ,(geography::Point(47.60453, -122.33069, 4326), 'Columbia Center')
    ,(geography::Point(47.606738,-122.332678, 4326), 'Seattle Public Library')
    
    
    SELECT b1.name, b2.name, b1.location.STDistance(b2.location) [distance]
    FROM @building b1
    JOIN @building b2 ON (b1.location.STDistance(b2.location) < 150 AND b1.name <> b2.name)
    ORDER BY 1


    Jason

    • Proposed as answer by Sofiya Li Friday, October 11, 2013 9:02 AM
    • Marked as answer by shizl8r Friday, October 11, 2013 1:07 PM
    Wednesday, October 9, 2013 6:59 PM

All replies

  • Some sample data might have been helpful. ;)  I'm posting this example using points instead of polygons for simplicity, but the query is the same.  This example returns buildings in downtown Seattle within 150 meters of each other.

    DECLARE @building TABLE (
    	 location geography
    	,name varchar(254)
    	)
    
    INSERT @building VALUES
     (geography::Point(47.6051, -122.3298, 4326), 'Seattle Municipal Tower')
    ,(geography::Point(47.6061, -122.3341, 4326), 'Safeco Plaza')
    ,(geography::Point(47.60583, -122.3320, 4326), '901 5th Ave')
    ,(geography::Point(47.60453, -122.33069, 4326), 'Columbia Center')
    ,(geography::Point(47.606738,-122.332678, 4326), 'Seattle Public Library')
    
    
    SELECT b1.name, b2.name, b1.location.STDistance(b2.location) [distance]
    FROM @building b1
    JOIN @building b2 ON (b1.location.STDistance(b2.location) < 150 AND b1.name <> b2.name)
    ORDER BY 1


    Jason

    • Proposed as answer by Sofiya Li Friday, October 11, 2013 9:02 AM
    • Marked as answer by shizl8r Friday, October 11, 2013 1:07 PM
    Wednesday, October 9, 2013 6:59 PM
  • Sorry I couldn't follow up sooner.  I had to wait for my account verification to post all this.

    Jason's solution is correct and exactly what I was looking for.  I was able to use his code sample for my polygon features.

    Apologies for not including sample data earlier; I was worried I might make the post TLDR for some.

    For anyone who might come across this in the future I am including a sample of my table and the code I used to get the results I am looking for.  

    --Buildings Sample Dataset. One City Block of Buildings.
    --Create the sample table.
    IF OBJECT_ID('dbo.BUILDS38','U')IS NOT NULL
    	DROP TABLE dbo.BUILDS38;
    GO
    
    CREATE TABLE BUILDS38
    	(taxkey varchar(254),
    	shape geometry,
    	shape1 AS shape.STAsText());
    GO
    
    --Add sample data into table.
    INSERT INTO BUILDS38 (taxkey,shape)
    VALUES (1,geometry::STGeomFromText('POLYGON ((2555686.1400146484 371953.75, 2555636.0100097656 371951.16998291016, 2555636.3500366211 371926.85998535156, 2555692.1799926758 371927.63000488281, 2555689.9400024414 371937.21002197266, 2555686.1400146484 371953.75))',32054)),
     (2,geometry::STGeomFromText('POLYGON ((2555508.2700195312 372221.94000244141, 2555510.1199951172 372234.71002197266, 2555509.7600097656 372246.48004150391, 2555507.25 372252.40002441406, 2555457.0900268555 372251.97003173828, 2555457.3099975586 372226.42999267578, 2555485.0400390625 372225.30999755859, 2555508.2700195312 372221.94000244141))',32054)),
     (3,geometry::STGeomFromText('POLYGON ((2555490.9400024414 372262.38000488281, 2555490.0900268555 372289.14001464844, 2555472.1799926758 372290.03002929688, 2555456.6000366211 372289.64001464844, 2555441.3099975586 372287.82000732422, 2555442.1300048828 372261.5400390625, 2555490.9400024414 372262.38000488281))',32054)),
    (4,geometry::STGeomFromText('POLYGON ((2555451.700012207 372046.53002929688, 2555507.0599975586 372047.59002685547, 2555506.6000366211 372071.41003417969, 2555457.049987793 372070.46002197266, 2555457.33001709 372055.76000976562, 2555451.5300292969 372055.64001464844, 2555451.700012207 372046.53002929688))',32054)),
    (5,geometry::STGeomFromText('POLYGON ((2555561.5200195312 371867.66998291016, 2555561.5200195312 371923.55999755859, 2555529.8400268555 371923.55999755859, 2555531.8900146484 371906.65002441406, 2555533.9299926758 371899, 2555515.0700073242 371899, 2555515.0700073242 371868.91998291016, 2555532.33001709 371868.29998779297, 2555561.5200195312 371867.66998291016))',32054)),
    (6,geometry::STGeomFromText('POLYGON ((2555633.8400268555 372232.29998779297, 2555656.4400024414 372231.15002441406, 2555671.2200317383 372231.15002441406, 2555687.2399902344 372232.29998779297, 2555687.2399902344 372255.60998535156, 2555633.8400268555 372255.60998535156, 2555633.8400268555 372232.29998779297))',32054)),
    (7,geometry::STGeomFromText('POLYGON ((2555560.33001709 372232.35003662109, 2555560.33001709 372254.58001708984, 2555523.3400268555 372254.58001708984, 2555523.3400268555 372235.51000976562, 2555533.66998291 372233.92999267578, 2555560.33001709 372232.35003662109))',32054)),
    (8,geometry::STGeomFromText('POLYGON ((2555553.200012207 372351.47003173828, 2555552.33001709 372316.17999267578, 2555562.3500366211 372316.17999267578, 2555562.3500366211 372325.54998779297, 2555575.41998291 372325.54998779297, 2555576.8400268555 372337.30999755859, 2555577.0599975586 372351.91003417969, 2555574.8800048828 372364.98004150391, 2555573.9000244141 372371.94000244141, 2555554.0700073242 372371.94000244141, 2555553.200012207 372351.47003173828))',32054)),
    (9,geometry::STGeomFromText('POLYGON ((2555638.2399902344 372111.84002685547, 2555694.299987793 372112.54998779297, 2555694.0200195312 372135.08001708984, 2555638.3599853516 372132.63000488281, 2555632.1799926758 372130.79998779297, 2555632.3500366211 372117.88000488281, 2555638.1600341797 372117.95001220703, 2555638.2399902344 372111.84002685547))',32054)),
    (10,geometry::STGeomFromText('POLYGON ((2555509.1099853516 372108.10003662109, 2555508.8900146484 372130.48004150391, 2555460.4899902344 372130, 2555460.6300048828 372116.10998535156, 2555454.8200073242 372116.04998779297, 2555454.9299926758 372107.92999267578, 2555509.1099853516 372108.10003662109))',32054)),
    (11,geometry::STGeomFromText('POLYGON ((2555508.4400024414 372035.28002929688, 2555457.3099975586 372036.77001953125, 2555455.1900024414 372021.35998535156, 2555452.91998291 372014.2900390625, 2555458.5599975586 372013.57000732422, 2555510.0400390625 372013.65002441406, 2555511.7300415039 372021.41998291016, 2555513.3500366211 372032.97003173828, 2555508.4400024414 372035.28002929688))',32054)),
    (12,geometry::STGeomFromText('POLYGON ((2555453.8400268555 372071.11999511719, 2555499.4100341797 372072, 2555501.7900390625 372083, 2555501.9000244141 372092.0400390625, 2555499.4800415039 372097.58001708984, 2555458.0900268555 372096.77001953125, 2555456.0300292969 372080.63000488281, 2555453.8400268555 372071.11999511719))',32054)),
    (13,geometry::STGeomFromText('POLYGON ((2555509.9100341797 372157.96002197266, 2555454.7300415039 372156.71002197266, 2555455.2700195312 372132.98999023438, 2555497.0100097656 372133.92999267578, 2555496.7200317383 372146.65002441406, 2555510.1600341797 372146.95001220703, 2555509.9100341797 372157.96002197266))',32054)),
    (14,geometry::STGeomFromText('POLYGON ((2555636.9899902344 372082.83001708984, 2555697.2800292969 372083.19000244141, 2555697.1500244141 372105.25, 2555636.8599853516 372104.90002441406, 2555636.9899902344 372082.83001708984))',32054)),
    (15,geometry::STGeomFromText('POLYGON ((2555686.2100219727 372018.26000976562, 2555688.7100219727 372029.98999023438, 2555691.2200317383 372041.02001953125, 2555633.5400390625 372040.63000488281, 2555633.700012207 372017.90002441406, 2555686.2100219727 372018.26000976562))',32054)),
    (16,geometry::STGeomFromText('POLYGON ((2555642.6900024414 372359.2900390625, 2555633.9299926758 372359.27001953125, 2555633.9299926758 372365.36999511719, 2555613.8900146484 372365.36999511719, 2555611.4899902344 372316.91003417969, 2555640.25 372318.10003662109, 2555641.9800415039 372344.26000976562, 2555642.6900024414 372359.2900390625))',32054)),
    (17,geometry::STGeomFromText('POLYGON ((2555636.9000244141 371988.72003173828, 2555693.2800292969 371989.97003173828, 2555692.7900390625 372012.03002929688, 2555636.4100341797 372010.77001953125, 2555636.9000244141 371988.72003173828))',32054)),
    (18,geometry::STGeomFromText('POLYGON ((2555454.0200195312 372173.26000976562, 2555451.5300292969 372163.65002441406, 2555456.6900024414 372163.03002929688, 2555494.950012207 372164.01000976562, 2555500.2900390625 372165.60998535156, 2555500.2900390625 372187.32000732422, 2555456.5100097656 372187.32000732422, 2555454.0200195312 372173.26000976562))',32054)),
    (19,geometry::STGeomFromText('POLYGON ((2555453.5300292969 372194.95001220703, 2555505.3699951172 372194.95001220703, 2555507.3800048828 372199.16998291016, 2555509.1300048828 372221.17999267578, 2555459.8500366211 372218.98004150391, 2555459.8500366211 372203.16998291016, 2555453.5300292969 372203.16998291016, 2555453.5300292969 372194.95001220703))',32054)),
    (20,geometry::STGeomFromText('POLYGON ((2555641.7700195312 371869.97003173828, 2555705.16998291 371871.10003662109, 2555704.75 371895.22003173828, 2555677.0599975586 371893.23999023438, 2555641.3900146484 371891.10998535156, 2555641.7700195312 371869.97003173828))',32054)),
    (21,geometry::STGeomFromText('POLYGON ((2555509.6400146484 372011.10998535156, 2555458.9100341797 372009.86999511719, 2555459.1900024414 371998.76000976562, 2555453.16998291 371998.60998535156, 2555453.4299926758 371988.03002929688, 2555507.0900268555 371989.35003662109, 2555508.4700317383 371996.13000488281, 2555509.6400146484 372011.10998535156))',32054)),
    (22,geometry::STGeomFromText('POLYGON ((2555519.75 372381.23999023438, 2555493.6799926758 372381.03002929688, 2555495.41998291 372355.15002441406, 2555494.2399902344 372336.10998535156, 2555504.700012207 372333.51000976562, 2555520.1300048828 372330.92999267578, 2555519.75 372381.23999023438))',32054)),
    (23,geometry::STGeomFromText('POLYGON ((2555645.1000366211 371897.09002685547, 2555689.7399902344 371897.47003173828, 2555687.2700195312 371907.22003173828, 2555684.7399902344 371921.89001464844, 2555644.8900146484 371921.55999755859, 2555645.1000366211 371897.09002685547))',32054)),
    (24,geometry::STGeomFromText('POLYGON ((2555609.1900024414 372318.0400390625, 2555606.7900390625 372371.07000732422, 2555600.6900024414 372368.67999267578, 2555582.3900146484 372366.28002929688, 2555582.3900146484 372316.84002685547, 2555609.1900024414 372318.0400390625))',32054)),
    (25,geometry::STGeomFromText('POLYGON ((2555467.2700195312 371954.14001464844, 2555498.6000366211 371954.14001464844, 2555500.4700317383 371963.92999267578, 2555502.33001709 371975.48999023438, 2555497.7100219727 371977.63000488281, 2555467.2700195312 371979.77001953125, 2555467.2700195312 371954.14001464844))',32054)),
    (26,geometry::STGeomFromText('POLYGON ((2555438.3599853516 372381.42999267578, 2555438.7800292969 372302.90002441406, 2555465.33001709 372303.04998779297, 2555464.9100341797 372381.57000732422, 2555438.3599853516 372381.42999267578))',32054)),
    (27,geometry::STGeomFromText('POLYGON ((2555639.6799926758 372345.39001464844, 2555642.4700317383 372315.35998535156, 2555652.549987793 372317.91003417969, 2555667.2200317383 372320.54998779297, 2555667.4000244141 372312.35003662109, 2555699.4899902344 372313.02001953125, 2555700.7600097656 372321.19000244141, 2555698.6400146484 372329.28002929688, 2555675.1400146484 372328.78002929688, 2555673.2200317383 372349.14001464844, 2555671.3500366211 372366.84002685547, 2555658.9600219727 372368, 2555641.3400268555 372369.04998779297, 2555640.3900146484 372360.41998291016, 2555639.6799926758 372345.39001464844))',32054)),
    (28,geometry::STGeomFromText('POLYGON ((2555522.2600097656 372369.10998535156, 2555522.2600097656 372317.27001953125, 2555544.700012207 372317.27001953125, 2555545.7900390625 372335.57000732422, 2555545.7900390625 372354.73999023438, 2555544.700012207 372372.82000732422, 2555538.3900146484 372370.97003173828, 2555522.2600097656 372369.10998535156))',32054)),
    (29,geometry::STGeomFromText('POLYGON ((2555456.7800292969 371926.40002441406, 2555518.0100097656 371928.35998535156, 2555517.2800292969 371951.10998535156, 2555460.5599975586 371949.29998779297, 2555458.700012207 371936.91003417969, 2555456.7800292969 371926.40002441406))',32054)),
    (30,geometry::STGeomFromText('POLYGON ((2555638.1099853516 372202.04998779297, 2555689.7200317383 372202.41003417969, 2555689.549987793 372226.07000732422, 2555637.950012207 372225.71002197266, 2555638.1099853516 372202.04998779297))',32054)),
    (31,geometry::STGeomFromText('POLYGON ((2555621.2399902344 371984.77001953125, 2555609.7300415039 371985.13000488281, 2555585.1400146484 371984.89001464844, 2555585.6400146484 371957.13000488281, 2555602.5200195312 371958.29998779297, 2555622.9600219727 371959.5400390625, 2555624.8200073242 371963.79998779297, 2555623.8900146484 371979.66998291016, 2555621.2399902344 371984.77001953125))',32054)),
    (32,geometry::STGeomFromText('POLYGON ((2555689.299987793 371956.28002929688, 2555687.5900268555 371983.41998291016, 2555647.1400146484 371983.05999755859, 2555647.3599853516 371957.7900390625, 2555689.299987793 371956.28002929688))',32054)),
    (33,geometry::STGeomFromText('POLYGON ((2555651.75 372138.82000732422, 2555691.2700195312 372138.82000732422, 2555691.7200317383 372165.60998535156, 2555655.4899902344 372162.85003662109, 2555653.6199951172 372147.5400390625, 2555651.75 372138.82000732422))',32054)),
    (34,geometry::STGeomFromText('POLYGON ((2555691.7200317383 372168.14001464844, 2555692.1600341797 372194.73999023438, 2555656.2100219727 372194.73999023438, 2555654.3400268555 372179.44000244141, 2555652.4700317383 372170.89001464844, 2555691.7200317383 372168.14001464844))',32054)),
    (35,geometry::STGeomFromText('POLYGON ((2555614.41998291 372255.60003662109, 2555581.5900268555 372255.60003662109, 2555581.5900268555 372234.04998779297, 2555586.4700317383 372232.54998779297, 2555592.4899902344 372232.54998779297, 2555621.6900024414 372234.04998779297, 2555621.6900024414 372244.33001708984, 2555614.41998291 372244.33001708984, 2555614.41998291 372255.60003662109))',32054)),
    (36,geometry::STGeomFromText('POLYGON ((2555446.7200317383 371911.45001220703, 2555448.1400146484 371865.17999267578, 2555500.1600341797 371866.78002929688, 2555501.299987793 371895.19000244141, 2555502.7700195312 371913.17999267578, 2555446.7200317383 371911.45001220703))',32054)),
    (37,geometry::STGeomFromText('POLYGON ((2555636.6400146484 372051.85998535156, 2555693.3800048828 372052.22003173828, 2555693.2399902344 372075.53002929688, 2555636.4899902344 372075.17999267578, 2555636.6400146484 372051.85998535156))',32054)),
    (38,geometry::STGeomFromText('POLYGON ((2555548.200012207 371987.42999267578, 2555548.200012207 372011.80999755859, 2555516.3400268555 372011.80999755859, 2555516.3400268555 371992.76000976562, 2555521.3200073242 371991.07000732422, 2555530.9299926758 371988.41003417969, 2555548.200012207 371987.42999267578))',32054))
    GO
    
    --Select the table to see if buildings created correctly.
    SELECT * FROM BUILDS38
    GO

    Image of 38 Buildings in City Block:


    Finally, Jason's code that I modified to Select the buildings by Distance:

    --The code to select buildings within n distance of each other.
    SELECT b1.taxkey, b2.taxkey, b1.shape.STDistance(b2.shape) [distance], b1.shape
    FROM BUILDS38 b1
    JOIN BUILDS38 b2 ON (b1.shape.STDistance(b2.shape)< 5 AND --Change the number here to set your desired search distance.
    b1.taxkey <> b2.taxkey)
    ORDER BY 1

    The Result - Buildings Within 5 Feet of Each Other:


    A big thank you to Jason for quickly providing a solution.

    -JP


    • Edited by shizl8r Friday, October 11, 2013 1:52 PM
    Friday, October 11, 2013 1:07 PM
  • I'm glad I was able to help!

    Jason

    Friday, October 11, 2013 6:12 PM