CODEWITHSUNDEEP
c#sqlsql-server
NanoHead
NanoHead

Reputation: 159

Geography Convert LINESTRING to POLYGON

How do i convert the closed linestring to a polgyon for Geography with MS SQL or C#. I am trying to build this data so I can search the data within the MS SQL database. When i try to convert the data with the function STGeomFromWKB, it didn't return the right shape.

DECLARE @data geography;
 SET @data= 'LINESTRING(145.13527778 13.64166667, 144.74694444 13.47666667, 144.63722222 13.51194444, 144.61944444 13.5875, 144.62337227 13.60962359, 144.62777753 13.63154738, 144.63412072 13.65301205, 144.64235403 13.6738541, 144.65241522 13.69391474, 144.66422807 13.71304108, 144.67770294 13.73108731, 144.69273746 13.74791585, 144.70921729 13.76339835, 144.72701699 13.7774167,144.74600098 13.78986396,144.76602456 13.80064514,144.78693505 13.80967796,144.80857293 13.81689348,144.83077307 13.82223661,144.85336602 13.82566656,144.87617929 13.82715716,144.8990387 13.826697,144.92176971 13.82428962,144.94419878 13.81995338,144.96615467 13.8137214,144.98746983 13.80564125,145.0079816 13.79577461,145.02753354 13.78419678,145.0459766 13.77099612,145.06317023 13.75627335,145.07898353 13.74014079,145.09329617 13.7227215,145.10599934 13.70414829,145.1169966 13.68456278,145.12620454 13.66411424, 145.13355348 13.64295851, 145.13527778 13.64166667)'; 

 --This shape looks like this

enter image description here

 select @data, @data.STAsText()

 --Convert to polygon it

DECLARE @Polygon geography  
SET @Polygon =   geography::STGeomFromWKB(  0x01 + 0x03000000 + 0x01000000   + SUBSTRING(@data.STAsBinary(),6,datalength(@data.STAsBinary())),@data.STSrid)  
select @Polygon

The result is wrong like below. Return wrong shape

Upvotes: 2

Views: 6906

Answers (3)

Ben Thul
Ben Thul

Reputation: 32687

If you change that very last line to

select @Polygon.ReorientObject();

I think you'll find the desired result. Your linestring has what is called a ring orientation problem. The order in which you specify the points is important. As defined, the polygon is the entire globe minus the intended polygon. By calling ReorientObject() on the polygon, it, well… reorients the object.

Upvotes: 3

NanoHead
NanoHead

Reputation: 159

The ReorientObject is 50% of the problem. We only need to do this if the EnvelopeAngle is under 90 degree.

 /// <summary>
        /// Fixing ring orientation problem By calling ReorientObject() for all any shapes
        /// Shape must be reposition before insert into the database otherwise you have the wrong result especially with the polygon
        /// SQL Script example
        //        DECLARE @Polygon geography;
        //SET @Polygon = 'POLYGON((145.13527778 13.64166667, 144.74694444 13.47666667, 144.63722222 13.51194444, 144.61944444 13.5875, 144.62337227 13.60962359, 144.62777753 13.63154738, 144.63412072 13.65301205, 144.64235403 13.6738541, 144.65241522 13.69391474, 144.66422807 13.71304108, 144.67770294 13.73108731, 144.69273746 13.74791585, 144.70921729 13.76339835, 144.72701699 13.7774167,144.74600098 13.78986396,144.76602456 13.80064514,144.78693505 13.80967796,144.80857293 13.81689348,144.83077307 13.82223661,144.85336602 13.82566656,144.87617929 13.82715716,144.8990387 13.826697,144.92176971 13.82428962,144.94419878 13.81995338,144.96615467 13.8137214,144.98746983 13.80564125,145.0079816 13.79577461,145.02753354 13.78419678,145.0459766 13.77099612,145.06317023 13.75627335,145.07898353 13.74014079,145.09329617 13.7227215,145.10599934 13.70414829,145.1169966 13.68456278,145.12620454 13.66411424, 145.13355348 13.64295851, 145.13527778 13.64166667))'; 
        // --This shape looks like this, you have to zoom in to see, it is really small
        //select @Polygon, 'error here' as ErrorHere

        //set @[email protected]();
        //select @Polygon;

        //--Only convert if the EnvelopeAngle> 90
        //select 
        //case when @Polygon.EnvelopeAngle() > 90 then
        //@Polygon.ReorientObject()
        //  else
        //   @Polygon
        //end  

        //--Do not duplicate ReorientObject it will cost error like below
        //set @Polygon = @Polygon.ReorientObject();
        //        select @Polygon;

        /// </summary>
        /// <param name="dataPoints">Example POLYGON((145.13527778 13.64166667, 144.74694444 13.47666667, 144.63722222 13.51194444, 144.61944444 13.5875, 144.62337227 13.60962359, 144.62777753 13.63154738, 144.63412072 13.65301205, 144.64235403 13.6738541, 144.65241522 13.69391474, 144.66422807 13.71304108, 144.67770294 13.73108731, 144.69273746 13.74791585, 144.70921729 13.76339835, 144.72701699 13.7774167,144.74600098 13.78986396,144.76602456 13.80064514,144.78693505 13.80967796,144.80857293 13.81689348,144.83077307 13.82223661,144.85336602 13.82566656,144.87617929 13.82715716,144.8990387 13.826697,144.92176971 13.82428962,144.94419878 13.81995338,144.96615467 13.8137214,144.98746983 13.80564125,145.0079816 13.79577461,145.02753354 13.78419678,145.0459766 13.77099612,145.06317023 13.75627335,145.07898353 13.74014079,145.09329617 13.7227215,145.10599934 13.70414829,145.1169966 13.68456278,145.12620454 13.66411424, 145.13355348 13.64295851, 145.13527778 13.64166667))</param>
        /// <returns></returns>
        public static string ReorientObject(string dataPoints)
        {
            //Convert to DbGeography
            DbGeography newGeography = DbGeography.FromText(dataPoints.ToString(), 4326);

            //Fixing ring orientation problem By calling ReorientObject() on the polygon
            SqlGeography parseData = SqlGeography.Parse(dataPoints);
            if (parseData.EnvelopeAngle() > 90)
            {
                parseData= parseData.ReorientObject();
            }
            //Take the new reposition of the poing
            dataPoints = DbGeography.FromText(parseData.ToString(), 4326).AsText();
            return dataPoints;
        }

Upvotes: 0

Jacob H
Jacob H

Reputation: 2505

This is a difficult question to answer because you are getting the expected results, but the question asks how to convert a LINESTRING to a POLYGON. This answer will use a REPLACE but surely it can be done other ways (such as the method you tried). Additionally, we will add a bit of "unnecessary" code to show the methodology. This is based on the assumption that the source values are in a closed LINESTRING format.

See this set of queries:

DECLARE @predata varchar(MAX) = 'LINESTRING(145.13527778 13.64166667, 144.74694444 13.47666667, 144.63722222 13.51194444, 144.61944444 13.5875, 144.62337227 13.60962359, 144.62777753 13.63154738, 144.63412072 13.65301205, 144.64235403 13.6738541, 144.65241522 13.69391474, 144.66422807 13.71304108, 144.67770294 13.73108731, 144.69273746 13.74791585, 144.70921729 13.76339835, 144.72701699 13.7774167,144.74600098 13.78986396,144.76602456 13.80064514,144.78693505 13.80967796,144.80857293 13.81689348,144.83077307 13.82223661,144.85336602 13.82566656,144.87617929 13.82715716,144.8990387 13.826697,144.92176971 13.82428962,144.94419878 13.81995338,144.96615467 13.8137214,144.98746983 13.80564125,145.0079816 13.79577461,145.02753354 13.78419678,145.0459766 13.77099612,145.06317023 13.75627335,145.07898353 13.74014079,145.09329617 13.7227215,145.10599934 13.70414829,145.1169966 13.68456278,145.12620454 13.66411424, 145.13355348 13.64295851, 145.13527778 13.64166667)';
DECLARE @data geography;
DECLARE @linestring geography = @predata;
DECLARE @srid int;
SET @predata = REPLACE(@predata, 'LINESTRING', 'POLYGON(') + ')';
SELECT @predata AS PolygonString
SET @data = geography::STGeomFromText(@predata, @linestring.STSrid);
SELECT @data AS GeographyPolygon

After declaring the variables, I did a simple REPLACE on the string/varchar representation of the LINESTRING to turn it to a POLYGON.

SET @predata = REPLACE(@predata, 'LINESTRING', 'POLYGON(') + ')';

And the result:

PolygonString
POLYGON((145.13527778 13.64166667, 144.74694444 13.47666667, 144.63722222 13.51194444, 144.61944444 13.5875, 144.62337227 13.60962359, 144.62777753 13.63154738, 144.63412072 13.65301205, 144.64235403 13.6738541, 144.65241522 13.69391474, 144.66422807 13.71304108, 144.67770294 13.73108731, 144.69273746 13.74791585, 144.70921729 13.76339835, 144.72701699 13.7774167,144.74600098 13.78986396,144.76602456 13.80064514,144.78693505 13.80967796,144.80857293 13.81689348,144.83077307 13.82223661,144.85336602 13.82566656,144.87617929 13.82715716,144.8990387 13.826697,144.92176971 13.82428962,144.94419878 13.81995338,144.96615467 13.8137214,144.98746983 13.80564125,145.0079816 13.79577461,145.02753354 13.78419678,145.0459766 13.77099612,145.06317023 13.75627335,145.07898353 13.74014079,145.09329617 13.7227215,145.10599934 13.70414829,145.1169966 13.68456278,145.12620454 13.66411424, 145.13355348 13.64295851, 145.13527778 13.64166667))

After we have a valid POLYGON string value with a + string concatenation for the final parenthesis, we can pass it to the STGeomFromText function as such:

SET @data =  geography::STGeomFromText(@predata, @linestring.STSrid);

Finally, by selecting the geography type @data variable we arrive at the Spatial result.

SELECT @data AS GeographyPolygon

Which is the same polygon that you have displayed. The scale is very different than the scale of the LINESTRING value as it's placed on a full geography plane representation of SRID 4326. The definition of such:

http://spatialreference.org/ref/epsg/wgs-84/

Since the projected bounds of SRID 4326 are:

Projected Bounds: -180.0000, -90.0000, 180.0000, 90.0000

This means the scale of the results is massive compared to the initial LINESTRING. If you zoom in on the 9°-18° by 140°-160° quadrants, you will see the polygon.

Upvotes: 1

Related Questions