CODEWITHSUNDEEP
sqlspatial
Pat Hastings
Pat Hastings

Reputation: 412

How to extract Polygons from a SQL Server geometry value

I have a set of data in SQL Server 2008 for each country.

I want to add a geometry value for each describing the the land mass of each country so I have found the TM_World_Border shape file and imported it into a the db - All well and good.

The issue is the existing schema has Alaska and Hawaii as separate entries from the rest of the USA where as the shape file only has one entry for all three combined.

Could you please point me as to how I can extract Alaska and Hawaii into their own geometry values

Upvotes: 1

Views: 1482

Answers (1)

Pat Hastings
Pat Hastings

Reputation: 412

Ok so looks I solved the issue by creating a second polygon which bounds around the area I am interested in (Think Clipping Mask) and selecting the intersection of the polygon and the original Geography value

declare @Bounding Geography

declare @USA Geography

declare @Result Geography

select @USA = geog from dbo.country_10m where id = 238

select @Bounding = Geography::STGeomFromText('POLYGON((-130 54, -130 23, -60 23, -60 54, -130 54))', 4326);

 SELECT @Result = @USA.STIntersection(@Bounding)

update foo

set geog = @Result

where id = 2

Upvotes: 1

Related Questions