sql地址寻路算法(省市区路)

首页 > 代码库 > sql地址寻路算法(省市区路)

sql地址寻路算法(省市区路)

2024-08-03 19:06:10 221人阅读

最近无意翻开4年前做过的一个功能，就是搜集全国各城市各个区（县）的路（XX路、XX道、XX街、XX镇、XX乡、XX屯、XX村、XX社）。众所周知，我们都可以在网上找到省、市、区（县）这三级联动的数据，可是就并没有关于某个城市的某些区（县）下所对应的路（以下所有的路，道，街，镇，乡，屯，村，社统称为路）的数据，不过我们可以找到一些有地址的网站，例如大众点评网，里面就有很多一些饮食店等的具体地址。可以写个爬虫程序，把所有的详细地址先写进数据库的某个表中，然后再执行算法，把地址中有包含路、道、街、镇、乡、屯、村、社等属于第四级的数据写进对应的数据表（当然必须把第四级的数据对应到第三级区（县））。

1、首先需要建五张表（一个用来存放地址的表Address，一个用来存放全国的所有的省Province，一个用来存放属于省的城市City，一个用来存放属于城市的区(县)District，一个用来存放属于区的路Road）。下面是数据表关系图：

所有的表中的FID是外键表的ID，其中省Province的FID暂时没用到可忽略，其中flag是一个标志，如0表示新加，1表示修改，2表示删除等，shid和shengId是对应到其它表的，如某个城市的邮政编码，主要是用来搜集它们的一些对应的数据而已，这里可不考虑。下面是数据库查询的数据展示图：

2.其次是写一个根据输入的地址，返回省市区路四级联动的数据，然后把第四级路写进Road这个表并且把它的外键FID对应到第三级District区的主键ID

这里使用存储过程来处理，好了，那么问题就来了，执行的地址有好几种情况，在插入路这个数据之前需要对地址进行查找，有五种查找方式：

需要注意城市是否为直辖市（目前直辖市有四个：重庆市,北京市,天津市,上海市）

1）查找方式——省市区路，这个是最理想的，因为地址中已有前面三级的数据，首先找到省，然后找市，再找区，最后找路，如果路不存在就把路插入到Road表，返回省市区路

2）查找方式——省区路，这里只有省和区，首先找出省，然后找区，再根据区找到其所对应的城市，最后找路，如果路不存在就把路插入到Road表，返回省市区路

3）查找方式——省市路，这里只有省和市，于上面两种方式来说是比较复杂，首先找出省，然后找市，再根据路来找出对应的区，如果路不存在就把路插入到Road表，返回省市路

如果存在，则可找出区，返回省市区路。不存在时只能手工更正。

4）查找方式——市区路，这里只有市和区，首先找出市，然后根据市再找省，最后找路，如果路不存在就把路插入到Road表，返回省市区路

5）查找方式——市路，这里只有市，于上面四种方式是最复杂的，首先找出市，如果不是直辖市就找省，然后根据路来找区，如果路不存在就把路插入到Road表，返回省市路

如果存在，则可找出区，返回省市区路。不存在时只能手工更正。

以上是返回省市区路的五种查找方式，下面是存储过程：

GetAddress是查找方式的存储过程，返回省市区路，代码：

 1 ALTER pROCEDURE [dbo].[GetAddress] 2 @address varchar(100), 3  @NProvinceName varchar(30) OUTPUT, 4  @NCityName     varchar(30) OUTPUT, 5  @NDistrictName varchar(30) OUTPUT, 6  @NRoadName     varchar(50) OUTPUT, 7  @remain        varchar(50) OUTPUT, 8  @PostCode      varchar(7) OUTPUT, 9  @Road          varchar(100) output,    --新增，要插入road的路名 10  @method        int        output,11  @number        int         output,12  @insert        bit   =1           --新增，是否插入13 AS14 BEGIN15 16 set @address=replace(@address,‘ ‘,‘‘);    --空格17 set @address=replace(@address,‘    ‘,‘‘);    --制表符18 set @address=replace(@address,‘０‘,‘0‘);    --数字19 set @address=replace(@address,‘１‘,‘1‘);    --数字20 set @address=replace(@address,‘２‘,‘2‘);    --数字21 set @address=replace(@address,‘３‘,‘3‘);    --数字22 set @address=replace(@address,‘４‘,‘4‘);    --数字23 set @address=replace(@address,‘５‘,‘5‘);    --数字24 set @address=replace(@address,‘６‘,‘6‘);    --数字25 set @address=replace(@address,‘７‘,‘7‘);    --数字26 set @address=replace(@address,‘８‘,‘8‘);    --数字27 set @address=replace(@address,‘９‘,‘9‘);    --数字28 29   set @method=130   set @number=031   32   create table #temp(NProvinceName varchar(30),NCityName varchar(30),NDistrictName varchar(30),33                      NRoadName varchar(50),remain  varchar(50),PostCode varchar(7) ,Road varchar(100),method int,number int)34 35   while @method<=536   begin37     exec ResAddress @address,@NProvinceName output,@NCityName output,38                   @NDistrictName output,@NRoadName output,@remain output,@PostCode output,@Road output,@method output,@number output39 40     insert into #temp values (@NProvinceName,@NCityName,@NDistrictName,@NRoadName,@remain,@PostCode,@Road,@method,@number)41 42     select @NProvinceName=null,@NCityName=null,@NDistrictName=null,@NRoadName=null,@remain=null,@PostCode=null,@number=0,@Road=null43     set @method=@method+144   end 45 46 /*47  select top 1 @NProvinceName=NProvinceName,@NCityName=NCityName,@NDistrictName=NDistrictName,48           @NRoadName=NRoadName,@remain=remain,@PostCode=PostCode ,@method=method,@number=number,@Road=Road from #temp order by number desc49 */50 51  select top 1 @NProvinceName=dbo.F_Convert(NProvinceName,0),@NCityName=dbo.F_Convert(NCityName,0),@NDistrictName=dbo.F_Convert(NDistrictName,0),52               @NRoadName=dbo.F_Convert(NRoadName,0),@remain=dbo.F_Convert(remain,0),@PostCode=dbo.F_Convert(PostCode,0) ,53               @method=dbo.F_Convert(method,0),@number=dbo.F_Convert(number,0),@Road=dbo.F_Convert(Road,0) from #temp order by number desc54  55  if (@Road is not null) and (@insert=1)56     begin57       if not exists (select * from road where fid=left(@road,36) and title=RIGHT(@road,LEN(@ROAD)-36) and text=RIGHT(@road,LEN(@ROAD)-36))58       insert into Road(fid,title,flag,text) values(left(@road,36),RIGHT(@road,LEN(@ROAD)-36),2,RIGHT(@road,LEN(@ROAD)-36))59     end60   drop table #temp61 END

View Code

ResAddress是查找方式中需要调用的存储过程，其中有number这个返回一个数字，数字越大表示查找的难度就越大，代码：

  1 ALTER pROCEDURE [dbo].[ResAddress]  2   3 @address varchar(100),  4  @NProvinceName varchar(30) OUTPUT,  5  @NCityName     varchar(30) OUTPUT,  6  @NDistrictName varchar(30) OUTPUT,  7  @NRoadName     varchar(50) OUTPUT,  8  @remain        varchar(50) OUTPUT,  9  @PostCode      varchar(7) OUTPUT, 10  @Road          varchar(100) output,    --新增，要插入road的路名  11  @method        int        output, 12  @number        int        output 13  14 AS 15 BEGIN 16  17 DECLARE @id            varchar(36) 18 declare @fid           varchar(36) 19 declare @text          varchar(30) 20  21  22 declare @NAddress      varchar(200) 23 set @NAddress=@address 24  25  26 if @method=1    --查找方式 省市区路 27 begin 28 select top 1 @NProvinceName=title ,@id=id,@text=text from province where left(@Address,len(text)) like ‘%‘+text+‘%‘  order by len(text) desc   29   if (@NProvinceName is not null)  30   begin 31       set @number=@number+1                   --找到省计数加1 32       select @id=id from province where title=@NProvinceName and shengid is not null 33       set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘) 34   end  35  36  37 select top 1 @NCityName=title,@id=id,@fid=fid ,@text=text from city where left(@Address,len(text)) like ‘%‘+text+‘%‘  and city.fid=@id order by len(text) desc 38     if (@NCityName is not null)  39     begin 40        set @number=@number+1 41        select @id=id from city where title=@NCityName and shiid is not null 42        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘) 43     end 44  45 select top 1 @NDistrictName=title,@id=id,@fid=fid,@text=text from district where left(@Address,len(text)) like ‘%‘+text+‘%‘ and district.fid=@id order by len(text) desc 46     if (@NDistrictName is not null) 47     begin 48        set @number=@number+1 49         select @id=id from district where title=@NDistrictName and shiid is not null 50        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘) 51     end 52  53 select top 1 @NRoadName=title,@fid=fid,@id=id ,@PostCode=PostCode,@text=text from Road where left(@Address,len(text)) like ‘%‘+text+‘%‘ and road.fid=@id order by len(text) desc 54     if (@NRoadName is not null)  55     begin 56        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘) 57        set @number=@number+1 58     end 59  60 if @text is not null 61 set @remain=right(@NAddress,len(@NAddress)-charindex(@text,@NAddress)-len(@text)+1) 62 else  63 set @remain=@Address 64  65 if (@NRoadName is null) and (@NDistrictName is not null)  66     goto GetRoad  67 else  68     goto finished   69 end 70  71 if @method=2                    --查找方式 省区路 72 begin 73   select top 1 @NProvinceName=title ,@id=id,@text=text from province where left(@Address,len(text)) like ‘%‘+text+‘%‘ order by len(text) desc   74   if (@NProvinceName is not null)  75   begin 76       set @number=@number+1                   --找到省计数加1 77       select @id=id from province where title=@NProvinceName and shengid is not null --找省的标准ID 78       set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘)  79   end  80  81  82   select top 1 @NDistrictName=title,@id=id,@fid=fid,@text=text from district where left(@Address,len(text)) like ‘%‘+text+‘%‘  83   and  district.fid in (select id from city where city.fid=@id) order by len(text) desc 84     if (@NDistrictName is not null)  85     begin 86        select @id=id from district where title=@NDistrictName and shiid is not null   --找区的标准ID 87        set @number=@number+1 88        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘) 89     end 90     91   SELECT @NCityName=title FROM City where id=@fid     --返回查找市名 92   if  (@NCityName is not null)  93   set @number=@number+1 94  95    96   select top 1 @NRoadName=title,@fid=fid,@id=id ,@PostCode=PostCode,@text=text from Road where left(@Address,len(text)) like ‘%‘+text+‘%‘  97   and Road.fid=@id order by len(text) desc 98     if (@NRoadName is not null) 99     begin100        set @number=@number+1101        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘) 102     end103 104 if @text is not null105 set @remain=right(@NAddress,len(@NAddress)-charindex(@text,@NAddress)-len(@text)+1)106 else 107 set @remain=@Address108 109 if (@NRoadName is null) and (@NDistrictName is not null) 110     goto GetRoad 111 else 112     goto finished  113 end114 115 if @method=3 --查找方式 省市路116 begin117   select top 1 @NProvinceName=title ,@id=id,@text=text from province where left(@Address,len(text)) like ‘%‘+text+‘%‘ order by len(text) desc  118   if (@NProvinceName is not null) 119   begin120       set @number=@number+1                   --找到省计数加1121       select @id=id from province where title=@NProvinceName and shengid is not null --找省的标准ID122       set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘) 123   end 124 125  if @NProvinceName in (‘重庆市‘,‘北京市‘,‘天津市‘,‘上海市‘)    --新增这个判断 2009-08-25126  begin127    select top 1 @NCityName=title,@id=id,@fid=fid,@text=text from city where city.fid=@id128    set @number=@number+1129  end130  else 131  begin132  select top 1 @NCityName=title,@id=id,@fid=fid,@text=text from city where left(@Address,len(text)) like ‘%‘+text+‘%‘ 133  and city.fid=@id order by len(text) desc134     if (@NCityName is not null) 135     begin136        set @number=@number+1137        select @id=id from city where title=@NCityName and shiid is not null      --找市的标准ID138        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘)139     end140  end141 142   select top 1 @NRoadName=title,@fid=fid,@id=id ,@PostCode=PostCode,@text=text from Road where left(@Address,len(text)) like ‘%‘+text+‘%‘ 143   and road.fid in (select id from district where district.fid=@id) order by len(text) desc144     if (@NRoadName is not null) 145     begin146        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘)147        set @number=@number+1148     end149 150  select @NDistrictName=title from district where id=@fid    --返回查找区名151  if (@NDistrictName is not null) 152     set @number=@number+1153 154 if @text is not null155 set @remain=right(@NAddress,len(@NAddress)-charindex(@text,@NAddress)-len(@text)+1)156 else 157 set @remain=@Address158 if (@NRoadName is null) and (@NDistrictName is not null) 159     goto GetRoad 160 else 161     goto finished  162 end163 164 if @method=4    --查找方式 市区路165 begin166  select top 1 @NCityName=title,@id=id,@fid=fid ,@text=text from city where left(@Address,len(text)) like ‘%‘+text+‘%‘ order by len(text) desc167     if (@NCityName is not null) 168     begin169        select @id=id from city where title=@NCityName and shiid is not null      --找市的标准ID170        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘)171        set @number=@number+1172     end173 174 select @NProvinceName=title from Province where id=@fid             --返回找省名175 if (@NProvinceName is not null) 176 begin177    set @number=@number+1178 end179 180 select top 1 @NDistrictName=title,@id=id,@fid=fid, @text=text from district where left(@Address,len(text)) like ‘%‘+text+‘%‘ 181 and  district.fid=@id order by len(text) desc182     if (@NDistrictName is not null) 183     begin184        set @number=@number+1185        select @id=id from district where title=@NDistrictName and shiid is not null   --找区的标准ID186        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘)187     end188 189 190 select top 1 @NRoadName=title,@fid=fid,@id=id ,@PostCode=PostCode,@text=text from Road where left(@Address,len(text)) like ‘%‘+text+‘%‘ 191 and road.fid=@id order by len(text) desc192     if (@NRoadName is not null) 193     begin194        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘)195        set @number=@number+1196     end197 198 if @text is not null199 set @remain=right(@NAddress,len(@NAddress)-charindex(@text,@NAddress)-len(@text)+1)200 else 201 set @remain=@Address202 203 if (@NRoadName is null) and (@NDistrictName is not null) 204     goto GetRoad 205 else 206     goto finished  207 end 208 209 if @method=5  --查找方式 ：市-路210 begin211    select top 1 @NCityName=title,@id=id,@fid=fid , @text=text from city where left(@Address,len(text)) like ‘%‘+text+‘%‘ order by len(text) desc212     if (@NCityName is not null)213     begin214        set @number=@number+1215        select @id=id from city where title=@NCityName and shiid is not null      --找市的标准ID216        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘)217     end218    select @NProvinceName=title from Province where id=@fid             --返回找省名219    if (@NProvinceName is not null)220        set @number=@number+1221 222   select top 1 @NRoadName=title,@fid=fid,@id=id ,@PostCode=PostCode ,@text=text from Road where left(@Address,len(text)) like ‘%‘+text+‘%‘ 223   and road.fid in (select id from district where district.fid=@id) order by len(text) desc224     if (@NRoadName is not null)225     begin226        set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),‘‘)227        set @number=@number+1228     end229 230   select @NDistrictName=title from district where id=@fid    --返回查找区名231   if (@NDistrictName is not null)232       set @number=@number+1233 234 if @text is not null235 set @remain=right(@NAddress,len(@NAddress)-charindex(@text,@NAddress)-len(@text)+1)236 else 237 set @remain=@Address238 239 if (@NRoadName is null) and (@NDistrictName is not null) 240     goto GetRoad 241 else 242     goto finished  243 end 244 245 246 GetRoad:                     --新增将返回@road             247    if charindex(‘路‘,@Address)>0248      set @Road=left(@address,charindex(‘路‘,@Address))249    else if  charindex(‘道‘,@Address)>0250      set @Road=left(@address,charindex(‘道‘,@Address))251    else if  charindex(‘街‘,@Address)>0252      set @Road=left(@address,charindex(‘街‘,@Address)) 253    else if  charindex(‘镇‘,@Address)>0254      set @Road=left(@address,charindex(‘镇‘,@Address))255    else if  charindex(‘乡‘,@Address)>0256      set @Road=left(@address,charindex(‘乡‘,@Address))257    else if  charindex(‘屯‘,@Address)>0258      set @Road=left(@address,charindex(‘屯‘,@Address))259    else if  charindex(‘村‘,@Address)>0260      set @Road=left(@address,charindex(‘村‘,@Address))261    else if  charindex(‘社‘,@Address)>0262      set @Road=left(@address,charindex(‘社‘,@Address))263 264  set @road=@id+@road265 finished:266 END

View Code

execAddress是执行存储过程，根据输入的地址返回详细的地址，代码：

 1 ALTER Procedure [dbo].[execAddress] 2 @inputaddress varchar(100) 3 AS  4 DECLARE    @return_value int, 5         @NProvinceName varchar(30), 6         @NCityName varchar(30), 7         @NDistrictName varchar(30), 8         @NRoadName varchar(50), 9         @remain varchar(50),10         @PostCode varchar(7),11         @Road varchar(100),12         @method int,13         @number int14 15 EXEC    @return_value = [dbo].[GetAddress]16         @address = @inputaddress,17         @NProvinceName = @NProvinceName OUTPUT,18         @NCityName = @NCityName OUTPUT,19         @NDistrictName = @NDistrictName OUTPUT,20         @NRoadName = @NRoadName OUTPUT,21         @remain = @remain OUTPUT,22         @PostCode = @PostCode OUTPUT,23         @Road = @Road OUTPUT,24         @method = @method OUTPUT,25         @number = @number OUTPUT