Automatically creating 2D-point features for ArcGIS Desktop from addresses in SQL database?

Question

I currently have an SQL-Table which contains our buildings. I already added the table and now want to create features ( 2D-Points ) from that information. The biggest problem I have is that the "location" of those buildings is stored as an address.

I think I may have to use python and ArcPy to automatically add those buildings from the database as features on my map. However, I am new to ArcGIS, so maybe I don't know about some functionality that comes with it. So:

Is there a tool to read addresses from a data source and automatically create points out of those?

Answer 1

What information is in the sql-table? Are you saying it is a sql spatial table or just a listing of building names with addresses?

1. if just addresses you would need to use the geocoding tool in arcmap.

   (ver9.3.x) Under the tools pulldown select geocoding

   (ver10) select the geocoding toolbar. This will create a special point file with all the records in your address list with a code indicating whether the address geocoded correctly (or not).

   BTW you may need to parse the addresses into seperate fields or use the complete address with the appropriate geocoder.

   You can read more about geocoding in the arcmap help search>geocoding

2. if the data contains x-y information you can simply add the table to arcmap> right click on the table and select Display XY Data. You would then select the coordinate system your data is in (usually going to be [geographic system WGS84]) lat lon.

• brot: It is just the list of building names with their addresses, not a spatial table. Sounds like the geocoding tool is exactly what i need. I need to read through the documentation first, but i think this answers my question. I will report back as soon as possible (and when i have enough reputation to choose an answer ;) ) Thanks!

Answer 2

If you can export your data into an excel file or format it and copy it into the web form, you can use http://www.batchgeo.com/ to geocode. Note that this solution may not work if you are not allowed to send the information over the internet, or if you require better geocoding than the website can provide.

• brot: I dont think i am allowed to upload those tables, but this website looks really useful for private projects, so thanks anyway! :)

Answer 3

As ESRI says in his documents,you have 3 choices to show your spatial data in ArcGIS clients when your data has unknown format for these software collection:

1. Use "Custom Layer" method,mean you add a custom layer to ArcMap and add you data as points,polylines,polygons or ... to this layer(in its Draw method),in this way even you can have different geometry types in one layer.
2. Use "plug in data source". here you must have a class which implements some interfaces and you make your data known via these interfaces.
3. Use "OLE DB Provider" which is compliant with OGC simple feature specification for OLE/COM. this is the most difficult way and you must have nice programming skills to produce some thing like "SQL Native client",but spatial enabled one.

The simplest way is using "Custom Layer".Let me know if you need a sample code,so I send you,I can't see here any link to attach my sample code to this post,or let's ask the site admin.

Here is a sample custom layer which opens a non_geodatabase access file and interpret and shows every record as afeature.It's a quite simple table which you can use ArcTools to show too,but here I put it as an example,it may be any data source,with any type field which contains spatial data.It's the main class,and I implemented only necessary interfaces.Hope it's useful.

using System;
using System.Collections;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using ESRI.ArcGIS.Carto;
using ESRI.ArcGIS.esriSystem;
using ESRI.ArcGIS.Geodatabase;
using ESRI.ArcGIS.Geometry;
using ESRI.ArcGIS.Display;
namespace ACustomLayer
{
 [Guid("81ccf4ce-8d2f-4cfc-af63-e13c5a96c9cb")]
 [ClassInterface(ClassInterfaceType.None)]
 [ProgId("ACustomLayer.gpCLayer")]
public class gpCLayer:ILayer,IGeoDataset,ILayerGeneralProperties,IPersistVariant,IDisposable,System.Collections.IEnumerable,IFeatureLayer,ITable
 {
 #region Private Properties
 private DataTable mFeatures = null;
 private IEnvelope mpExtent = null;
 private ISpatialReference mpSRef = null;
 private string mName = "MyLayer";
 private bool mIsVisible = true;
 private bool mIsCachable = true;
 private bool mIsValid = true;
 private double mMaxScale = 0;
 private double mMinScale = 0;
 private bool mShowTips = false;
 private ISymbol mpDSymbol = null;
 private ISymbol mpSSymbol = null;
 private IDisplay mpDis = null;
 #endregion
 #region Constructors & Destructor
 public gpCLayer(DataTable dt)
 {
 this.mFeatures = dt;
 IRgbColor pC = new RgbColorClass();
 pC.Blue = 255; pC.Green = 10; pC.Red = 30;
 this.mpDSymbol = new SimpleMarkerSymbolClass();
 (this.mpDSymbol as ISimpleMarkerSymbol).Size = 6;
 (this.mpDSymbol as ISimpleMarkerSymbol).Style = esriSimpleMarkerStyle.esriSMSCircle;
 (this.mpDSymbol as ISimpleMarkerSymbol).Color = (IColor)pC;
 pC.Blue = 200; pC.Green = 200; pC.Red = 30;
 this.mpSSymbol = new SimpleMarkerSymbolClass();
 (this.mpSSymbol as ISimpleMarkerSymbol).Size = 6;
 (this.mpSSymbol as ISimpleMarkerSymbol).Style = esriSimpleMarkerStyle.esriSMSCircle;
 (this.mpSSymbol as ISimpleMarkerSymbol).Color = (IColor)pC;
 Marshal.ReleaseComObject(pC);
 ISpatialReferenceFactory spatialRefFatcory = new SpatialReferenceEnvironmentClass();
 IProjectedCoordinateSystem prjCoordSys = spatialRefFatcory.CreateProjectedCoordinateSystem((int)esriSRProjCSType.esriSRProjCS_WGS1984UTM_40N);
 this.mpSRef = prjCoordSys as ISpatialReference;
 this.mpSRef.SetDomain(0, 500000, 0, 4000000);
 this.mpExtent = new EnvelopeClass();
 this.mpExtent.SpatialReference = this.mpSRef;
 }
 #endregion
 #region Public Properties
 #endregion
 #region Methods
 #endregion
 #region ILayer Members
 public IEnvelope AreaOfInterest
 {
 get 
 {
 return this.mpExtent;
 }
 }
 public bool Cached
 {
 get
 {
 return this.mIsCachable;
 }
 set
 {
 this.mIsCachable = value;
 }
 }
 public void Draw(esriDrawPhase DrawPhase, ESRI.ArcGIS.Display.IDisplay Display, ITrackCancel TrackCancel)
 {
 if (DrawPhase != esriDrawPhase.esriDPGeography && DrawPhase != esriDrawPhase.esriDPSelection)
 return;
 if (Display == null)
 return;
 if (this.mpDis == null)
 this.mpDis = Display;
 IPoint pP = new PointClass();
 pP.SpatialReference = this.mpSRef;
 IEnvelope pEnv = Display.DisplayTransformation.FittedBounds;
 double x, y;
 foreach (DataRow dr in this.mFeatures.Rows)
 {
 if (!TrackCancel.Continue())
 break;
 x = double.Parse(dr["X"].ToString());
 y = double.Parse(dr["Y"].ToString());
 bool selected = dr["Selected"].ToString().ToLower().IndexOf("true") >= 0;
 if (DrawPhase == esriDrawPhase.esriDPSelection && !selected)
 continue;
 if (!(x <= pEnv.XMax && x > pEnv.XMin && y >= pEnv.YMin && y <= pEnv.YMax))
 continue;
 pP.X = x;
 pP.Y = y;
 if (selected)
 Display.SetSymbol(this.mpSSymbol);
 else
 Display.SetSymbol(this.mpDSymbol);
 Display.DrawPoint(pP);
 }
 }
 public double MaximumScale
 {
 get
 {
 return this.mMaxScale;
 }
 set
 {
 this.mMaxScale = value;
 }
 }
 public double MinimumScale
 {
 get
 {
 return this.mMinScale;
 }
 set
 {
 this.mMinScale = value;
 }
 }
 public string Name
 {
 get
 {
 return this.mName;
 }
 set
 {
 this.mName = value;
 }
 }
 public bool ShowTips
 {
 get
 {
 return this.mShowTips;
 }
 set
 {
 this.mShowTips = value;
 }
 }
 public ISpatialReference SpatialReference
 {
 get
 {
 return this.mpSRef;
 }
 set 
 {
 this.mpSRef = value;
 }
 }
 public int SupportedDrawPhases
 {
 get 
 {
 return (int)esriDrawPhase.esriDPGeography + (int)esriDrawPhase.esriDPSelection;
 }
 }
 public bool Valid
 {
 get 
 {
 return this.mIsValid;
 }
 }
 public bool Visible
 {
 get
 {
 return this.mIsVisible;
 }
 set
 {
 this.mIsVisible = value;
 }
 }
 public string get_TipText(double x, double y, double Tolerance)
 {
 double xmin = x - Tolerance;
 double xmax = x + Tolerance;
 double ymin = y - Tolerance;
 double ymax = y + Tolerance;
 string qry = "X >= " + xmin.ToString() + " AND X <= " + xmax.ToString() + " AND Y >= " + ymin.ToString() + " AND Y <= " + ymax.ToString();
 DataRow[] rows = this.mFeatures.Select(qry);
 if (0 == rows.Length)
 return string.Empty;
 DataRow r = rows[0];
 return r["Tip"].ToString();
 }
 #endregion
 #region IGeoDataset Members
 public IEnvelope Extent
 {
 get 
 {
 return this.mpExtent;
 }
 }
 #endregion
 #region ILayerGeneralProperties Members
 public double LastMaximumScale
 {
 get 
 {
 return this.mMaxScale;
 }
 }
 public double LastMinimumScale
 {
 get 
 {
 return this.mMinScale;
 }
 }
 public string LayerDescription
 {
 get
 {
 return "";
 }
 set
 {
 }
 }
 #endregion
 #region IPersistVariant Members
 public UID ID
 {
 get 
 {
 UID uid = new UIDClass();
 uid.Value = "ACustomLayer.gpCLayer";
 return uid;
 }
 }
 public void Load(IVariantStream Stream)
 {
 }
 public void Save(IVariantStream Stream)
 {
 throw new Exception("Save HERE.");
 }
 #endregion
 #region IDisposable Members
 public void Dispose()
 {
 Marshal.ReleaseComObject(this.mpSRef);
 Marshal.ReleaseComObject(this.mpExtent);
 Marshal.ReleaseComObject(this.mpDSymbol);
 }
 #endregion
 #region IEnumerable Members
 public IEnumerator GetEnumerator()
 {
 return this.mFeatures.Rows.GetEnumerator();
 }
 #endregion
 #region IFeatureLayer Members
 public string DataSourceType
 {
 get
 {
 return "";
 }
 set
 {
 }
 }
 public string DisplayField
 {
 get
 {
 return "Tip";
 }
 set
 {
 }
 }
 public IFeatureClass FeatureClass
 {
 get
 {
 return null;
 }
 set
 {
 }
 }
 public bool ScaleSymbols
 {
 get
 {
 return true;
 }
 set
 {
 }
 }
 public IFeatureCursor Search(IQueryFilter queryFilter, bool recycling)
 {
 return null;
 }
 public bool Selectable
 {
 get
 {
 return true;
 }
 set
 {
 }
 }
 #endregion
 #region ITable Members
 public void AddField(IField Field)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public void AddIndex(IIndex Index)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public UID CLSID
 {
 get { throw new Exception("The method or operation is not implemented."); }
 }
 public IRow CreateRow()
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public IRowBuffer CreateRowBuffer()
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public void DeleteField(IField Field)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public void DeleteIndex(IIndex Index)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public void DeleteSearchedRows(IQueryFilter QueryFilter)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public UID EXTCLSID
 {
 get { throw new Exception("The method or operation is not implemented."); }
 }
 public object Extension
 {
 get { throw new Exception("The method or operation is not implemented."); }
 }
 public IPropertySet ExtensionProperties
 {
 get { throw new Exception("The method or operation is not implemented."); }
 }
 public IFields Fields
 {
 get { throw new Exception("The method or operation is not implemented."); }
 }
 public int FindField(string Name)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public IRow GetRow(int OID)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public ICursor GetRows(object oids, bool Recycling)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public bool HasOID
 {
 get { throw new Exception("The method or operation is not implemented."); }
 }
 public IIndexes Indexes
 {
 get { throw new Exception("The method or operation is not implemented."); }
 }
 public ICursor Insert(bool useBuffering)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public string OIDFieldName
 {
 get { throw new Exception("The method or operation is not implemented."); }
 }
 public int RowCount(IQueryFilter QueryFilter)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 ICursor ITable.Search(IQueryFilter QueryFilter, bool Recycling)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public ISelectionSet Select(IQueryFilter QueryFilter, esriSelectionType selType, esriSelectionOption selOption, IWorkspace selectionContainer)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public ICursor Update(IQueryFilter QueryFilter, bool Recycling)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 public void UpdateSearchedRows(IQueryFilter QueryFilter, IRowBuffer buffer)
 {
 throw new Exception("The method or operation is not implemented.");
 }
 #endregion
 }
}

• brot: Well, some sample code for the "Custom Layer" method would be really nice - especially because i cant really tell if we both mean the same thing :) . If it isnt possible here, you could add it to dpaste.com (or any other pastebin service) and put a link here. But thank you very much for the Answer!