Issues adding a spatial reference to a sql view

Question

I'm looking at adding a spatial reference to my database view, I'm fairly new to SQL and not entirely sure the syntax I need to use.

My query currently looks like this:

SELECT UniqueID, CASE_NUMBER, PROJECT_NAME, APPLICANT, SUB_TYPE_DEFAULT_TEXT, LOCATION, SUB_TYPE_DESC, STATUS_DESC, DATE_ACCEPTED, IMAGE_URL, PDF_URL, STATUS_COMMENTS, PUBLIC_VIEW, SHAPE

FROM *MYDATA**

Query works fine but the base feature class that the tables are pulling from doesn't have a coordinate system.

I was thinking that I could define the SHAPE as the SRID I am looking for, so something like this:

SELECT UniqueID, CASE_NUMBER, PROJECT_NAME, APPLICANT, SUB_TYPE_DEFAULT_TEXT, LOCATION, SUB_TYPE_DESC, STATUS_DESC, DATE_ACCEPTED, IMAGE_URL, PDF_URL, STATUS_COMMENTS, PUBLIC_VIEW, geometry::Polygon(26910) AS SHAPE

FROM *MYDATA**

geometry:Polygon(26910)

That threw back a syntax error and not entirely should how to proceed.

Any help would be much appreciated!

Thanks

RyanTucker · Answer

What about using a SET statement after the view has been defined?

SET SHAPE = geometry::Polygon(26910)

KevinDurkee · Answer

Thanks Ryan, my shape field is already in my select statement, however that shape field doesn't have a coordinate system, that's what I would like to add.

RyanTucker · Answer

You should be able to put your shape field in your select statement.

The example below joins my property assessor records (many) to parcels (1). By adding the shape field from the Parcels polygon to the view definition it creates a polygon for each Assessor table record.

CREATE VIEW  Test AS 
SELECT        
ASSESSORSBF.LOWPARCELID, 
ASSESSORSBF.PARCELID,                   
PARCELS.ZoningCode, 
PARCELS.Condominium, 
PARCELS.NumberUnits, 
ASSESSORSBF.Landlord_Number_of_Units, 
PARCELS.Shape
FROM            
ASSESSORSBF LEFT OUTER JOIN                   
PARCELS ON ASSESSORSBF.LOWPARCELID = PARCELS.LowParcelID