Spatial query in SQL server

1

Hi, trying to do a spatial query in SQL server, either STWithin or STContains, or whatever works, eg Union or intersects....
I can do this easily in GIS software, but having trouble in SQL server.
I have SMALLtable and BIGTable of polygons. What i wish to do is add some columns from BIGTable to SmallTable based on a spatial query that evalutes when SMALLTable within BIGTable. Note - every record in SmallTable DOES fall within a record of BigTable.
Code like below (but this obviously doesn't work in SQL Server)

SELECT SmallTable., BIGTable.
where SmallTable.ITMGEOMETRY.STWithin(BigTable.SP_GEOMETRY)

Any help appreciated.
Thanks Peter H

2

Ok. Got the code below to execute with no errors. Note SmallTable is Visit and BigTable is Suburbs. But also returns no records. I'll have to look into this a bit more.
Found a great youtube presentation Working With Spatial Data in SQL Server (youtube.com).

CODE -
select *
from MAPINFO.Suburbs S
Inner Join Vph_MOSI_ITM_Visit V on V.ITMGEOMETRY.STWithin(S.SP_Geometry) = 1

3

You aren't by chance running these backwards, are you?

What about this?

SELECT *
FROM MAPINFO.Suburbs S
INNER JOIN Vph_MOSI_ITM_Visit V 
ON S.SP_Geometry.STWithin(V.ITMGEOMETRY) = 1;

It doesn't sound right, since I'd think a "visit" would be smaller than a "suburb".

EDIT: ah, it just clicked that's what you meant with "small" and "big"... not row size, but geometry size. OK, ignore my comment entirely.

1 Like
4

Quick update on this one - Query running but returning no records. Once I set the SRID (spatial Reference ID) to the same parameter in each table, everything ran perfectly and returned expected results.