sql地址寻路算法(省市区路)
最近无意翻开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是查找方式的存储过程,返回省市区路,代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 AS 14 BEGIN 15 16 set @address=replace(@address,' ',''); --空格 17 set @address=replace(@address,' ',''); --制表符 18 set @address=replace(@address,'0','0'); --数字 19 set @address=replace(@address,'1','1'); --数字 20 set @address=replace(@address,'2','2'); --数字 21 set @address=replace(@address,'3','3'); --数字 22 set @address=replace(@address,'4','4'); --数字 23 set @address=replace(@address,'5','5'); --数字 24 set @address=replace(@address,'6','6'); --数字 25 set @address=replace(@address,'7','7'); --数字 26 set @address=replace(@address,'8','8'); --数字 27 set @address=replace(@address,'9','9'); --数字 28 29 set @method=1 30 set @number=0 31 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<=5 36 begin 37 exec ResAddress @address,@NProvinceName output,@NCityName output, 38 @NDistrictName output,@NRoadName output,@remain output,@PostCode output,@Road output,@method output,@number output 39 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=null 43 set @method=@method+1 44 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 desc 49 */ 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 desc 54 55 if (@Road is not null) and (@insert=1) 56 begin 57 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 end 60 drop table #temp 61 END
ResAddress是查找方式中需要调用的存储过程,其中有number这个返回一个数字,数字越大表示查找的难度就越大,代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 begin 100 set @number=@number+1 101 set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),'') 102 end 103 104 if @text is not null 105 set @remain=right(@NAddress,len(@NAddress)-charindex(@text,@NAddress)-len(@text)+1) 106 else 107 set @remain=@Address 108 109 if (@NRoadName is null) and (@NDistrictName is not null) 110 goto GetRoad 111 else 112 goto finished 113 end 114 115 if @method=3 --查找方式 省市路 116 begin 117 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 begin 120 set @number=@number+1 --找到省计数加1 121 select @id=id from province where title=@NProvinceName and shengid is not null --找省的标准ID 122 set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),'') 123 end 124 125 if @NProvinceName in ('重庆市','北京市','天津市','上海市') --新增这个判断 2009-08-25 126 begin 127 select top 1 @NCityName=title,@id=id,@fid=fid,@text=text from city where city.fid=@id 128 set @number=@number+1 129 end 130 else 131 begin 132 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) desc 134 if (@NCityName is not null) 135 begin 136 set @number=@number+1 137 select @id=id from city where title=@NCityName and shiid is not null --找市的标准ID 138 set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),'') 139 end 140 end 141 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) desc 144 if (@NRoadName is not null) 145 begin 146 set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),'') 147 set @number=@number+1 148 end 149 150 select @NDistrictName=title from district where id=@fid --返回查找区名 151 if (@NDistrictName is not null) 152 set @number=@number+1 153 154 if @text is not null 155 set @remain=right(@NAddress,len(@NAddress)-charindex(@text,@NAddress)-len(@text)+1) 156 else 157 set @remain=@Address 158 if (@NRoadName is null) and (@NDistrictName is not null) 159 goto GetRoad 160 else 161 goto finished 162 end 163 164 if @method=4 --查找方式 市区路 165 begin 166 select top 1 @NCityName=title,@id=id,@fid=fid ,@text=text from city where left(@Address,len(text)) like '%'+text+'%' order by len(text) desc 167 if (@NCityName is not null) 168 begin 169 select @id=id from city where title=@NCityName and shiid is not null --找市的标准ID 170 set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),'') 171 set @number=@number+1 172 end 173 174 select @NProvinceName=title from Province where id=@fid --返回找省名 175 if (@NProvinceName is not null) 176 begin 177 set @number=@number+1 178 end 179 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) desc 182 if (@NDistrictName is not null) 183 begin 184 set @number=@number+1 185 select @id=id from district where title=@NDistrictName and shiid is not null --找区的标准ID 186 set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),'') 187 end 188 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) desc 192 if (@NRoadName is not null) 193 begin 194 set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),'') 195 set @number=@number+1 196 end 197 198 if @text is not null 199 set @remain=right(@NAddress,len(@NAddress)-charindex(@text,@NAddress)-len(@text)+1) 200 else 201 set @remain=@Address 202 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 begin 211 select top 1 @NCityName=title,@id=id,@fid=fid , @text=text from city where left(@Address,len(text)) like '%'+text+'%' order by len(text) desc 212 if (@NCityName is not null) 213 begin 214 set @number=@number+1 215 select @id=id from city where title=@NCityName and shiid is not null --找市的标准ID 216 set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),'') 217 end 218 select @NProvinceName=title from Province where id=@fid --返回找省名 219 if (@NProvinceName is not null) 220 set @number=@number+1 221 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) desc 224 if (@NRoadName is not null) 225 begin 226 set @Address=stuff(@Address,charindex(@text,@Address),LEN(@text),'') 227 set @number=@number+1 228 end 229 230 select @NDistrictName=title from district where id=@fid --返回查找区名 231 if (@NDistrictName is not null) 232 set @number=@number+1 233 234 if @text is not null 235 set @remain=right(@NAddress,len(@NAddress)-charindex(@text,@NAddress)-len(@text)+1) 236 else 237 set @remain=@Address 238 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)>0 248 set @Road=left(@address,charindex('路',@Address)) 249 else if charindex('道',@Address)>0 250 set @Road=left(@address,charindex('道',@Address)) 251 else if charindex('街',@Address)>0 252 set @Road=left(@address,charindex('街',@Address)) 253 else if charindex('镇',@Address)>0 254 set @Road=left(@address,charindex('镇',@Address)) 255 else if charindex('乡',@Address)>0 256 set @Road=left(@address,charindex('乡',@Address)) 257 else if charindex('屯',@Address)>0 258 set @Road=left(@address,charindex('屯',@Address)) 259 else if charindex('村',@Address)>0 260 set @Road=left(@address,charindex('村',@Address)) 261 else if charindex('社',@Address)>0 262 set @Road=left(@address,charindex('社',@Address)) 263 264 set @road=@id+@road 265 finished: 266 END
execAddress是执行存储过程,根据输入的地址返回详细的地址,代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 int 14 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
以下是测试,根据输入的地址返回省市区路的详细地址,这里就只截图:
1.输入重庆新华路41-43号,点转换,执行存储过程execAddress,把返回的地址显示在输出地址里,如下图
这里是第五种查找方式(市路)也可能是第三种查找方式(省市路)。
2.输入广州海珠区广州大道客村墩和,返回广东省广州市海珠区广州大道客村墩和,如下图:
这里是第四种查找方式(市区路)。
以上就是介绍查找地址的方法,还没用到address表,这个表是前面所说的用来搜集有详细地址的,具体需要写一个网页爬虫的程序,去爬爬有地址的网站,如大众点评网,这里就介绍这么多了,有写得不好的还望各位手下留情~