Joining tables based on distance using SQL Server

Question

I need to show the three closest hospitals to every house. I have a table of properties and a table of hospitals. Both are in MSSQL2012, have a geography column that is indexed and are in the same 4326 SRID.

I cant find any examples on how to perform ST_Distance across different tables, returning the top 3.

As per Evans suggestion i have tried the following but the query is not valid

SELECT *
FROM (
        SELECT * , row_number OVER (
        PARTITION BY [dbo].[houses].[geogtab]
        ORDER BY [dbo].[houses].[geogtab].STDistance([dbo].[hospitals].[geogtab]) ASC
      ) AS i
  FROM [dbo].[houses]
  JOIN [dbo].[hospitals]
       ON [dbo].[houses].[geogtab].STBuffer(100000).STIntersects([dbo].[hospitals].[geogtab])
       ORDER BY [dbo].[houses].[geogtab].STDistance([dbo].[hospitals].[geogtab]  ) ASC
) AS t
WHERE t.i <= 3;

I am unsure if this is formatting related and am unsure from reading this if the distance will be an output in the result table.

Answer 1

4326 SRID

Microsoft SQL doesn't use SRID, so 4326 or 9999 isn't a problem. Like MySQL they just error on SRID mismtach. They do have a geographic object. They also don't follow any spec whatsoever with their spatial interface. It's really just a link to their Microsoft Java/.NET stuff and a veneer that sometimes look standardized.

Create your points with something like this,

-- No st_point in this bizzaro world
geography::Point(47.65100, -122.34900, 4326)

Find point-in-polygon boolean searches with STIntersects(). For the SqlGeography class this is essentially the PostGIS equivalent of ST_DWithin. At least it uses an index if you have one.

CREATE TABLE #tmp (
  geog geography,
);
INSERT INTO #tmp(geog) VALUES
  (geography::Point(1,1,4326)),
  (geography::Point(1.5,1.5,4326)),
  (geography::Point(2,2,4326));

SELECT geography::Point(1,1,4326).STBuffer(100000).STIntersects(geog)
FROM #tmp;

In the above, we're finding all points within 100000 meters of the CAST( ST_Point(1,1) AS geography)

Joining is just as simple.

FROM t1
JOIN t2
  ON t1.geog.STBuffer(100000).STIntersects(t2.geog)

Finding the top three JOIN and then ORDER BY distance.

SELECT *
FROM (
  SELECT * , row_number OVER (
    PARTITION BY t1.geog
    ORDER BY t1.geog.STDistance( t2.geog ) ASC
  ) AS i
  FROM t1
  JOIN t2
    ON t1.geog.STBuffer(100000).STIntersects(t2.geog)
  ORDER BY t1.geog.STDistance( t2.geog ) ASC
) AS t
WHERE t.i <= 3;

Importantly I don't think the KNN stuff (or anything else for that matter) is as sophisticated as PostGIS. For instance on Spatial KNN say,

The first expression in the ORDER BY clause must use the STDistance() method.

In this construction we do that, but I'm not sure that the planner will plan KNN.

You can always ask another question specifically about the indexing problem you encounter on this site or on Database Administrators (tag with spatial)