topic Re: Issues adding a spatial reference to a sql view in ArcGIS Pro Questions

Issues adding a spatial reference to a sql view

KevinDurkee — Mon, 25 Jul 2022 19:41:56 GMT

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

Re: Issues adding a spatial reference to a sql view

RyanTucker — Mon, 25 Jul 2022 23:17:56 GMT

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

Re: Issues adding a spatial reference to a sql view

KevinDurkee — Tue, 26 Jul 2022 14:37:35 GMT

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.

Re: Issues adding a spatial reference to a sql view

RyanTucker — Tue, 26 Jul 2022 21:04:31 GMT

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

SET SHAPE = geometry::Polygon(26910)