CODEWITHSUNDEEP
sqlsql-server-2008geometrygeospatial
Chris
Chris

Reputation: 319

SQL Server 2008 - using STIntersects from a table that defines a country

I have a table where each row is part of a New Zealand map. What I really want to know is is a point within the map (ie the country data) or is it in the water.

I understand how StIntersects works but all examples are for a single Polygon or LineString but I have a table of LineStrings - 130 rows that define the country border.

Lots of rows like this

LINESTRING (6252032.7308424888 -3161950.9615992079, 6252033.7275789445 -3161929.3581238855, 6252011.5227283547 -3161906.1086780191, 6251992.0438580718 -3161880.6299652755)

So I think I need to sort of put all the line strings together to make my country border a single polygon or something like that, but I do not know how to do that.

Can someone give me an example of how I could do this?

The original data was from a ShapeFile from www.koordinates.com called New Zealand Coastlines. I then used Shape2Sql to import to SQL Server using Planer Geometry.

Geometry (spheric) in Shape2File said "Data projects or extent is outside the bounds of what is supported by the SqlGeography type)

Hope I have provided enough information?

Cheers Chris

Upvotes: 1

Views: 5602

Answers (3)

Geobility
Geobility

Reputation: 178

You have most likely moved on from this but I am posting for future use.

If I understand you correctly, you need to create one polygon from a table of LINESTRINGs that make up the boundary of the polygon?

First, combine all of the LINESTRINGs in your table into one geometry like this:

DECLARE @g geometry

SELECT TOP 1 @g = geom from #geom_tmp

SELECT
     @g = @g.STUnion(geom)
FROM
    #geom_tmp

SELECT @g

Then you want to create a polygon from your single-line boundary. There are only two things that differ between your single-line boundary string and a polygon, first we replace "LINESTRING" with "POLYGON". Then we add a set of parentheses since a polygon's WKT string separates multiple polygons with parentheses like this:

DECLARE @str NVARCHAR(max)
SET @str = REPLACE(REPLACE(@g.STAsText(),'(','((') + ')','LINESTRING','POLYGON')

SET @g = GEOMETRY::STGeomFromText(@str,0)

SELECT @g

Which will give you your polygon of the country.

Then you simply see if your point intersects the polygon (@p is the point geometry):

SELECT @p.STIntersects(@g)

-- OUTPUT is 1 if it intersects and 0 if it does not.

OR if your points are in a table, you could select the list of points that intersect the country as shown below if the point table geometry column is called "point".

SELECT *
FROM point_table
WHERE point.STIntersects(@g) = 1

Since there are many of us who cannot move to SQL server 2012 any time soon, this comes in handy. Hope it helps.

Upvotes: 1

Pure.Krome
Pure.Krome

Reputation: 86967

Let me see if I understand your problem.

You wish to determine if a pre-determined point is either INSIDE NZ or OUTSIDE. Now, you have the coastline of NZ, right? This coastline is around 130 rows ... but we need to make this one single row .. and THEN see if the point lies inside this boundary or outside.

If this is correct, then the first thing you'll need to do is to join all the linestrings into a single massive polygon .. and we need this final single row as a Sql Server 2008 GEOGRAPHY type.

So - first some help. Jump over to Codeplex and grab this library - sql spatial tools. In this library, there is a method to JOIN all the linestrings. I think it's called GeographyUnionAggregate . Figure out how to run this stored proc which will join all the linestrings together (assuming none of them are messed up).

Once this task is done, you will have a single GEOGRAPHY row which repesents the entire coastline of NZ.

Now, it's a simple sql statement to see if the point exists/intersects the boundary (the country of NZ) or not :-

DECLARE @SomePoint GEOGRAPHY
SET @SomePoint = geography::STGeomFromText('POINT(-122.358 47.653)', 4326);
SELECT CountryId, Name
FROM Countries a
    INNER JOIN a.AdministrativeBoundary.STIntersects(@SomePoint) = 1

And that should return you one result :)

Upvotes: 0

SF.
SF.

Reputation: 14069

The classic approach to determine whether two points are on the same side (inside or outside) of a polygon, is to calculate the number of intersections between a line connecting the two and borders of the polygon. Even number (incl. 0) is same side, odd is different sides.

So define one (any) point which is inside New Zealand for sure (preferably near geometric middle), then a line that connects it with the point you want to check, then calculate the number of lines it intersects. If it's 0 or even, the other point is on the same side (that is in New Zealand.) If it's odd, the point is outside the country borders. You don't need to worry about ordering or connecting the border lines.

It becomes more tricky if the line crosses exactly through a joint point between two border lines, but StIntersects won't save you against that, and besides if you work on real values with 17 significant places, the likehood for this to occur is minimal.

The algorithm is good here in that it works for multiple disjoint polygons (isles) just as well as for single irregular polygon. The only requirement is all the lines are closed.

Of course to get that to work you'd have to transform linestrings into a set of single lines and store these, because you need to find number of intersections, not just fact: line intersects polygon (n times) vs 0 times.

Upvotes: 0

Related Questions