5

I'm new to sql geometry. I've a table that stores polygon or multipolygon in a column. Below given a sample stringified row. Latitude, longitude, geom columns respectively

  36.846642    -119.613123            POLYGON ((-120.67037807500749 36.138281341834187, -120.5952518160816 36.488562090109014, -121.21937765946595 36.910886396539652, -121.24827237443743 37.089753161616166, -120.96510416771677 37.345632006100615, -120.98244099669967 37.400285739873993, -120.38720986828683 37.633806238723878, -120.39298881128113 37.683491451245125, -120.31208360936094 37.653680323732374, -120.34097832433243 37.725723881888193, -120.08092588958895 37.827578567556756, -119.80931556885689 37.755535009400944, -119.53770524812481 37.9045906469647, -119.26031598439843 37.725723881888193, -119.12162135253524 37.733176663766379, -119.01760037863787 37.584121026202624, -118.79800054485449 37.487234861786177, -118.6650848559856 37.154343937893792, -118.43970607920792 37.059942034103415, -118.36457982028202 36.692271461446147, -118.09874844254425 36.346959234423444, -117.98316958265826 35.867496933593358, -118.00628535463547 35.78800059355936, -120.21384157845785 35.790484854185422, -120.32364149534953 35.909729364236426, -120.67037807500749 36.138281341834187))

Below given raw data from the same row

36.846642      -119.613123            0xE610000001041A00000035077179E72A5EC09469F833B311424020BD129B18265EC0B48ADB33893E4240463898480A4E5EC02AC1E9EC97744240612DD0B1E34F5EC051ED16087D8B4240EB644544C43D5EC0965369AB3DAC4240C8C43350E03E5EC030E828903CB34240BF3EE60BC8185EC0F0A8159020D142405E098BBA26195EC0C118DBA57CD74240AAF4872DF9135EC0103CFECBABD34240C5E9BF96D2155EC067512985E4DC4240CB4BC8E32D055EC06E433118EEE94240931887D3CBF35DC0182E065FB5E042405AE545C369E25DC08D7D56A0C9F3424082E75F04A9D05DC067512985E4DC424096E8ECA4C8C75DC09388A0BBD8DD424066A9565D20C15DC01E39507AC4CA4240C695E07012B35DC0DE6B42B65DBE4240796112C090AA5DC07BCBCA8AC19342403A83F724249C5DC0A0103A2EAC8742402439994655975DC0C36EEB599C5842408BD0FCE451865DC06B720229692C42401CFC1C40EC7E5DC0F694B7230AEF41409926B0FA66805DC074481534DDE4414018809694AF0D5EC0D85A929B2EE54140E889D18AB6145EC09CCD050372F4414035077179E72A5EC09469F833B311424001000000020000000001000000FFFFFFFF0000000003

My problem is, 

    > DECLARE @g geometry;   DECLARE @h geometry;   SET @g =
        > geometry::STGeomFromText('POLYGON ((-120.67037807500749
        > 36.138281341834187, -120.5952518160816 36.488562090109014, -121.21937765946595 36.910886396539652, -121.24827237443743 37.089753161616166, -120.96510416771677 37.345632006100615, -120.98244099669967 37.400285739873993, -120.38720986828683 37.633806238723878, -120.39298881128113 37.683491451245125, -120.31208360936094 37.653680323732374, -120.34097832433243 37.725723881888193, -120.08092588958895 37.827578567556756, -119.80931556885689 37.755535009400944, -119.53770524812481 37.9045906469647, -119.26031598439843 37.725723881888193, -119.12162135253524 37.733176663766379, -119.01760037863787 37.584121026202624, -118.79800054485449 37.487234861786177, -118.6650848559856 37.154343937893792, -118.43970607920792 37.059942034103415, -118.36457982028202 36.692271461446147, -118.09874844254425 36.346959234423444, -117.98316958265826 35.867496933593358, -118.00628535463547 35.78800059355936, -120.21384157845785 35.790484854185422, -120.32364149534953 35.909729364236426, -120.67037807500749 36.138281341834187))', 0);   
    >SET @h = geometry::STGeomFromText('POINT(-119.61334353534343 36.84664456779879)', 0);   
>SELECT @g.STContains(@h);

Above code works perfectly. But, whe I try to select it from the table, it is not working. query below

SET @h = geometry::STGeomFromText('POINT(-119.61334353534343 36.84664456779879)', 0);  
SELECT g.geom.STContains(@h) from mygeotable g;

What am I doing wrong here??

Improve this question
2
  • What version of SQL Server are you on? Have run the queries you've posted on SS2012 and it worked fine.
    – Alex Tereshenkov
    Feb 12, 2018 at 13:22
  • I'm on 2012. and the above query, when i'm declaring geometry object manually, is working fine. But its not selecting row when i use table. I ran STIsValid() and all rows are fine
    – Manu Mohan
    Feb 12, 2018 at 13:30

1 Answer 1

Reset to default
2

If you execute this query, you should get 1 and 1. As a comment, you probably would like to use a meaningful spatial reference system id such as WGS84 (WKID 4326) instead of just 0 when you construct geometries. Make sure you run your spatial SQL functions against geometries that are defined using the same spatial references.

create table geometry_polygons(    
    shape geometry
);

DECLARE @g geometry;   
DECLARE @h geometry;   

delete from geometry_polygons

set @g = geometry::STGeomFromText('POLYGON ((-120.67037807500749 36.138281341834187, -120.5952518160816 36.488562090109014, -121.21937765946595 36.910886396539652, -121.24827237443743 37.089753161616166, -120.96510416771677 37.345632006100615, -120.98244099669967 37.400285739873993, -120.38720986828683 37.633806238723878, -120.39298881128113 37.683491451245125, -120.31208360936094 37.653680323732374, -120.34097832433243 37.725723881888193, -120.08092588958895 37.827578567556756, -119.80931556885689 37.755535009400944, -119.53770524812481 37.9045906469647, -119.26031598439843 37.725723881888193, -119.12162135253524 37.733176663766379, -119.01760037863787 37.584121026202624, -118.79800054485449 37.487234861786177, -118.6650848559856 37.154343937893792, -118.43970607920792 37.059942034103415, -118.36457982028202 36.692271461446147, -118.09874844254425 36.346959234423444, -117.98316958265826 35.867496933593358, -118.00628535463547 35.78800059355936, -120.21384157845785 35.790484854185422, -120.32364149534953 35.909729364236426, -120.67037807500749 36.138281341834187))', 4326);
insert into geometry_polygons (shape) values (@g);

SET @g = geometry::STGeomFromText('POLYGON ((-120.67037807500749 36.138281341834187, -120.5952518160816 36.488562090109014, -121.21937765946595 36.910886396539652, -121.24827237443743 37.089753161616166, -120.96510416771677 37.345632006100615, -120.98244099669967 37.400285739873993, -120.38720986828683 37.633806238723878, -120.39298881128113 37.683491451245125, -120.31208360936094 37.653680323732374, -120.34097832433243 37.725723881888193, -120.08092588958895 37.827578567556756, -119.80931556885689 37.755535009400944, -119.53770524812481 37.9045906469647, -119.26031598439843 37.725723881888193, -119.12162135253524 37.733176663766379, -119.01760037863787 37.584121026202624, -118.79800054485449 37.487234861786177, -118.6650848559856 37.154343937893792, -118.43970607920792 37.059942034103415, -118.36457982028202 36.692271461446147, -118.09874844254425 36.346959234423444, -117.98316958265826 35.867496933593358, -118.00628535463547 35.78800059355936, -120.21384157845785 35.790484854185422, -120.32364149534953 35.909729364236426, -120.67037807500749 36.138281341834187))', 4326);   
SET @h = geometry::STGeomFromText('POINT(-119.61334353534343 36.84664456779879)', 4326);   
SELECT @g.STContains(@h);

SET @h = geometry::STGeomFromText('POINT(-119.61334353534343 36.84664456779879)', 4326); 
SELECT g.shape.STContains(@h) from geometry_polygons g; 

drop table geometry_polygons
Improve this answer
2
  • Super. I already found this issue. I found SRID by using below query. select distinct SP_GEOMETRY.STSrid from dbo.MYTABLE. And applied that SRID in the object creation query instead of zero. My exact issue was using different SRID
    – Manu Mohan
    Feb 12, 2018 at 13:56
  • Great, glad it worked out well.
    – Alex Tereshenkov
    Feb 12, 2018 at 13:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.