ThinkGeo.com    |     Documentation    |     Premium Support

Geocoding from SQL Server Data

#1

I am researching the use of Map Suite Geocoder for the new version of our application.  The old version used ESRI's Map Objects, which has been unsupported for some time.


Our application will have all of its data in SQL Server.  I haven't seen any mention in the documentation or forums about using SQL Server data for geocoding. We have had issues with people opening and possibly modifying standalone files, so we're working to keep everything possible inside a password-protected SQL Server database.


Can we use SQL Server with this product?


Thanks,


Allen Huber



#2

Allen, 
  
   At the moment we do not support SQL Server with our Geocoder 4.0 as we have our custom, high performance, file format.  We could however possibly support encryption of the files if you are concerned about user tampering.  We could also do some research about supporting SQL Server, do you have a layout of your data, would it be possible to post the schema of it?  What makes me curious is that we did have a SQL based version with our Map Suite 2.0 platform and we may be able to bring that forward with some tweaks.  The class structure for our 4.0 Geocoder is well designed and very flexible.  We could leverage most of the core classes and just plug in the SQL part.  If you are interested in any of this please let me know. 
  
 David

#3

David, 
  
 Thanks for the response.  I discussed this with my manager yesterday, and he said that if there was interest in supporting SQL Server on Think Geo’s part that we would be willing to get involved. He’s on vacation next week, so I can more or less go in whatever direction things take me if we want to discuss this further.  At the moment we have a SQL Server table (“roads”) which I believe has all of the stuff needed for the geocoding, since this is the same data we use in the current version of our product using Map Objects to geocode.  It has the prefix, name, type, left/right from/ to fields, etc.  The only big difference is that the actual shape is now stored in a SQL Server geometry field; everything that is in shapefiles in our current application will be in SQL Server Express in the new version using the geometry data type.  (Our application has a map and the roads in this table are displayed on it, and the data is used for address lookup functions as well.)   I think it would be great to have whatever data your geocoder requires to be right in SQL Server under SQL login authority, and with the performance that can be obtained from a high-performance DB such as this, I would imagine with the proper use of indexing, etc., it should perform acceptably.  I look forward to hear what you think about all of this.  If the desire is there to pursue this, I’m here to help. 
  
 Allen

#4

Allen, 
  
   We are still looking into this.  I should know more early next week.  Sorry for the delays but things are busy here. 
  
 David

#5

David, 
  
 No problem…I’ve been keeping busy with some production coding, a bunch of research, and some experimenting on what I found after doing that research.  I look forward to hearing from you sometime next week.  I don’t know if anyone else is interested in this and would like to follow it, but if you ever feel it’s time to move this off the forum, just let me know.   
  
 Allen

#6

Allen, 
  
   Sounds good. 
  
 David

#7

Anything new on this, David?  We’re actually discussing a need for this sometime in the not-so-distant future.  It would be a shame to have to look somewhere else. 
  
 Allen

#8

Allen, 
  
 We are working on this. I think we can give you something tomorrow and please have a try then. 
  
 Thanks, 
  
 Ben

#9

OK, Ben…I’ll try to keep a closer watch here on the forum to see what’s happening. 
  
 Allen

#10

Allen,


We have integrated StreetSQLServerMatchingPlugin to the latest Geocoder (4.0.126), the autobuild process is running and it might be available to download in a couple hours, please have a try then with the attached test sample. From the sample code you can find it very straightforward, you need to input 3 parts of data, 1st, the connection string and table name for initialization; 2nd the column names for searching within, (streetColumnName, LeftFromAddressColumnName, etc) and 3rd, the addresses for searching. Let us know if you have any issues about it.

snapshot.png498×633 39.2 KB

Thanks,
Ben

SQLQueryMatchingPluginSample.zip (15.4 KB)

#11

That’s great news, Ben…I’m wrapping things up for the day but I’ll be sure to check first thing tomorrow (Wednesday) morning.   
  
 Allen

#12

No problem. Just let us know what you find out. 
  
 Ben

#13

Ben, 
  
 Sorry…I’ve been swamped with things to do for about a week and didn’t get to this yet, but it is slowly rising closer to the top of my list.  We’re all anxious here to give it a try. 
  
 Allen

#14

Allen, that’s fine. In fact, we did some updates even after that. Now please try the attached new sample which works with 4.0.135 or later. It is easier to use.


.

001_snapshot.png498×633 33.3 KB


Ben



SQLQueryMatchingPluginSampleUpdated.zip (14.6 KB)

#15

Ben, 



I just wanted to let you (and anyone else watching) that I finally got around to downloading this stuff. Initially it didn't work, but then I realized the version of the actual geocoder release was BEFORE we started talking about adding SQL Server. So I snagged the latest nightly build's DLL and it's working. I'm modifying the sample a little to make it easier for someone to do testing, but I pointed it to a table of ours and it's working. We generally geocode by municipality code rather than zip code, so I had to change a few of the column names from "ZIP" to our more generic "ZONE" but other than that and changing the connection string (it appears Windows authority doesn't work, but we have a SQL login in the database already anyway) but beyond those little tweaks, the demo was working AOK. 



Here's a question for you. For our immediate need, we know ahead of time what muni (or munis) we are looking for. Is there a way to tell it only to look for certain codes? I think MapObjects used to allow defining a specific code rather than pointing to the columns in the data. Right now I modified the example to filter out unwanted matches, but it would probably be more efficient not to even look at those records right from the start. (In reality we could probably implement a view or something like that.) 



Hopefully I can get someone from the office who is more familiar with the address input formatting to do some in-depth testing. I'll continue to report back. 



Thanks! 

Allen



#16

Allen, 
  
 That’s great finally the sample works on your machine.  
  
 Just want to make things clear. So there are some ColumnNames in streetSqlMatchingPlugin, only StreetColumnName is required and the others are optional, which means just setting them to null or string.Empty, those columns will then be skipped internally while searching and it will not bring any performance penalty in this part.  
  
 The searching result includes all the columns of the hit record, is that where you were concerning that we only need to return the columns we want instead of all the columns in the database? I think that makes sense and I will talk with our Tech Director about this and see if/when we can implement this feature.  Thanks for the suggestion. As you said, for the current version you can use a view or something like that.  
  
 Thanks, 
  
 Ben 


#17

Hi Ben, 
  
 I’ve done a little more testing and for certain addresses I’m not getting the correct result or I’m doing something wrong in the plugin setup.  Our address here is 313 W Liberty St.  There are two results for Liberty St., but not the right ones: the results are in a different part of the county.  The address 313  matches the range, but the results are for Liberty St., not W Liberty St.  It appears to be ignoring the “W” part.  We have the direction in STREET_DIR, but I don’t see a place to assign this to the plugin.  I tried the prefix column and it didn’t seem to change anything.  When I use my home address, which doesn’t have a direction, it’s the first match that comes up.  This is important of course, since there is data with address 313 for both E. Liberty and W. Liberty street.  Any thoughts as to what could be causing this? 
  
 Also, I noticed when I put in my home address, 401 Eden Rd, it works, but when I put in 401 Eden Road, it finds no matches.   
  
 To answer your question…no, that isn’t what I was asking about.  The data I’m using at the moment (and this is typical for our application) is for all of Lancaster County, PA, because this is what the application map shows.  But when we try to geocode an address, we know it’s for a certain municipality–in the application the user selects the municipality of interest–say the city of Lancaster, as opposed to something like East Earl Township which is on the east side of the county.  It think it would be nice to tell the geocoder up front which municipalities to check to avoid unneeded matches outside the municipality the user is working with. 
  
 I hope to get this “W Liberty” issue resolved and let the boss test it.  He’s the one that makes the decisions and pays for stuff like this. 
  
 Thanks, 
 Allen

#18

 Allen,



I really cannot reproduce your problem on my local machine correctly, I used the TigerData 2009 as the data source in the SQL Server 2005, currently, we have added the ability for matching the PrefixDirection and SuffixDirection fields,  before you start to match the street address, you should set the PrefixDirection column name or SuffixDirection column name, we have exposed the API to set them, please see the code below:


 



geocoder = new Geocoder();
            StreetSQLServerMatchingPlugin streetSqlMatchingPlugin = new StreetSQLServerMatchingPlugin(txtSQLConStr.Text);
            streetSqlMatchingPlugin.TableName = txtTableName.Text;
            streetSqlMatchingPlugin.StreetColumnName = "fullname";
            streetSqlMatchingPlugin.LeftFromAddressColumnName = "lfromadd";
            streetSqlMatchingPlugin.RightFromAddressColumnName = "rfromadd";
            streetSqlMatchingPlugin.LeftToAddressColumnName = "ltoadd";
            streetSqlMatchingPlugin.RightToAddressColumnName = "rtoadd";
            streetSqlMatchingPlugin.PrefixColumnName = "PrefixDirection";
            streetSqlMatchingPlugin.SuffixColumnName = "SuffixDirection";
            streetSqlMatchingPlugin.StreetTypeColumnName = "StreetType";
            streetSqlMatchingPlugin.LeftZipColumnName = "zip";
            streetSqlMatchingPlugin.RightZipColumnName = "zip";
            geocoder.MatchingPlugIns.Add(streetSqlMatchingPlugin);
  Please use the code above to try again and if there are still have any problems can you give us a part test data of your database so that we can deal with it better,  


Thanks,  


Scott,



#19

Hi Scott… 
  
 Sure, I’ll give it another try.  If it still doesn’t work, I can extract a small number of representative records from the table into a script.  What’s the best way to get that data to you?  I guess if it’s just a few lines of SQL script I can just paste it in the body of a message here.   
  
 Allen

#20

Allen, 
  
 You are right, if you still have any troubles you just need to give us the script for your database table, but when you test it, please make sure the enter column names are correct, according to my test, it worked fine. 
  
 If you have any questions please let us know, 
  
 Thanks, 
  
 Scott,