Cartesian coordinate- Select coordinates which fall within an area

Question

I'm working on a problem, where I have to map indoor Cartesian coordinates to a certain zone.

The problem has numerous zones lets say zone1, zone 2, zone 3 and zone 4 with coordinates (x1,y1), (x2,y2) (x3,y3) and (x4,y4) respectively. I have a huge data set of visitors who are coming to visit the area and have there individual coordinates. The task is to map the visitor to a zone. A visitor is in that zone if he is within 10 mtrs of radius of a specific zone.

So, visitor A(a1,b1) is in Zone A(x1,y1) if visitors' coordinates are in radius of 10 mtrs from x1,y1.

So, a few questions:

1. which data type to be used for storing coordinates. Will float work. I saw a discussion going for spatial coordinates, not sure what does it mean.

2. Also, when I do the mapping, do I need to write the code in ETL? or do I need to use any other programming lang

3. Lastly, how to code the above problem

Answer 1

Here's how I'd do it. Let's assume that you have a zone table with one row per zone. I would define it like this:

create table dbo.Zones (
   ZoneID int identity not null,
   constraint [PK_Zone] primary key clustered (ZoneID),
   ZoneGeometry geometry
);

You'd populate it like so:

    insert into dbo.Zones 
      (ZoneGeometry)
    values 
      (geometry::STGeomFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))', 0));

Similarly for your visitors:

create table dbo.Visitors (
   VisitorID int identity not null,
      constraint [PK_Visitors] primary key clustered (VisitorID),
   Location geometry
);

insert into dbo.Visitors 
   (Location)
values
   (geometry::STPointFromText('POINT (1.5 1)', 0));

Then to find which zone each visitor is in:

select *
from dbo.Visitors as v
left join dbo.Zones as z
   on z.ZoneGeometry.STBuffer(10).STContains(v.Location) = 1;