How to create a new table from multiple spatial searches in SQL Server? -

i wondering if help. asked colleagues create postcode lookup table them , takes me considerable time using desktop gis. wondering if possible create table using sql server create lookup me?

the lookup table based on finding relevant boundaries postcode sample table this:

table postcode  school     ward        polling district bh15 2ru  st peters  elmsdale    pd 

all of information school, ward , polling district coming different tables. each of these tables has geometry column (as has postcode table).

i can run select statement simple join (say postcode school) , create table, run of separate spatial queries in 1 large query create singular table. have 20 or different tables boundaries required large lookup table.

i hope makes sense!

any appreciated.

first you'll need create table, one-off process:

create table [example] (     postcode varchar(8) not null,     school varchar(50),     ward varchar(50)     -- repeat each row (setting appropriate length / type etc) ); 

then can populate them (suggest making stored procedure, index on example.postcode , ensure appropriate spatial index on geometry columns):

merge [example] target using ( select     p.[name] postcodename,     sch.[name] schoolname,     wrd.[name] wardname     -- other variables required     [postcodes] p     join [schools] sch on sch.[geometry].stintersects(p.[geometry]) = 1     join [wards] wrd on wrd.[geometry].stintersects(p.[geometry]) = 1     -- other joins required ) source on target.[postcode] = source.[postcodename] when matched target     update set     [school] = source.[schoolname],     [ward] = source.[wardname]     -- repeat other variables when not matched target     insert     ([postcode], [school], [ward]) -- etc.     values     (source.[postcodename], source.[schoolname], source.[wardname]) -- etc. when not matched source -- include if want remove records postcode has have been deleted since last "run"     delete; 

i haven't tested obviously, should 99%+ of way there.

merge if haven't used provides wonderful way handle insert / update / delete in single statement. making assumption geography data formed, share same (or no) srid, specify coordinates in same datum / projection), , there no overlaps in each table deal with. also, please note lot of ooompfh, make take time run if have national coverage (1.7m postcodes, 225k oa's, 9k wards etc.). suggest add where postcode.[name] 'bh%' or similar test first.