[GIS] Mapping SQL tables in ArcGIS Desktop

arcgis-10.3arcgis-desktopquery-layerspatial-databasesql server

I have several tables in SQL server. I am trying to visualize my data in ArcGIS. However, I have not been able to do so.

I started off my making a database connection in Arc Catalog to my SQL. I did this correctly and I am able to view my data.

Then; I went to ArcGIS 10.3 -> Make Query layer and selected my SQL connection. I then double clicked on the table I wanted. Checked advanced options and validated and clicked next. I then selected the primary key and tried to select the CRS. I could not because it was greyed out. So I decided to continue and finalized. In the end I am only able to see my connection in ArcGIS but no mapped data.

How can I map my data?

enter image description here

Best Answer

First, I hope I understand your setup correctly:

  • You have several tables in SQL, some with tabular data and one table containing Lat/Long columns (probably floating/double precision data type).
  • You would like to join several data tables together and see the information as points in ArcGIS.

Possible solution:

  • You should make your join/view directly in SQL, joining data tables with the lat/long table. You will end up with a view that will have a.Col1, a.Col2, b.ColX, b.Lat, b.Long
  • With the view created, you can add it to ArcMap using the Add XY Data dialog. It is accesible from File/Add XY Data.
  • In the wizzard, select the view from the SQL database, add the Lat and Long columns to Y and X geometry columns.
  • latitude is Y-axis, longitude is X-axis for WGS84, which is code EPSG:3844.
  • the wizzard creates a new point Event Layer, which can be used in GIS analysis or saved as a distinct layer.

Problem with using query layer the way you presented:

Query Layers should be used only if joining geometry tables with data tables, to see the resulting query layer as a geometry in ArcMap. Since your are joining one data table to another, because the Lat/Long is not expressed as geometry (in a SQLGeometry data type) the query Layer will only add a table view in the Table of Contents. You can still use this table to create XY point event layer, similar to the steps described above.

Related Question