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
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
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.
What about using a SET statement after the view has been defined?
SET SHAPE = geometry::Polygon(26910)