Tuesday, April 16, 2013

[T-SQL] Storing Spatial Data

[T-SQL] Storing Spatial Data

Storing Spatial Data

Posted: 15 Apr 2013 06:57 PM PDT

Hi All,I work with geospatial specialists who provides me with polygons which i save it to my geography field. And any spatial related queries are queried to this field and i get the values as well. The polygon details are provided as a string of lat and long pairs. For eg (Lat1 Long1, Lat2 Long2,.......Lat1 Long1)as my first point and last point needs to be same for a polygon.I have read in many blogs that we need to provide a point as Long Lat and not as Lat long. That is, we recieve the points as Lat Long but we need to reverse the points as Long LatSo my ploygon value before converting to Geography data type, i reverse the points. The string will be (Long1 Lat1, Long2 Lat2,........Long1 Lat1)This works fine for me and my queries are fetching the values properly. Now we have another feature added to the web application wherein the geospatial team would draw a polygon on the google map. When a polygon is drawn, google map API gives me the points of the polygon. We can plot as many points and the user ensures that the polygon ends at the same location where it started. The API provides me the points in Lat long pair. However it gives only distinct points. So what i do is that i take the first point and add at the last to ensure that i dont get an error when i convert it into a geography data. But even then i get[b] "The specified input does not represent a valid geography instance"[/b] error.INSERT INTO SpatialTable (GeogCordinate)VALUES (geography::STGeomFromText('POLYGON((76.9410753250122 11.05135492884294,76.94399356842041 11.055229898350953, 76.95734024047851 11.053439837277391,76.96186780929565 11.045963581774801,76.94744825363159 11.044805271627415,76.93420886993408 11.048764567419667, 76.9410753250122 11.05135492884294))', 4326));If i do not reverse my Lat and Long value in a pair and if my query isINSERT INTO SpatialTable (GeogCordinate)VALUES (geography::STGeomFromText('POLYGON((11.05135492884294 76.9410753250122,11.055229898350953 76.94399356842041,11.053439837277391 76.95734024047851,11.045963581774801 76.96186780929565,11.044805271627415 76.94744825363159,11.048764567419667 76.93420886993408,11.05135492884294 76.9410753250122))', 4326));then it works. I am confused if i need on how to pair the Lat and long value before converting it into a geograpahy data type.Any help is highly appreciated. Thanks & RegardsKrishna Kumar. P

Need to call a table as a variable

Posted: 16 Apr 2013 02:44 AM PDT

I need to pull records from a table and that would be pretty simple, however, the select statement needs to run as a sproc that passes a variable input as the table name. Let me illustrate:create procedure dbo.sproc(@tablename varchar(10))AsSelect cust#, fname, lname... other fieldsFrom tableAWhere Cust# in (Select Cust# from @Tablename)the error is telling to declare the table variable, but if I declare it as table I have to include the fields and I dont want to. Does this work better with dynamic SQL? What's the best way to do this?

Wildcard DB name in VIEW of Procedure - Possible?

Posted: 16 Apr 2013 02:31 AM PDT

Hi there,I've been trying to figure out whether it's possible to have a Wildcard DB name in a SELECT query. eg. Database1, Database2, Database3, Database4, Database5, Database6 all have the same schema - but for different aspects of the business.What I would like to figure out to do is to run something like this:SELECT Col1, Col2, Col3, Col4, Col5 FROM AllDatabases (where AllDatabases is a concatenation of Database1-6)At the moment, the best solution i've come up with is to loop round all the DB's and place the results into a temporary table, then select the results from the temporary table.HOWEVER - the list of DB's *could* change. Not often, but it's possible. Ideally it needs to be as dynamic as possible.Am I barking up the wrong tree here, or should I just stick with the looped concatenation of the DB's ?Dave

Group of Groups

Posted: 15 Apr 2013 03:11 AM PDT

Hello All,Thanks if you would like to help.I am hoping there is a 'best practice' way to solve this problem but searching 'sql server groups' gets a lot of results.I have two tables, groups and group members. My goal is to write a function that when passed a group code will return all the group members.The complicating factor is when a group contains other groups. I'd like to allow for multiple levels of groups.I am certainly open to revising my table structures at this point. I expect dozens of groups and thousands of group members. I also have a Members table (not shown) if that helps.Passing C should get the members 1,2,3,4Passing D should get 4,1,2Passing E should get 1,2,3,4,5IF OBJECT_ID('[dbo].[GroupMembers]') IS NOT NULL DROP TABLE [dbo].[GroupMembers]IF OBJECT_ID('[dbo].[Groups]') IS NOT NULL DROP TABLE [dbo].[Groups]CREATE TABLE [dbo].[Groups]( [GroupCode] [varchar](20) NOT NULL, [GroupDescription] [varchar](20) NULL CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED ([GroupCode] ASC)) CREATE TABLE [dbo].[GroupMembers]( [GroupCode] [varchar](20) NOT NULL, [Member] [varchar](20) NOT NULL, [MemberType] [varchar](12) NOT NULL CONSTRAINT [PK_GroupMembers] PRIMARY KEY CLUSTERED ( [GroupCode] ASC, [Member] ASC, [MemberType] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[GroupMembers] WITH CHECK ADD CONSTRAINT [FK_GroupMembers_Groups] FOREIGN KEY([GroupCode])REFERENCES [dbo].[Groups] ([GroupCode])GOINSERT INTO Groups (GroupCode,GroupDescription) VALUES ('A','Simple group')INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('B','Simple group')INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('C','Group of Groups')INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('D','Mixed')INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('E','Complicated')INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('A','1','Individual')INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('A','2','Individual')INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('B','3','Individual')INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('B','4','Individual')INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('C','A','Group')INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('C','B','Group')INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('D','A','Group')INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('D','4','Individual')INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('E','C','Group')INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('E','5','Individual')SELECT * FROM [dbo].[Groups]SELECT * FROM [dbo].[GroupMembers]

Need advice on this string manipulate scalar UDF

Posted: 09 Apr 2013 11:44 PM PDT

Dear all,[b]Can you please comment on this UDF, is there any better way to do it?For example CLF scalar UDF or inline table UDF?[/b]There is around 50 Million rows in TableABC and it will be around 1GB in size if it is exported as plain text .we want to use this function in this way:[code="sql"]SELECT 'D' AS RECORDTYPECODE, a.[Id],a.[Col1],a.[Col2],a.[Col3],%DBNAME%.ReplaceSplVarcharsBICD(a.[Col4]),%DBNAME%.ReplaceSplVarcharsBICD(a.[Col5]),%DBNAME%.ReplaceSplVarcharsBICD(a.[Col6]),a.[genID] FROM %DBNAME%.TableABC a[/code]The function is shown below:[code="sql"]USE [BPSUAT_BICD]GO/****** Object: UserDefinedFunction [dbo].[ReplaceSplVarcharsBICD] Script Date: 04/10/2013 16:08:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE function [dbo].[ReplaceSplVarcharsBICD](@value varchar(8000))RETURNS varchar(8000)asbegindeclare @result varchar(8000);-- This function is used to Replace some Special characters with space for varchar and char datatypes.-- Replacing for Enter, newlineselect @result = REPLACE(REPLACE(REPLACE(@value, CHAR(10) + CHAR(13), ' '),CHAR(10),' '), CHAR(13),' ');--Replacing other non printable characters from Hex 00 to Hex'1F' and Hex'7F'select @result = REPLACE(@result, CHAR(0),' ');select @result = REPLACE(@result, CHAR(1),' ');select @result = REPLACE(@result, CHAR(2),' ');select @result = REPLACE(@result, CHAR(3),' ');select @result = REPLACE(@result, CHAR(4),' ');select @result = REPLACE(@result, CHAR(5),' ');select @result = REPLACE(@result, CHAR(6),' ');select @result = REPLACE(@result, CHAR(7),' ');select @result = REPLACE(@result, CHAR(8),' ');select @result = REPLACE(@result, CHAR(9),' ');--select @result = REPLACE(@result, CHAR(10),' ');select @result = REPLACE(@result, CHAR(11),' ');select @result = REPLACE(@result, CHAR(12),' ');--select @result = REPLACE(@result, CHAR(13),' ');select @result = REPLACE(@result, CHAR(14),' ');select @result = REPLACE(@result, CHAR(15),' ');select @result = REPLACE(@result, CHAR(16),' ');select @result = REPLACE(@result, CHAR(17),' ');select @result = REPLACE(@result, CHAR(18),' ');select @result = REPLACE(@result, CHAR(19),' ');select @result = REPLACE(@result, CHAR(20),' ');select @result = REPLACE(@result, CHAR(21),' ');select @result = REPLACE(@result, CHAR(22),' ');select @result = REPLACE(@result, CHAR(23),' ');select @result = REPLACE(@result, CHAR(24),' ');select @result = REPLACE(@result, CHAR(25),' ');select @result = REPLACE(@result, CHAR(26),' ');select @result = REPLACE(@result, CHAR(27),' ');select @result = REPLACE(@result, CHAR(28),' ');select @result = REPLACE(@result, CHAR(29),' ');select @result = REPLACE(@result, CHAR(30),' ');select @result = REPLACE(@result, CHAR(31),' ');select @result = REPLACE(@result, CHAR(127),' ');--Replacing the pipe symbol select @result = REPLACE(@result, CHAR(124),' ');--Repalcing the NULsselect @result = Nullif(@result,'');--Removing the Right Spaces select @result = RTRIM(@result);--Removing the Left Spacesselect @result = LTRIM(@result);return @result;end[/code]or should we use CLF scalar UDF like this[code="VB"] using System; using System.Data.Sql; using System.Data.SqlTypes; using System.Collections.Generic; using Microsoft.SqlServer.Server; using System.Text; using System.Text.RegularExpressions; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString ReplaceSplVarcharsBICD(SqlString s){ if (s.IsNull) return String.Empty; //Removing the Right Spaces and Left Spaces string s1 = s.ToString().Trim(); if (s1.Length == 0) return String.Empty; StringBuilder tmpS = new StringBuilder(s1.Length); //striping out the "control characters" //Control characters are non-printing and formatting characters, such as ACK, BEL, CR, FF, LF, and VT. The Unicode standard assigns the following code points to control //characters: from \U0000 to \U001F, \U007F, and from \U0080 to \U009F. According to the Unicode standard, these values are to be interpreted as control characters unless //their use is otherwise defined by an application. Valid control characters are members of the UnicodeCategory.Control category. //Replacing for Enter, newline 0D0A or \x000D (13) or \x000A (10) //Replacing other non printable characters from Hex 00 to Hex'1F' and Hex'7F' subset of control characters //Replacing the pipe symbol \x007C (124) //Repalcing the NULs 00 for (int i = 0; i <= s1.Length; i++) { if (!Char.IsControl(s1[i]) & !s1[i] =='\x000D' & !s1[i] =='\x000A' & !s1[i] =='\x007C') { tmpS.Append(s1[i]); } else { tmpS.Append(' '); } } string result = tmpS.ToString().Trim(); return result; } }[/code]

comparing list to master list

Posted: 15 Apr 2013 05:25 PM PDT

How would you write a query that would return only the items in the where clause, which were not found in the table? ie. 'fff','ggg','hhh','iii' are not found in the table. I could build a second table with all the values in the where clause and do an except, but that is too much work...[code="plain"]create table List(id int,name varchar(20))insert into Listvalues(1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd'),(5,'eee'),(6,'jjj'),(7,'kkk'),(8,'lll');select * from Listwhere name not in ('ooo','bbb','ccc','ddd','eee','fff', 'ggg', 'hhh', 'iii', 'jjj','kkk','lll');[/code]

Can someone help with a TSQL date sorting issue?

Posted: 15 Apr 2013 01:59 PM PDT

I would like to retrieve data for all the fields listed in the query below. The problem is that I would like to partition Collection into 3 date fields then sort the columns by the most Resent date (DESC Order) See query and sample output below:SELECTName,MeterId,Collection Date,Collection Amount,MeterNumber,BlockfaceFROM dbo.CollectionSummaries C WITH(NOLOCK) JOIN Meters M WITH(NOLOCK) ON M.MeterId = C.MeterId LEFT JOIN RouteAssignments RA WITH(NOLOCK) ON RA.MeterId = M.MeterId LEFT JOIN [Routes] R WITH(NOLOCK) ON R.RouteId = RA.RouteId JOIN RouteTypes RT WITH(NOLOCK) ON RT.DisplayName = 'Collection' JOIN dbo.Blockfaces B WITH(NOLOCK) ON B.BlockFaceId = M.BlockFaceId JOIN dbo.Streets S WITH(NOLOCK) ON S.StreetId = B.StreetId WHERE AND R.RouteTypeId = 1 Group by Name, MeterId, CollectionDate, MeterNumber, Blockface Order by CollectionDate DESC[b]Sample Output[/b][b]Column #1 Column#2 (Column#3[/b] [u]CollectionDate1[/u] [u]CollectionDate2[/u] [u]CollectionDate3 [/u]2013/04/15 2013/03/01 2012/12/14 2013/04/14 2013/02/10 2012/11/022013/04/02 2013/01/30 2012/11/01Any suggestions would be greatly appreciated.Thanks

Using pivot in sql

Posted: 15 Apr 2013 02:18 AM PDT

Hello,I have a sql table as belowAutoId PID ItemId Item ItemValue Date7560 432 1 Wbc 4 05/05/12 00:007561 432 2 HCT 4 05/05/12 00:007562 432 3 W/L/M 4 05/05/12 00:007563 432 4 Bilirubin 485 06/05/12 00:007564 432 1 Wbc 45 06/05/12 00:007565 432 2 HCT 4 06/05/12 00:00 I tried to use the pivot to display the result as below. But it didn't worked for me. Does any one have any idea on how to get this done? AutoId PID ItemId Item 05/05/12 06/05/127560 432 1 Wbc 47561 432 2 HCT 47562 432 3 W/L/M 47563 432 4 Bilirubin 4857564 432 5 Wbc 457565 432 6 HCT 4Thanks,VIjay

Getting only Street name suite information from the address.

Posted: 15 Apr 2013 08:33 AM PDT

Hi All,Thanks for looking into my post, well I been scratching my head for a long time now the requirement which sound pretty simple turned out to be a tough one, below is the requirement....I need to seprate (Street Number, Street Name, Suite\Apt information or any zip found)Address looks like this1) 123 Main St2) 123 NW.17 St3) One Washington Way, Suite 4054) 1 Governement Dr #400, Washington, 01211So far I was able to seprate out 123 Main St, but whenever I go further using Char\Pat index in my string the address from above line 2 gets scramblled such as it becomes (123 NW St)My main gole is to break htis string and put it in a table and then concatinate.....Any help would be much appreciated.

SSMS Query Logging

Posted: 23 Sep 2012 07:31 PM PDT

We have a user who has come from an environment (SAS) where all of the queries she ran were logged, along with number of rows affected and any error messages.At the end of a session, she could save this log as a text file.A typical session would include a multitude of DML queries to move data around and populate calculated fields.She liked the fact that she could always go back and review the statements that were executed and how many rows were being affected and has asked whether we can provide something similar in SSMS.A brief investigation suggested not. The SSMS Tools add-on gets quite close with its SQL History functionality, but this does not capture errors or number of rows affected.I can write a stored proc which executes SQL for her and captures the required information, but this seems cumbersome because she'd have to wrap each of her queries with exec ''.Does anyone have any other ideas? Thanks in advance.

How the ssrs expressions works for DateDiff?

Posted: 15 Apr 2013 03:48 AM PDT

I'm working on SSRS reports there is on column i.e Holding period where we actually calculate forhow many days the company is hold in our database (we compare with buy date)and ssrs expression is=DateDiff(DateInterval.Day, Fields!BuyDate.Value, First(Fields!Date.Value, "DailyPosition"))Which is coming wrong could any one help to execute correct calulation.Buy date is a normal date column from table , and "DailyPosition" is my dataset name from ssrs report.for example Company ABC buy date is 2012-03-13 00:00:00.000 and last parameter date is 2012-12-31 00:00:00.000below query giving me result as 293 select DATEDIFF (day,'2012-03-13 00:00:00.000','2012-12-31 00:00:00.000')which is wrong as my holding period is 126 days onlyplease help me to correct the expression.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog