# Trying to determine the intersection of geographies in two tables

• ### 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

• 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
• 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
• 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