Add SQL Server view to ArcMap as XY event – Problem resolved

In the past week I came acros a problem of displaying X,Y event based on quite complicated, multitables, multiowners query.
Usually I work with Oracle database, however this time I had to pull out the data from SQL Server database.

I have created sucessfully the View, and served the X,Y data as the MapService through the ArcGIS Server – everything was fine until I had to relate another table and the problems jumped out as there was no OBJECTID field presented in the view.

I dig a bit and found a solution of using indexed Views ( however in my case I was getting:

"Index cannot be created on view '....View_Ix' because the underlying object '...._origin' has a different owner." – that was probably because I had several tables from different users in my query, anyway…

After few hours of checking diferent things I have discovered that if you use the “Make Query Table” tool from ArcToolBox before you make your XY event layer and specifiy which field to use as a key in that tool, it works just great.

I need to test few things yet – so check if the data is being updated properly in that new table etc, but at the moment everything looks promissing.

I hope that hint will be useful for some of you as well.

CAD to File Geodatabase – Python script

In near feature I am going to work on the project where the CAD conversion to esri format will be required. The script below might be useful…


CADtoGeodatabase_conversion (input_cad_datasets, out_gdb_path, out_dataset_name, reference_scale, {spatial_reference})

input_cad_datasets [CAD drawing dataset,…] – The collection of CAD files to convert to geodatabase features. The input parameter will accept CAD data from multiple formats (DWG, DXF, and DGN) in one operation.

Workspace out_gdb_path – The ArcSDE, file, or personal geodatabase where the Output Feature Dataset will be created. The target geodatabase must already exist.

String out_dataset_name – The name of the feature dataset to be created.

Double reference_scale – Enter the scale to use as a reference for the annotation. This sets the scale to which all symbol and text sizes in the annotation will be made relative.

Spatial Reference spatial_reference – (Optional) The spatial reference of the output feature dataset. If you wish to control other aspects of the spatial reference (i.e., the xy, z, m domains, resolutions, tolerances), use the relevant environments.

Code sample:

# Description: Create a feature dataset
# Author: ESRI
# Import system modules
import arcpy
from arcpy import env
# Set workspace
env.workspace = "C:/data"

# Set local variables
input_cad_dataset = "C:/data/City.DWG"
out_gdb_path = "C:/data/HabitatAnalysis.gdb"
out_dataset_name = "analysisresults"
reference_scale = "1000"
spatial_reference = "NAD_1983_StatePlane_California_VI_FIPS_0406_Feet"

# Create a FileGDB for the fds
arcpy.CreateFileGDB_management("C:/data", "HabitatAnalysis.gdb")

# Execute CreateFeaturedataset
arcpy.CadToGeodatabase_conversion(input_cad_dataset, out_gdb_path, out_dataset_name, reference_scale)

Source: ESRI Help
Mapping Specification for DWG/DXF (MSD)


OSTN02 in NTv2 Transformation in ArcGIS Desktop

In its original form OSTN02 consists of a plane 700 km by 1250 km grid of 1 km resolution with an eastings shift and northing shift at each grid node.

The plane grid form of OSTN02 is not easily compatible with some GIS systems.  Some of these systems utilise a transformation data format known as NTv2.  NTv2 format is a gridded format but the grid is realised on a latitude/longitude graticule and the shifts are expressed as differences in latitude and longitude.” OS

Ordnance Survey working with Defence Geographic Centre have produced an NTV2 version of their OSTN02 transformation.

To implement the transformation and make it available in ArcGIS Desktop:

1. Download the data file from OS website:

2. Using Windows Explorer, create a new folder called greatbritain (if it doesn’t already exist) using lowercase letters in (On a 64 bit machine this will be in Program Files (x86) ):

  • 9.x – C:\Program Files\ArcGIS\pedata\ntv2\
  • 10.0 – C:\Program Files\ArcGIS\Desktop10.0\pedata\ntv2\

3. Copy the NTv2 grid file(s) to the greatbritain folder. Note: There is no limit to how many  .gsb files you can store in this folder.

4. Download GEOGTRAN.txt file. Paste that file in some folder on the C drive like C:\PEOBJ (please do not put the GEOGTRAN file to the Program Files Folder).Add an Environment Variable of PEOBJEDITHOME to the System Variables with the value of the folder where the GEOGTRAN file will reside  (e.g. C:\PEOBJ ).

When you download and copy the file into the e.g. C:\PEOBJ – please remove the file extension (txt) leaving the file without extension.

The content of GEOGTRAN file is as follow:

GEOGTRAN, 208339, "OSGB_1936_To_WGS_1984_OSTN02_NTV2",
GEOGTRAN, 208338, "OSGB_1936_To_ETRS_1989_OSTN02_NTV2",
PE_PAR_NAME_DATASET, "Dataset_greatbritain/OSTN02_NTv2"

5. Open Arc Map and set the Layers projection to Geographic projection OSGB36

6. Add any data projected in OSGB36 (geographic) or cartesian British National Grid.

7. Add ESRI_STREETMAP_World_2D or any other data projected in WGS1984 – do not apply any trasformation:

As you can see there will be the data shift of approximately 100m.

8. Right click on Layers then go to Properties and click Transformation button and select one of the new transformation methods from the drop-down list – in our case it will be OSGB_1936_To_WGS_1984_OSTN02_NTV2:

9. Apply the selected transformation – as you can see the WGS84 data has been properly re-projected to OSGB36 using OSTN02 in NTv2 Transformation method:

The above method is working for ArcGIS Desktop 9.2 to 10.0 – it has been tested and is working fine. If you have any questions please do not hesitate to ask.

Please remember – if you have data in British National Grid (Easting, Northing), you need to project it first to OSGB36 (world projection) to use the method described above.