I’ve recently had the pleasure of being able to play with SQL Server 2008 that, of course, has spatial support. There are two types of geometries that are fully supported…that being the classic OGC simple geometry types and then Microsoft’s flavour called “Geography”. Rather than trying to go in to any details about the differences between the two like so many people have done over the last year, I am going to post some examples on how to use a few of the built in spatial functions. I tend to find it a lot more useful to see a working example than a lame description on how to do something.
1. Select all the Polygons that intersect with a Linear Feature
SELECT GEOM_STATES.GEOM
FROM [GEOM_STATES] INNER JOIN GEOM_ROADS
ON (GEOM_ROADS.GEOM.STIntersects(GEOM_STATES.GEOM)=1)
WHERE GEOM_ROADS.STATE LIKE '%florida%';
2. Select all the Points that fall within a particular Polygon
SELECT Assets.SpatialAsset
FROM [Assets] INNER JOIN GEOM_STATES
ON (GEOM_STATES.GEOM.STIntersects(Assets.SpatialAsset )=1)
WHERE GEOM_STATES.STATE_NAME LIKE '%texas%';
3. Select all the Polygons that intersect with a series of Points.
SELECT GEOM_STATES.GEOM
FROM [GEOM_STATES] INNER JOIN view_Assets
ON (Assets.SpatialAsset.STIntersects(GEOM_STATES.GEOM)=1)
WHERE Assets.Facility_Name LIKE '%adams%';
4. Set a Buffer Distance and Search for Points within that Radius
DECLARE @SearchArea geography SET @SearchArea =
(SELECT SpatialAsset.STBuffer(10000) FROM dbo.Assets WHERE ID = 1234)
SELECT * FROM Assets WHERE SpatialAsset.STIntersects(@SearchArea)=1
5. Search for Linear features that are within a set Search Radius
DECLARE @SearchArea geography SET @SearchArea =
(SELECT SpatialAsset.STBuffer(10000) FROM dbo.Assets WHERE ID = 1234)
SELECT GEOM
FROM [GEOM_ROADS]
WHERE (GEOM.STIntersects(@SearchArea)=1)
These are all very useful queries that I will certainly reuse over and over again. Note that I used the “Geography” data type in each query and I imported the data from Shapefile using Morten’s Shape2SQL Utility.
More from Adam Estrada
- How to copy music from your iPod to your iPhone
- Oracle Spatial and USER_SDO_GEOM_METADATA
- GeoDC Kickoff…
- Automated web site testing
- Only in Alabama…
Adam Estrada Recommends
- Adam - The Indian iPad killer (MtaraM)
- Update from Google Map on my Problem (Maploser)
- Layar for the iPhone (Maploser)
