All points hide on a map

484
1
Jump to solution
06-30-2017 07:56 AM
MarkLittell
New Contributor III

I am using ArcGIS 10.5 Desktop and Server pulling a Query Layer from a MS SQL View.

When I move the map where a point falls of the current map extent, hit refresh, all the points remove from the map.

It does this in ArcMap and as a published service.

Thoughts?

Thanks 

Mark

0 Kudos
1 Solution

Accepted Solutions
MarkLittell
New Contributor III

Problem solved... let me expand on my issue and the resolution.

Issue:

I had two services going to two different MS SQL views.

View 1 only displayed icons on the map if all the icons were in the current map extent.

View 2 displayed icons on the map regardless if the icons were in the current map extent or not.

When I grabbed the View, I was casting the coordinates to numeric type. This process has worked fine over the past 3 years. This morning, I had the problem stated above.

Things Discovered:

In ArcMAP, I could do an Identify on the layer after they disappeared from the map - no identify results would appear - but an 'Error' did appear on the Identify window. I was able to mouse over the Error and see a popup explaining it was having issues with converting the varchar type to numeric.

View 1 coordinates came to me, from MS SQL as varchar type.

View 2 coordinates came to me, from MS SQL as int type.

In ArcMAP, looking at the fields in the layer properties you can see the Appearance \ Number Format is set to Numeric for both Views. But the Field Details \ Data Type was set to Float for View 1 and Double in View 2.

Resolution:

Perform a second CAST on View 1 to change the coordinates from varchar type to int type, then cast as numeric.

Like so:

CAST(CAST(Latitude AS int) AS numeric) /1000000 as latitude,  CAST(CAST(Longitude AS int) AS numeric) /-1000000 as longitude

The math part is to add the decimal and negative characters back into the values. The varchar and int type removes the decimal and negative characters.

View solution in original post

0 Kudos
1 Reply
MarkLittell
New Contributor III

Problem solved... let me expand on my issue and the resolution.

Issue:

I had two services going to two different MS SQL views.

View 1 only displayed icons on the map if all the icons were in the current map extent.

View 2 displayed icons on the map regardless if the icons were in the current map extent or not.

When I grabbed the View, I was casting the coordinates to numeric type. This process has worked fine over the past 3 years. This morning, I had the problem stated above.

Things Discovered:

In ArcMAP, I could do an Identify on the layer after they disappeared from the map - no identify results would appear - but an 'Error' did appear on the Identify window. I was able to mouse over the Error and see a popup explaining it was having issues with converting the varchar type to numeric.

View 1 coordinates came to me, from MS SQL as varchar type.

View 2 coordinates came to me, from MS SQL as int type.

In ArcMAP, looking at the fields in the layer properties you can see the Appearance \ Number Format is set to Numeric for both Views. But the Field Details \ Data Type was set to Float for View 1 and Double in View 2.

Resolution:

Perform a second CAST on View 1 to change the coordinates from varchar type to int type, then cast as numeric.

Like so:

CAST(CAST(Latitude AS int) AS numeric) /1000000 as latitude,  CAST(CAST(Longitude AS int) AS numeric) /-1000000 as longitude

The math part is to add the decimal and negative characters back into the values. The varchar and int type removes the decimal and negative characters.

0 Kudos