How to return more than one field using a STIntersection query in SQL Server

Question

I am calculating polygon intersections within SQL Server and have a table as shown below based on the example here: http://msdn.microsoft.com/en-gb/library/cc280766.aspx where 'Coordinates_OGC_WKT' is computed.

+--------+----------------------+------------------------------------+------------+
| ZoneId |     Coordinates      |        Coordinates_OGC_WKT         |  ZoneName  |
+--------+----------------------+------------------------------------+------------+
|      1 | 0xE61000000 [etc...] | POLYGON ((-122.358 47.653 [etc...] | Polygon    |
|      2 | 0xE61000000 [etc...] | LINESTRING (-122.36 47 [etc...]    | Linestring |
+--------+----------------------+------------------------------------+------------+

I have tested the proposed SP as below and it works however I am unsure how to also return the 'ZoneName' field - which is important.

DECLARE @geocoords geography;
DECLARE @geoline geography;
DECLARE @result geography;

SELECT @geocoords = Coordinates FROM SpatialZonePolygons WHERE ZonePolygonId = 1;
SELECT @geoline = Coordinates FROM SpatialZonePolygons WHERE ZonePolygonId = 2;
SELECT @result = @geoline.STIntersection(@geocoords);
SELECT @result.STAsText() AS WKT;

The result shows the intersection coordinates but doesn't indicate the row in the database that holds the polygon coordinates. I'm trying to retrieve the 'ZoneName' where the intersection occurs (which is required):

+---------------------------------------------------------------+
|                              WKT                              |
+---------------------------------------------------------------+
| LINESTRING (-122.35800000000017 47.656000130337446, [etc...]) |
+---------------------------------------------------------------+

This must be simple but I'm struggling.

---- EDIT ----

This looks as though it should work and kind of does... but I'm getting zones that are no where near the polyline:

SELECT *
FROM dbo.SpatialZonePolygons
WHERE Coordinates.STIntersects(geography::STGeomFromText('LINESTRING(51.46276 -0.106, 51.46275 -0.10604, 51.46248 -0.10672, 51.46262 -0.10687, etc...)', 4326))>0

I have checked the polyline is correct by manually plotting it on a Google map. I have also checked the polygon coordinates are correct by doing the same so why would the database return zones that are 50-odd miles away from the polyline with definitely no intersections?

As a test, the polyline runs through London so I thought I'd see if a zone in Brussels was also returned... It wasn't. Could this therefore be an accuracy issue? I doubt it as the plotted polyline renders accurately and goes no where near the zones.

I'm a little desperate now so I am also wondering if I am storing the coordinates in the right order (lat lng) or should it be lng lat?

Is your business case (1) returning the polygons from a table of such that intersect a given line; or (2) returning the lines from a table of such that intersect a given polygon? — Pieter Geerkens, Aug 08 '14 at 04:35
Hi Pieter. It is (1) - returning the polygons from a table. The polyline is generated on demand. Polygons coordinates are determined from a Google map, polylines are from Google Directions API. Many thanks. — ChrisCurrie, Aug 08 '14 at 07:31

Pieter Geerkens · Answer 1 · 2014-08-08T09:29:46.883

1

This should do it:

CREATE PROCEDURE FindPolygons(
    @geoline geography
) as begin
SELECT
     Coordinates
    ,ZoneName 
    ,WKT
FROM (
    SELECT
         Coordinates
        ,ZoneName 
        ,@geoline.STIntersection(Coordinates).STAsText() AS WKT
    FROM SpatialZonePolygons        
) data
WHERE data.WKT is not null
end

go

edited Aug 08 '14 at 09:29

answered Aug 08 '14 at 01:35

Pieter Geerkens

11,775
2
32
52

Many thanks. That's closer to what I need but I'm getting a null return. That is because the intersection coordinates returned (LINESTRING (-122.35800000000017 47.656000130337446, -122.34800000000006 47.656000260674908)) - presumably representing where the polyline enters and then leaves the zone - are not a direct match to the zone boundary coordinates held in 'Coordinates_OGC_WKT'. So the intersection can happen at any point along or between these coordinates... Thanks though. – ChrisCurrie Aug 08 '14 at 02:31

score 0 · Accepted Answer · answered Aug 08 '14 at 15:07

0

I'm taking your comment of "returning the polygons from a table of such that intersect a given line" as the requirement.

create procedure findPolygons (
   @line geography
)
as
begin

    select ZoneId, Coordinates
    from SpatialZonePolygons 
    where Coordinates.STIntersects( @line ) = 1

end

answered Aug 08 '14 at 15:07

Ben Thul

31,080
4
45
68

That's the ticket! Many thanks! This is definately what I need but oddly I am now getting false positives where the statement is returning intersections with polygons that are over 50 miles away from the polyline. This is really strange but is probably to do with the polygon coordinates (or some other dark force) so I have another question that articulates the problem. This is the accepted answer. Many thanks. – ChrisCurrie Aug 10 '14 at 13:24

How to return more than one field using a STIntersection query in SQL Server

2 Answers2