I found the below code for creating a polygon. I know what a polygon is, but I'm confused about the code. I have my polygon points defined, but where do I bring my table in to match to the polygon lat/longs? I think it is where I "Set @p = geography", but I'm bringing in a file, not setting a column to a specific lat/long.

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL 
    DROP TABLE dbo.SpatialTable;

CREATE TABLE SpatialTable 
    ( id INT IDENTITY (1,1),
    GeogCol1 GEOGRAPHY, 
    GeogCol2 AS GeogCol1.STAsText() );

DECLARE @geom GEOMETRY = 'POLYGON(( 55.297622963050465 -4.627166781574488,
                                    55.25851203752759 -3.5724792815744877,
                                    55.034056344339206 -3.5450134612619877,
                                    54.955277168321636 -3.7647400237619877,
                                    54.92372217917785 -4.113555941730738,
                                    54.96946883538248 -4.561248812824488,
                                    55.151935308382306 -4.712310824543238,
                                    55.297622963050465 -4.627166781574488))';

DECLARE @geog GEOGRAPHY = @geom.MakeValid().STUnion(@geom.STStartPoint()).STAsText()

INSERT INTO SpatialTable (GeogCol1) VALUES (@geog)

SET @g = (SELECT GEOGRAPHY::STPolyFromText(GeogCol2, 4326) FROM dbo.SpatialTable); -- 4326 is a common SRID

SET @p = GEOGRAPHY::STPointFromText('POINT(55.10029008969451 -4.100017953449488)', 4326); -- SRID MUST match polygon

SELECT @p.STIntersects(@g); -- 1 is true, 0 is false

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL 
    DROP TABLE dbo.SpatialTable;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.