Displaying a SQL view with Lat/Longs

Anonymous User

Here's the scenario:

I have two tables in a SQl database, one with lat/longs and the other with data I want to see.

I have created a view in SQL that shows what we need, but how do we view that in ArcGIS? We do not want to create a new table from xy data (static) and we need the data to update when the users and another record. Hence the view.

BrianOevermann

We've successfully created various views from our permitting system that, ahem, permit me to add those tables as query layers (points) in ArcGIS.

Full credit for this code goes to our database admin guru whom works with our permitting and other databases--all SQL Server-based.

Here is his SQL code snippet:

SELECT

geography::Point(CASE ISNULL(geo.LAT,0)

                     WHEN 0 THEN '47.56580'

                           ELSE geo.LAT END,

                CASE ISNULL(geo.LON,0)

                     WHEN 0 THEN '-122.07409'

                           ELSE geo.LON END

                , 4326)  AS GeomPoint

FROM dbo.. etc etc

The basic syntax is:

geography::Point(lat,long,4326) with '4326' being the WKID of the coordinate system.

FYI, geo.LAT and geo.LON are the field names for latitude/longitude in our permitting database. The CASE statement 'hard codes' a coordinate for any records that don't have a lat/long and puts them in the middle of a lake adjacent to our City. We chose the middle of the lake so it is obviously not an explicit location and because these are permits that have multiple locations throughout the City--just one example might be a special event permit for a 'fun run' that has a route winding through the City. On our internal viewers showing these permits I also have a square polygon container at the location in the lake to draw attention to the 'bucket' of non-explicitly located permits and indicate that the point(s) in the lake are not an error.

Edit: I'll add, also, that when creating a query layer in ArcGIS you will need a uniqueID field (integer--think about it as the ObjectID). Your view may already contain a suitable field. If not, you will need to create something to use. Our permit guru has sometimes gotten creative by morphing, say, the building permit (both alpha and numeric characters) into an integer field by substituting an integer 'code' for the alpha permit type portion. Sorry, no code available to share on this...