60 likes | 141 Views
Learn how to leverage Query Layers in ArcMap Services to fix issues, enable auto-updates, and streamline data integration for AVL applications and more. Explore techniques for handling bad addresses, sensor data, time units, and table editing within ArcMap. By creating views and integrating disparate data sources seamlessly, you can enhance the GIS workflow and simplify data manipulation.
E N D
Show me the ways Inside ArcMap Services Anyone? Polling Places Using them in place of conventional feature classes BaseMapIMS Using them to fix inherent problems Auto Updates AVL like applications • Bad Addresses • Simply looking at a table • Sensor_PT • Combining disparate datasources easily • Unit Time • Making functionality • Editing • With a little work
Real Integration • Table with a geometric component • Either a geometry/geography column • Or simply X/Y values • Create a view to handle attributes • A replacement for SDE views, but better • Add the view to ArcMap • Voila, you have a feature class • Use it just like you would a “regular” feature class
Bad Addresses Select APID, Num, PreDir, StrName, StrType, SecUnit, SecUnitDes, City, geometry::Point(X,Y,3419) as Shape from addrep.dbo.vwAddptFailQC_WithDescriptionsAndAddptData WHERE AddptQCID = 121
Sensor_PT select cast(row_number() over(order by a.SiteID) as int) as seq,GEOMETRY::Point(a.XCoord,a.YCoord,3419) as Shape, a.*,b.SensorGroup,b.LastValidDataTime,b.LastValidDataValue from jocoPub.DL.Sensor_PT a left outer join OP_STORMWATCH2.StormWatch3.dbo.SensorDef b on a.SiteID = b.SiteID where a.XCoord is not null and a.YCoord is not null and b.InService = 1 and b.LastValidDataTime > GETDATE() - 1 and SensorGroup in ( 'Barometric Pressure', 'Flow Volume', 'Peak Wind', 'Rain', 'Relative Humidity', 'Temperature', 'Water Level', 'Wind Direction')
Unit Time • Unit Time select * from (SELECT *, CAST(LEFT(DATETIME, 4) + '-' + SUBSTRING(DATETIME, 5, 2) + '-' + SUBSTRING(DATETIME, 7, 2) + ' ' + SUBSTRING(DATETIME, 9, 2) + ':' + SUBSTRING(DATETIME, 11, 2) + ':' + SUBSTRING(DATETIME, 13, 2) as DATETIME) as dt from dl.SHERIFFUNITS) a CROSS APPLY(SELECT MIN(DT) as dtt FROM (SELECT *, CAST(LEFT(DATETIME, 4) + '-' + SUBSTRING(DATETIME, 5, 2) + '-' + SUBSTRING(DATETIME, 7, 2) + ' ' + SUBSTRING(DATETIME, 9, 2) + ':' + SUBSTRING(DATETIME, 11, 2) + ':' + SUBSTRING(DATETIME, 13, 2) as DATETIME) as dt from dl.SHERIFFUNITS) c WHERE c.UNIT = a.UNIT AND c.dt > a.dt) b