topic Re: Get SQL views to completely draw in AGOL in Geodatabase Questions

Get SQL views to completely draw in AGOL

JayJohnsonWashoeCounty — Wed, 14 Oct 2020 21:45:50 GMT

We have had trouble getting spatial views (typically points) created in SQL to behave in AGOL. Sometimes they will only draw a few of the features, or most, but not all the features. Seems like different points will draw (or NOT) as we zoom in and out. Is there a guide to best practices for creating a view in SQL and then doing whatever it is that ESRI needs to make it happy with the view?

I imagine that this SOP would include registering the view with SDE and maybe adding a GlobalID, plus possibly a bunch of other steps.

Can anyone point me to a comprehensive resource? Melissa Jarman‌

Thanks,

Jay

Re: Get SQL views to completely draw in AGOL

George_Thompson — Thu, 15 Oct 2020 10:55:27 GMT

Does the view work correctly from the Server end point (in ArcGIS Pro / JS Viewer) before registering it with ArcGIS Online?

What is the max # of records for the service?

How many records are in the view?

Is the view joining between 2 different databases?

Re: Get SQL views to completely draw in AGOL

JayJohnsonWashoeCounty — Thu, 15 Oct 2020 14:34:03 GMT

George,

No, initially the view did not work correctly in Pro - it appeared to be drawing everything when zoomed way out, but as I zoomed in features would start to drop out (weird, I know). When you say "registered with AGOL", are you talking about registering it with SDE (right-click in Catalog) or something else?

Max # records for service = 2,000

Total Records in View = 11,046 (points)

Yes, this particular view is a join of two different databases. But we've had similar trouble with views before.

Having said that, we've apparently fixed our issue. On the view side of things there were some things not quite right. Changes we made included "return top 100%" and fixing some issue with the OBJECTIDs (casting a source field as INT to get this to work properly). Initially, when we were having issues, I noticed that SOMEHOW every other record had an OBJECTID = 0 (yeah, that could be a problem, eh?).

What I'm really in need of is a tutorial or detailed step-by-step example starting with a non-GIS table (containing XY values) in SDE and the whole process of creating a spatial view from it in SQL, with all the bells and whistles to make ESRI in general and AGOL in particular happy with the result.

Thanks for taking an interest,

Jay

Re: Get SQL views to completely draw in AGOL

CraigSwadner — Thu, 15 Oct 2020 14:47:01 GMT

Jay,

Here is an example of a spatial view we use, the crw_land view is a join between the city land files and the county appraiser’s data joined to the parcel layer. One view/table needs to own the objectids, or you can always do a row over sql statement (however those don’t perform all that well). All that said before you start publishing data the sql view/table stuff should be worked out first.

SELECT gis.crw_gisland.OBJECTID, gis.FABRICPARCELS_V.Name AS Strap, gis.crw_gisland.Strap AS Owner_Strap, gis.crw_gisland.Block, gis.crw_gisland.Lot, gis.crw_gisland.Street_Number, gis.crw_gisland.Site_Address,

gis.crw_gisland.SITE_UNIT_NO AS Site_Unit_Number, gis.crw_gisland.SiteZip, gis.crw_gisland.Owner_Name, gis.crw_gisland.OwnerOthers, gis.crw_gisland.OwnerCareOf, gis.crw_gisland.Mailing_Addr1,

gis.crw_gisland.Mailing_Addr2, gis.crw_gisland.Mailing_City, gis.crw_gisland.Mailing_State, gis.crw_gisland.Mailing_Zip, gis.crw_gisland.Improved, gis.crw_gisland.Foreign_Address, gis.crw_gisland.State_Use_Code,

gis.crw_gisland.Future_Land_Use, gis.crw_gisland.Zoning, gis.crw_gisland.Legal, gis.crw_gisland.Council_District, gis.crw_gisland.CO_Date, gis.crw_gisland.Assessed, gis.crw_gisland.Building, gis.crw_gisland.Land,

gis.crw_gisland.WATER, gis.crw_gisland.SEWER, gis.crw_gisland.IRRIGATION, gis.crw_gisland.GACUNT, gis.crw_gisland.Building_Impervious, gis.crw_gisland.Open_Air_Impervious, gis.crw_gisland.Coastal_Hazard,

gis.crw_gisland.FolioID, gis.crw_gisland.Flood_Zone, gis.crw_gisland.FIRM_Panel_No, gis.crw_gisland.SEWERDISTRICT AS Sewer_Impact_Dist, gis.crw_gisland.PARKING_ASSESS_DATE,

gis.crw_gisland.SEAWALL_ASSESS_DATE, gis.crw_gisland.SEAWALLASSESSMENT, gis.crw_gisland.PARKGLOTASSESSMENT, gis.crw_gisland.Confidential, gis.crw_gisland.GEOTYPE,

gis.FABRICPARCELS_V.OBJECTID AS parcelobj, gis.FABRICPARCELS_V.Shape

FROM gis.crw_gisland INNER JOIN

gis.FABRICPARCELS_V ON gis.crw_gisland.FolioID = gis.FABRICPARCELS_V.FolioID

WHERE (gis.crw_gisland.Confidential IS NULL)

Craig Swadner (GIS Coordinator)

City of Cape Coral

1015 Cultural Park Blvd.

Cape Coral, Fl 33990

Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net

Re: Get SQL views to completely draw in AGOL

George_Thompson — Thu, 15 Oct 2020 15:54:46 GMT

Here is the best workflow to accomplish what you are trying to do: Views in an enterprise geodatabase—ArcGIS Pro | Documentation