LBS位置服务的SQL实现原理以及SQL代码

一、位置服务(搜索周边商家POI)

  1. 自身有海量的周边搜索数据,并且一定要有经纬度坐标;(数据来源可以从网上购买,如果要直接从地图API中获取,那是要付费的,而且费用不菲)
  2. 手机定位后将经纬度发送给服务器,服务器根据经纬度在数据库中匹配数据,并按照距离由近到远排序(此处省略一万字...)

SQL实现经纬度匹配以及距离排序代码

1、SQL计算经纬度距离的自定义函数

CREATE FUNCTION dbo.GetDistance  
(  
    @LatBegin REAL  
    , @LngBegin REAL  
    , @LatEnd REAL  
    , @LngEnd REAL  
)  
RETURNS FLOAT  
AS  
BEGIN  
    DECLARE @Distance REAL  
    DECLARE @EARTH_RADIUS REAL  
    SET @EARTH_RADIUS = 6378.137  
      
    DECLARE @RadLatBegin REAL, @RadLatEnd REAL, @RadLatDiff REAL, @RadLngDiff REAL  
    SET @RadLatBegin = @LatBegin * PI() / 180.0  
    SET @RadLatEnd = @LatEnd * PI() / 180.0  
    SET @RadLatDiff = @RadLatBegin - @RadLatEnd  
    SET @RadLngDiff = @LngBegin * PI() / 180.0 - @LngEnd * PI() / 180.0  
      
    SET @Distance = 2 * ASIN(SQRT(POWER(Sin(@RadLatDiff / 2), 2) + COS(@RadLatBegin) * COS(@RadLatEnd) * POWER(SIN(@RadLngDiff/2),2)))  
    SET @Distance = @Distance * @EARTH_RADIUS  
    --SET @Distance = Round(@Distance * 10000) / 10000  
      
    RETURN @Distance * 1000  
END  

2、获取周边数据的存储过程

CREATE PROCEDURE Up_Data_GetPOI 
	@lngbegin nvarchar(50), --当前经度
	@latbegin nvarchar(50), --当前纬度
	@distance int, --搜索半径,单位为米
	@WhereStr nvarchar(200) --附加搜索条件,如and 1=2 
	AS
	declare @SqlStr nvarchar(2000)
	
	set @SqlStr='select *, dbo.GetDistance('+@latbegin+','+@lngbegin+',纬度字段,经度字段) as distance from [存储商家的数据表名] where (纬度字段<>'' and 经度字段<>'') and dbo.GetDistance('+@latbegin+','+@lngbegin+',纬度字段,经度字段)<'+Convert(nvarchar(10),@distance)+' '+@WhereStr+' order by distance asc'
	
	--print @SqlStr
	Execute Sp_ExecuteSql @SqlStr
GO

二、位置交友

  1. 要有会员数据,同样也得有客户端是否在线以及经纬度的字段(不一定要获取到经纬度,默认为空)
  2. 手机客户端定位后,更新该会员的经纬度坐标以及客户端在线状态
  3. 通过上面的SQL代码即可实现数据列表,只要输出JSON/XML给手机客户端解析即可

SQL LocalDB出现错误无法获取本地应用程序数据路径的解决办法

SQL LocalDB在IIS部署时出现错误:

无法获取本地应用程序数据路径。很可能是因为未加载用户配置文件。如果在 IIS 下执行 LocalDB,请确保为当前用户启用配置文件加载。

QQ截图20150303143331

解决办法:

在IIS管理器中找到你所部署网站对应的应用程序池,选中,点击右边菜单的高级设置

找到“加载用户配置文件”,把它设置为True,重启网站和IIS即可

QQ截图20150303143901

SQL2005/SQL2008中使用SQL语句导入Excel的方法

注意此方法只能在SQL05以上版本才能使用,2000的请无视

格式如下:

insert into tableA
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=D:\算神.xls',[sheet1$])

如果遇到下面的错误:

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

 

请执行以下脚本:

exec sp_configure 'show advanced options',1 
reconfigure 
exec sp_configure 'Ad Hoc Distributed Queries',1 
reconfigure

PS:可以用VB/C#等编程语言拼凑此方法的SQL语句,然后使用SqlCommand执行,即可实现Excel的导入。不必像原来那样,先把Excel的值取出来,再循环插入SQL

使用SQL语句更改数据库的兼容级别

某些情况下,我们需要调整数据库的兼容级别

如果企业管理器不支持修改,则需要使用以下命令:

sp_dbcmptlevel database, 90

其中database为数据库名,90则为要设定为的兼容级别的版本代号,其他版本的版本号请参照下表

SQLServer版本号 兼容基本代号
SQLServer 2000 80
SQLServer 2005 90
SQLServer 2008 100
SQLServer 2012 110

微软发布SQL Server 2012

微软于3月7日发布最新的SQL Server 2012版,将推出三个主要版本和很多新特征,同时微软也透露了SQL Server 2012的价格和版本计划,其中增加一个新的智能商业包。 同时微软服务和工具业务总裁Satya Nadella也在去年秋季透露,代号为“Denali”的SQL Server 2012将在今年年初发布。

SQL Server 2012主要版本包括新的商务智能版本,增加Power View数据查找工具和数据质量服务,企业版本则提高安全性可用性,以及从大数据到StreamInsight复杂事件处理,再到新的可视化数据和分析工具等,都将成为SQL Server 2012最终版本的一部分。

回顾SQL Server 2012 RC0版的更新特性:

通过 AlwaysOn 提供所需运行时间和数据保护

通过列存储索引获得突破性和可预测的性能

通过用于组的新用户定义角色和默认架构,帮助实现安全性和遵从性

通过列存储索引实现快速数据恢复,以便更深入地了解组织

通过 SSIS 改进、用于 Excel 的 Master Data Services 外接程序和新 Data Quality Services,确保更加可靠、一致的数据

通过使用 SQL Azure 和 SQL Server 数据工具的数据层应用程序组件 (DAC) 奇偶校验,优化服务器和云间的 IT 和开发人员工作效率,从而在数据库、BI 和云功能间实现统一的开发体验

Google Cloud SQL 主要特性和功能限制

对于用惯了关系型数据库的程序员来说,转而去习惯Google App Engine的Datastore简直是自找麻烦,所以很多想尝试GAE的开发者都望而却步,心想:与其花时间去了解这个家伙,还不如把时间花在Dota上呢!显然,Google也认识到了这一点,于是他们在这个月6号发布了Google Cloud SQL。

这是一个接近传统关系型数据库的云端服务,并能方便的与Google App Engine整合,而且Google Cloud SQL和Datastore一样,无需开发者维护,Google会帮你搞定这些琐事。

有了Google Cloud SQL,程序员们可以无限制地使用事务,对索引和字段有更多的控制,早已掌握的关系数据库理论知识和经验也能大展身手了。

需要说明的是Google Cloud SQL目前还处于有限制的测试阶段,并且Google保证在2011年是完全免费的,如果有天它变成收费服务,Google会提前30天通知你的。目前Google Cloud SQL还只能通过Google App Engine和一些工具来访问,但将来可能提供对外的REST接口。

Google Cloud SQL的主要特性:

MySQL数据库运行在云端。

不需要维护和管理,Google帮你管理和维护数据库。

高可信性和可用性:用户的数据在多个数据中心保持同步,机器故障和数据中心出错等都会自动调整,最小化用户影响。

兼容Java和Python,支持JDBC(基于Java的App Engine应用)和DB-API(基于Python的App Engine应用),类似于MySQL环境。

每个实例最多10GB容量,与Google App Engine datastore的性能相当。

支持用MysqlDump导入和导出数据库。(备份数据存放在Google Cloud Storage)

全面的用户界面管理数据库。支持命令行工具(需要JDK,版本最好是1.6)和SQuirrel SQL Client这个第三方的图形客户端,并可以在Google APIs Console运行SQL命令。

简单而强大整合Google App Engine。

当然目前Google Cloud SQL的使用还有一些限制(不排除将来解除某些限制的可能性)

限制最多3个Mysql实例。(每个实例最多10GB大小。)
目前不与Django兼容。
每秒查询率不能超过16MB。(针对外部请求,App Engine无限制。)
针对外部请求,每秒最多5次查询,App Engine的程序没有限制。
支持MySQL 5.1.59的所有特性,但不包括文件和插件等命令。
不能导出指定的数据库,只能导出整个实例。

一般情况下 Google Cloud SQL 支持MySQL的所有特性,当然要除了以下特性,毕竟Google Cloud SQL 不是 MySQL。 不支持的Mysql语句:

LOAD DATA INFILE
SELECT … INTO OUTFILE
SELECT … INTO DUMPFILE
INSTALL PLUGIN .. SONAME …
UNINSTALL PLUGIN
CREATE FUNCTION … SONAME …

不支持的SQL函数:

LOAD_FILE()

另外,Google Cloud SQL不支持MySql的自定义函数同步功能(MySql replication)。

如果对Google Cloud SQL感兴趣,而且不在乎它的特性和功能限制的话,就可以去申请试用。目前Google Cloud SQL出于限制性的对外开放测试阶段,如果想要试用,可以按照以下步骤申请:

1.登录Google APIs Console(https://code.google.com/apis/console/)
2.点击页面左侧的Service.
3.下拉找到SQL Service,点击Request access…链接.
4.填写注册表单,并等待Google的审核。

SQL Server Code Name &ldquo;Denali&rdquo; Express CTP3 现已推出

denali

SQL Server Code Name “Denali” Express Community Technology Preview 3 (CTP3) 现在可免费下载。

» 下载 SQL Server Code Name “Denali” Express CTP3

SQL Server Code Name “Denali” Express CTP3 在安装时会显示一个新选项 – SQL Server Express LocalDB。LocalDB 是一个新的 Express 轻量级版本,其中包括 Express 的所有可编程性功能,还是在用户模式下运行,安装速度快且无需任何配置,必要的前提条件较少。要进行试用,只需选中安装 Express 时提示的复选框即可。

» 了解有关 SQL Server Express LocalDB 的更多信息

SQL重复记录查询

1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select * from people
where peopleId in (select   peopleId  from   people  group  by   peopleId  having  count(peopleId) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people 
where peopleId  in (select   peopleId  from people  group  by   peopleId   having  count(peopleId) > 1)
and rowid not in (select min(rowid) from   people  group by peopleId  having count(peopleId )>1)

3、查找表中多余的重复记录(多个字段) 
select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq  having count(*) > 1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
(二)
比方说
在A表中存在一个字段“name”,
而且不同记录之间的“name”值有可能会相同,
现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;
Select Name,Count(*) From A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1
(三)
方法一
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >; 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0

  方法二

  有两个意义上的重复记录,一是完全重复的记录,也即所有字段均重复的记录,二是部分关键字段重复的记录,比如Name字段重复,而其他字段不一定重复或都重复可以忽略。

  1、对于第一种重复,比较容易解决,使用
select distinct * from tableName

  就可以得到无重复记录的结果集。

  如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp

  发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。

  2、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下

  假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)

  最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列)

(四)查询重复
select * from tablename where id in (
select id from tablename 
group by id 
having count(id) > 1

SQL批量替换语句

SQL批量替换语句:
update [ufile] set [url]=replace(cast([url] as varchar(8000)) ,'算神','黎摄文')

通过SQL Server 2008数据库复制实现数据库同步备份

SQL Server 2008数据库复制是通过发布/订阅的机制进行多台服务器之间的数据同步,我们把它用于数据库的同步备份。这里的同步备份指的是备份服务器与主服务器进行实时数据同步,正常情况下只使用主数据库服务器,备份服务器只在主服务器出现故障时投入使用。它是一种优于文件备份的数据库备份解决方案。

在选择数据库同步备份解决方案时,我们评估了两种方式:SQL Server 2008的数据库镜像和SQL Server 2008数据库复制。数据库镜像的优点是系统能自动发现主服务器故障,并且自动切换至镜像服务器。但缺点是配置复杂,镜像数据库中的数据不可见(在SQL Server Management Studio中,只能看到镜像数据库处于镜像状态,无法进行任何数据库操作,最简单的查询也不行。想眼见为实,看看镜像数据库中的数据是否正确都不行。只有将镜像数据库切换主数据库才可见)。如果你要使用数据库镜像,强烈推荐killkill写的SQL Server 2005 镜像构建手册,我们就是按照这篇文章完成了数据库镜像部署测试。

最终,我们选择了SQL Server 2008数据库复制。

下面通过一个示例和大家一起学习一下如何部署SQL Server 2008数据库复制。

测试环境:Windows Server 2008 R2 + SQL Server 2008 R2(英文版),两台服务器,一台主数据库服务器CNBlogsDB1,一台备份数据库服务器CNBlogsDB2。

复制原理:我们采用的是基于快照的事务复制。主数据库服务器生成快照,备份库服务器读取并加载该快照,然后不停地从主数据库服务器复制事务日志。见下图:

grid.ai

图片来自SQL Server联机丛书

安装与配置步骤:

一、在两台服务器上安装好SQL Server 2008 R2,主要安装的组件:Database Engine(含SQL Server Replication),Management Tools。

二、主数据库服务器(发布服务器)的配置:

1. 在主数据库服务器CNBlogsDB1新建示例数据库CNBlogsDemo(注意Recovery mode要使用默认值Full,只有这个模式才能进行事务复制),然后建立一张测试表,比如:CNBlogsTest。

cnblogs_test

2. 设置存放快照的文件夹:

创建发布之前,先设置一下存放快照的文件夹,创建发布后会在该文件夹生成快照文件,订阅服务器需要在初始化时加载该快照文件。

选择Replication》Local Publications》属性,在出现的窗口中选择Publishers,如下图:

20100826-8

点击红框处的按钮,出现设置窗口:

20100826-9

在Default Snapshot Folder中设置快照文件存放路径。

3. 在主数据库服务器创建发布:

在Replication》Local Publications中选择New Publication,出现一个向导。先选择要发布的数据库CNBlogsDemo,然后选择发布类型Transational publication,如下图:

Transationalpublication

点击Next,出现错误:

20100826-1

原来所有要复制的表都需要有主键,刚才建CNBlogsTest表时,没有建主键。建一下主键,并重新启动向导就可以了。

接着选择要复制的对象:

20100826-2

点Next,Next,进入Snapshot Agent窗口,选择Create a snapshot immediately and keep the snapshot available to initialize subscriptions,见下图:

20100826-3

Next,进入Agent Security:

20100826-4

选择Security Settings,进行相应的帐户设置:

20100826-5

一个是设置运行Snapshot Agent的Windows帐户,我们这里选择与SQL Server Agent同样的帐户。

一个是设置连接发布服务器的SQL帐户,我们这里就用主数据库服务器的sa帐户。

继续:OK,Next,Next,为这个发布起个名字:

 20100826-7

点击Finish,就开始正式创建发布,创建成功就会出现如下窗口:

20100826-10

这时查看快照文件夹,就会看到unc文件夹,快照文件就在这个文件夹中。

这里要考虑这样一个问题,如何让订阅服务器通过网络访问这个快照文件夹。

我们在这个问题上折腾了一些时间,本来想通过共享文件夹的方式,但又不想打开匿名共享,折腾了半天,没搞定订阅服务器访问共享文件夹用户验证的问题。于是采用了FTP的方式,所以,下面介绍一下如何让订阅服务器通过FTP访问快照文件。

4. 设置快照的FTP访问

首先在主数据库服务器上开通FTP服务,建立一个指向快照文件夹的FTP站点,设置好可以远程连接的FTP帐户。然后在这台发布服务器设置一下FTP客户端配置。配置方法如下:

在Replication》Local Publications中选择刚��创建的发布[CNBlogsDemo]:CNBlogsDemo_Publication,选择属性》FTP Snapshot,如下图:

 20100826-11

选中Allow Subscribers to download snapshot files using FTP,并设置一下FTP客户端连接参数,订阅服务器就是通过这里的设置连接FTP服务器的(注:Path from the FTP root folder的设置要和上图一样,设置为:/ftp)。

点击OK,这时会在快照文件夹中创建一个ftp文件夹,并在该文件夹中生成快照文件。

这样,发布服务器就配置好了,下面配置订阅服务器。

三、备份数据库服务器(订阅服务器)的配置:

进入订阅服务器CNBlogsDB2,创建与发布服务器同名的数据库CNBlogsDemo,使用完全恢复模式。

在Replication》Local Subscriptions中选择New Subscriptions,进入向导。

Next,进入选择发布服务器的窗口,选择Find SQL Server Publisher,出现服务器连接窗口:

20100826-12

这里要注意的是Server Name中一定要填写发布服务器的计算机名,如果计算机名连接不上,要在hosts文件中加一个IP地址解析。

成功连接发布服务器之后,就可以看到刚才在主数据库服务器上创建的发布:

20100826-13

Next,进入“分发代理工作位置”的选择窗口:

20100826-14

我们这里选择pull subscriptions,把数据给拉过来,这样主数据库服务器的负担会轻些。

Next,选择订阅服务器上的数据库,之前我们已经建好同名的数据库,所以系统自己会找到。

Next,进入分发代理安全设置窗口:

20100826-15

点击红框内的按钮,进入设置窗口:

20100826-16

设置如上图,Connect to the Distributor处设置的是发布服务器的sa帐户。

OK, Next, Next, Next:

20100826-17

Next, Finish, Success:

20100826-18

备份数据库的订阅就建好了!

现在来瞧一瞧订阅服务器CNBlogsDB2上的用于复制的数据库CNBlogsDemo:

20100826-19

看!我们在发布服务器上建立的表CNBlogsTest复制过来了。

现在我们去发布服务器CNBlogsDB1上添加一条记录:

20100826-20

再去订阅服务器CNBlogsDB2瞧一瞧:

20100826-21

数据立即同步过来了!搞定!

 20791975316932   

遇到的问题:

在测试过程中被两个问题折腾了很长时间。

1)发布服务器的Log Reader Agent不能启动,错误信息:

· The process could not execute 'sp_replcmds' on 'YCSERVER006'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
· Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)
Get help: http://help/15517
· The process could not execute 'sp_replcmds' on 'YCSERVER006'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

开始测试时,附加了一个现有数据库进行复制遇到了这个问题,附加的是一下SQL Server 2005数据库文件,Owner为空,改为sa问题就解决了,如下图:

2)第二个问题就是前面已经描述过的订阅服务器访问发布服务器上的快照文件夹的问题,后来通过FTP的方式解决的。

对于SQL Server 2008数据库复制,目前我就学习了这些,期待园子里有这方面经验的朋友也来分享一下,在分享过程中你也会学到很多。