locked
Trying to determine the intersection of geographies in two tables RRS feed

  • Question

  • I have two tables. One is a spatial table contating some tiger boundaries (Census Tracts) and the other is a flat table with lats and longs. I'd like to create a result set showing which tract each lat long falls within. I can find them one at a time like this:

    Select * from Census_Tracts Where geom.STIntersects(geography::Point(32.573571, -97.20554, 4326)) = 1

    but I'm getting stumped on building a query that will return all the results at once. For this purpose the other table is called Customers and the lat/lon fields are Lat and Lon respectively. This seems like it would be easy, but I'm not getting it.
    Friday, February 19, 2010 4:41 PM

Answers

  • Like this?

    SELECT 
      * 
    FROM 
      Census_Tracts ct, 
      Customers c
    WHERE
      ct.geom.STIntersects(geography::Point(c.Lat, c.Lon, 4326)) = 1
    

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, February 19, 2010 5:46 PM
    Answerer
  • tanoshimi aproach is perfectly valid, but i would suggest you to use:

    select
    c.id_customer
    ,t.id_tract
    from customers c left join tracts t
    on t.geom.STIntersects(geography::Point(c.Lat, c.Lon, 4326)) = 1

    just in case your not sure that every customer fall in a tract. if so, you'll get a null value for id_tract
    Friday, February 19, 2010 11:08 PM

All replies

  • Like this?

    SELECT 
      * 
    FROM 
      Census_Tracts ct, 
      Customers c
    WHERE
      ct.geom.STIntersects(geography::Point(c.Lat, c.Lon, 4326)) = 1
    

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, February 19, 2010 5:46 PM
    Answerer
  • tanoshimi aproach is perfectly valid, but i would suggest you to use:

    select
    c.id_customer
    ,t.id_tract
    from customers c left join tracts t
    on t.geom.STIntersects(geography::Point(c.Lat, c.Lon, 4326)) = 1

    just in case your not sure that every customer fall in a tract. if so, you'll get a null value for id_tract
    Friday, February 19, 2010 11:08 PM
  • Thanks for the replies guys. I got this working on my own the other day and should have posted it. My solution was very similar. Thanks again.
    Monday, February 22, 2010 3:55 PM