Aantal inwoners/woningen binnen straal


declare @straal int;
declare @capakey nvarchar(50);

set @straal = 500
set @capakey = '33029A0105/00G000'

-- aantal inwoners in woningen binnen straal
select sum(c.Aantal_Inwoners) from
(select a.adresid,Aantal_Inwoners,geometry::STGeomFromText('POINT(' + convert(varchar, a.X) + ' ' + convert(varchar, a.Y) + ')',31370) as punt from
(SELECT  [adresid], x, y, Aantal_Inwoners  FROM [K_AdressenBeheer].[dbo].V_Adressen_Detail where (bestemming = 'Bewoning' or bestemming = 'Gemengd') and archief = 0) a
inner join 
(SELECT [CEVI_GEOMETRY].STBuffer(@straal) as geo,capakey FROM [Spatial_Basis].[dbo].[PERCELEN_GRB]  where capakey =@capakey) b
on b.geo.STContains(geometry::STGeomFromText('POINT(' + convert(varchar, a.X) + ' ' + convert(varchar, a.Y) + ')',31370)) = 1) c

-- aantal woningen binnen straal
select a.adresid,Aantal_Inwoners,geometry::STGeomFromText('POINT(' + convert(varchar, a.X) + ' ' + convert(varchar, a.Y) + ')',31370) as punt from
(SELECT  [adresid], x, y, Aantal_Inwoners  FROM [K_AdressenBeheer].[dbo].V_Adressen_Detail where (bestemming = 'Bewoning' or bestemming = 'Gemengd') and archief = 0) a
inner join 
(SELECT [CEVI_GEOMETRY].STBuffer(@straal) as geo,capakey FROM [Spatial_Basis].[dbo].[PERCELEN_GRB]  where capakey =@capakey) b
on b.geo.STContains(geometry::STGeomFromText('POINT(' + convert(varchar, a.X) + ' ' + convert(varchar, a.Y) + ')',31370)) = 1

Categories:

Related Article