You actually don't need any special type system or complex infrastructure for this. Each data source can handle its own data representation and operations.
For GIS data, you could create a plugin that naturally handles spatial operations. Here's how it could work:
select
Id,
DistanceBetween(s.FromPoint(-73.935242, 40.730610)) as Distance,
s.IsBetween(s.FromPoint(-73.935242, 40.730610)) as IsInArea
from #gis.shapefile('map.shp') s
You could even combine it with other data sources. For example, if you have geometry data in a CSV:
select
sfg.DistanceBetween(sfg.FromPoint(..., ...))
from #csv.file(...) c
cross apply #gis.ShapeFromGeometry(c.Geometry) sfg
