Spatial data queries in sql server 2008

Question

Ive got 2 tables:

States
Roads

Each of them contains geometry column.

How can I write a query to show all roads that crosses state x ? It would be something with stintersects but I don't know how to:

geom.StIntersects(any object from states)

Thank You for any help

Sören Kuklau · Accepted Answer · 2012-01-15 01:47:14Z

1

SELECT * FROM Roads
WHERE Roads.geo.STIntersects(
    (SELECT geo FROM States WHERE Name = 'x')
) = 1

answered Jan 15, 2012 at 1:47

Sören Kuklau

20k8 gold badges57 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

That checks all roads against just one state?

That seems to have been the request: "How can I write a query to show all roads that crosses state x ?"

MatBailie · Accepted Answer · 2012-01-15 01:40:02Z

0

Something like this?

Select
  *
From
  Roads
Inner join
  States
    On roads.geomField.stintersects(states.geomField)

answered Jan 15, 2012 at 1:40

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges