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.
Best Answer
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,
Find point-in-polygon boolean searches with
STIntersects()
. For theSqlGeography
class this is essentially the PostGIS equivalent ofST_DWithin
. At least it uses an index if you have one.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.
Finding the top three JOIN and then
ORDER BY
distance.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,
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
)