CODEWITHSUNDEEP
sqlsql-serverspatial
Fernando Vega
Fernando Vega

Reputation: 527

SQL Server - Query by Geometry Data

Say I have following data in table addresses:

physicalState    physicalPostalCode    geometry
------------------------------------------------------------------------------    
     PA               15340            0xE6100000010CAC1C5A643B1354C02D431CEBE2264440
     OK               74576            0xE6100000010C7DD0B359F50158C079E9263108544140
     WV               26033            0xE6100000010CE8D9ACFA5C2554C0273108AC1CEA4340
     WV               26033            0xE6100000010C36AB3E575B2554C0C3D32B6519EA4340

I want to 

select * 
from addresses 
where geometry = GEOMETRY::STPointFromText('POINT (40.3038 -80.3005)', 4326)

Finding it very difficult to figure this out...

Upvotes: 1

Views: 1816

Answers (2)

Adam Silenko
Adam Silenko

Reputation: 3108

Try use method [STContains] like this condition:

geometry.STContains(GEOMETRY::STPointFromText('POINT (40.3038 -80.3005)', 4326))

Upvotes: 3

Fernando Vega
Fernando Vega

Reputation: 527

OKIE! Figured this one out... One thing worth noting, x/y coordinates were flip flopped. That helped to frustrate things. stupid me.

Either way, this is what in order able to query the database by specific points:

select * from addresses
WHERE geometry.ToString() = 'POINT (-80.3005 40.3038)'

Also, to credit @Adam Silenko, his solution also works as such:

select * 
from addresses
where geometry.STContains(GEOMETRY::STPointFromText('POINT (-80.3005 40.3038)', 4326)) = 1

Thanks!

Upvotes: 0

Related Questions