3

i'm trying to do a spatial query in sql 2008 -> for a given list of POI's (point of interest, long/lat GEOGRAPHY data), which postcodes do they exist in (multipolygon GEOGRAPHY data).

So this is the query i tried, but it's syntactically incorrect:-

SELECT PostCodeId, ShapeFile
FROM Postcodes a
WHERE a.ShapeFile.STIntersects(
    SELECT PointOfInterest
    FROM PointOfInterests
    WHERE PointOfInterestId IN (SELECT Item from dbo.fnSplit(@PoiIdList, ','))

So this means i pass in a csv list of POI Id's and split them. That's not a problem .. it's my subquery in the STIntersects. That's invalid.

So .. any suggestions folks?

Community
  • 1
  • 1
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647

1 Answers1

4

How about:

SELECT a.PostCodeId, a.ShapeFile
FROM (SELECT Item from dbo.fnSplit(@PoiIdList, ',')) AS POI_IDs
INNER JOIN PointOfInterests
    ON PointOfInterests.PointOfInterestId = POI_IDs.Item
INNER JOIN Postcodes a
    ON a.ShapeFile.STIntersects(PointOfInterests.PointOfInterest) = 1
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • thanks heaps :) i didn't know u could join the two tables in an STIntersects! – Pure.Krome Jan 31 '09 at 23:44
  • My only question would really be performance and what's the BEST way to do it. And right now, that's beyond my understanding (Spatial SQL is all new to me) – Cade Roux Feb 01 '09 at 02:18