CODEWITHSUNDEEP
sql-serverspatialspatial-query
arcee123
arcee123

Reputation: 243

how do I join against a geographic point and a geometry polygon

I have a sql server 2008 engine. In my database I have two tables. Table A has a PK1 column, and a geography data type used for a POINT value. Table B has a PK2 column with a geometric data type used for a POLYGON value. I need to output a table C with columns PK1 and PK2. To populate this table, I need to set the select join for Geographic POINT is in GEOMETRIC POLYGON.

I have tried this query:

SELECT p.PropertyID
   ,p.ParcelID
   ,t.GEOID
FROM [bhdev].[dbo].[REF_TRACTS] t
join bhdev.dbo.PropertyParameters p on p.Geolocation.STIntersects(t.geom)=1

This results in the following error: Operand type clash: sys.geometry is incompatible with sys.geography

How do I get the join to work? Thanks

Upvotes: 1

Views: 1840

Answers (1)

Ben Thul
Ben Thul

Reputation: 32707

The clue is in the error text:

Operand type clash: sys.geometry is incompatible with sys.geography

(emphasis mine) My guess is that [bhdev].[dbo].[REF_TRACTS].geom is a geometry type column instead of geography like it should be. You're going to have to convert it with something like:

geography::Point([bhdev].[dbo].[REF_TRACTS].geom.X, 
    [bhdev].[dbo].[REF_TRACTS].geom.Y, 
    3426
)

(or switch X & Y, depending on which one you stored your latitude in) And my suggestion would be to convert all the rows by adding a new column to your table, doing the conversion above, adjusting all the code that references the geom column to reference the new column, and finally rename the old column to something else. Once you have no code that breaks after the rename, drop the column completely.

Upvotes: 3

Related Questions