# Distance Between Postal Codes

Jul 23, 2005

I'm looking to find out how I'd go about setting up a database where a
visitor to my site could punch in their postal code, and find out how far
they are from another postal code. For example, AutoTrader has this feature
I believe to tell you how far the vehicle is from you. Dating sites have
them so you can do proximity searches.

Anyone have any ideas where I could start? I'm thinking the post office,
but if anyone else has suggestions, I'm open to hear them.

Thanks!

## Using Specific Criteria For Exact Postal Codes?

Feb 20, 2012

I have to find the number of orders sent to two different postal codes but to get all of the right columns, I have to use 2 different tables.

So, I used WHERE to connect the two tables with the primary keys, but where do I use the specific criteria for the exact postal codes?

## Haversine SQL Trouble - Distance Between Zip Codes

Jul 20, 2005

I am trying to use the haversine function to find the distance betweentwo points on a sphere, specifically two zip codes in my database. I'mneither horribly familiar with SQL syntax nor math equations :), so Iwas hoping I could get some help. Below is what I'm using and it is,as best as I can figure, the correct formula. It is not however,giving me correct results. Some are close, others don't seem right atall. Any ideas?SET @lat1 = RADIANS(@lat1)SET @log1 = RADIANS(@log1)SET @lat2 = RADIANS(@lat2)SET @log2 = RADIANS(@log2)SET @Dlat = ABS(@lat2 - @lat1)SET @Dlog = ABS(@log2 - @log1)SET @R = 3956 /*Approximate radius of earth in miles*/SET @A = SQUARE(SIN(@Dlat/2)) + COS(@lat1) * COS(@lat2) *SQUARE(SIN(@Dlog/2))SET @C = 2 * ATN2(SQRT(@A), SQRT(1 - @A))/*SET @C = 2 * ASIN(min(SQRT(@A))) Alternative calculation*/SET @distance = @R * @Cthnx,cjrsumner

## Postal Code Search And LIKE Statement

Oct 11, 2006

I'm trying to create a form that allows someone to find an
address from a postal code search in the database. My query works exactly as I’d
like in query builder using the following select:

Select [FIELD_LIST] from addresses WHETE POSTCODE LIKE ‘%’+@POSTCODE+’%’

I then pass the user entered post code to the select statement
which is executed. However, I’m getting some odd behaviour. Assuming there is
an address in the database with the post code
“LS11 0ES”...

If I search for LS11, nothing is returned;

If I search for LS11%, nothing is returned;

If I search for %LS11%, nothing is returned;

If I search for %LS11%%, the data is returned.

If I search for %LS%1%%, the data is returned (as is LS21
0ES etc. etc.).

However, I want the user to be able to enter shorter search
strings and it to pull all the data back out, so they can enter a substring
such as LS and it will pull out all the data without the users needing to enter
the full pattern of % symbols.

If I go into query builder (in visual web developer) and
enter just “LS” it works as I’d want, but not when pulled from a web page.

Any ideas?

Thanks

## SQL Server 2008 :: Split Postal Code Range Into Single Row

Jul 8, 2015

I got a table with organisation codes with postcal code ranges, from-to.

Example:
Organisationcode, startpostalcode, endpostalcode
001 52005249

I would like to generate rows for this range like this:

001 5200
001 5201
001 5202
.....
001 5249

The table looks like this:

SELECT OrganisationCode, PostcalCodeStart, PostalCodeEnd
FROM dbo.DimOrganisation

## Nearest Distance

Jan 21, 2004

Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciated

## Mirroring Over Distance????

Mar 1, 2007

Is there a recommended practice for mirroring in regards to distance? Is it best practice to mirror with both nodes at the same physical location and use another method for failing over to a remote location or can one just put the other node in the mirror a few thousand miles away? I'm suspecting not.

## Edit Distance

Mar 18, 2008

Hi,
please, it is possible to know the edit distance used in the fuzzy lookup/grouping.
On this forum I read fuzzy lookup use 4-gram with fix size.
Does exist any document explaining how fuzzy lookup calculate the similarity? In other word, what kind of edit distance, algorithm is used by fuzzy lookup/grouping?
I hope I was enough clear with my poor english.
Thanks All

## Help W/ Distance Calculation Query

Mar 28, 2007

I'm trying to run a dyncamic query that returns all records within a specific distance of a certain point. The longitude and latitude of each record is stored in the database. The query is constructed from two dynamic variables \$StartLatitude and \$StartLongitude with represent the starting point.

SELECT UserID, ACOS(SIN(\$StartLatitude * PI() / 180) * SIN(Latitude * PI() / 180) + COS(\$StartLatitude * PI() / 180) * COS(Latitude * PI() / 180) * COS((\$StartLongitude - Longitude) * PI() / 180)) * 180 / PI() * 60 * 1.1515 AS Distance
FROM HPN_Painters
HAVING (Distance <= 150)

It runs fine until I add the 'HAVING (Distance <= 150)' clause, in which I recieve the error: Invalid column name 'Distance' It seems that Distance cannot be referenced in the HAVING clause.

## Database Mirrors Over Distance

Mar 20, 2007

Various posts have noted that mirroring over distance is not advisable or that either async connections should be used.

Are there any limits/recommendations i.e. if two datacenters are a couple of files part with 10GBs fibre links and <50ms response times would this be acceptable for high-availability mirroring?

## Cluster Euclidean Distance

Mar 27, 2007

I am new to data mining so please excuse my ignorance. Lets assume

- i have created a cluser model

- identified 3 clusters ( a, b, c)

- each record consists of 15 columns

- collecting new records( 15 variables) real time

what i would like to do is plot these new records programmatically as i collect them realtime. I assume this new record will belong to one of these three clusters. I believe we can find the cluster this new record belongs to by ' SELECT Cluster()....' and distance from the center of the cluster by ClusterDistance(). To plot this on a 2-dimentional space i need (x, y).

ClusterDistance() could be Y but what will be X.

thanks.

## Distance Between Two Points Lat/long

Jan 12, 2008

I have a user defined function, I want to determine the distance between the 2 points. I have it working but i'm having a problem getting to print.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Code Snippetcreate function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)
returns float

as

begin

declare @Ans as float
declare @Miles as float

set @Ans = 0
set @Miles = 0

if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2 is
null or @lat2 = 0 or @long2 is null or @long2 = 0

begin

return ( @Miles )

end

set @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)

set @Miles = CEILING(@Miles)

return ( @Miles )

end

DECLARE @RC float
EXEC Distance '39.943762', '-78.122265', '32.334709', '-96.633546'
PRINT @RC /* in miles */

## Great Circle Distance Calculation

Oct 15, 2007

Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation

## SQL 2012 :: How To Calculate The Distance In Miles

Oct 15, 2015

DECLARE @Latitude NUMERIC(9, 6), @Longitude NUMERIC(9, 6)

DECLARE @MyLatitude NUMERIC(9, 6), @MyLongitude NUMERIC(9, 6)
Set @Latitude = 42.329596;
Set @Longitude = -83.709286;
Set @MyLatitude = 42.430883;
Set @MyLongitude = -82.923642;

Question: How do we calculate the distance in miles between the 2 points.

## Find Data Within 10m Distance Of Coordinates?

Nov 22, 2013

I am trying to write a piece of SQL which gives me a list of enquiries within 10 metre distance of a enquiry.

The idea is to identify possible duplicates.

Table: enquiry

Primary key: enquiry_number

Co-ordinates data fields: enquiry.enquiry_easting and enquiry.enquiry_northing.

I will need to self-search on the same table to find possible enquiries within 10m distance.

## Levenshtein Edit Distance Algorithm

Jun 24, 2005

See here www.merriampark.com/ld.htm for information about the algorithm. This page has a link (http://www.merriampark.com/ldtsql.htm) to a T-SQL implementation by Joseph Gama: unfortunately, that function doesn't work. There is a debugged version in the also-referenced package of TSQL functions (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=502&lngWId=5), but this still has the fundamental problem that it only works on pairs of strings up to 49 characters.

CREATE FUNCTION edit_distance(@s1 nvarchar(3999), @s2 nvarchar(3999))
RETURNS int
AS
BEGIN
DECLARE @s1_len int, @s2_len int, @i int, @j int, @s1_char nchar, @c int, @c_temp int,
@cv0 varbinary(8000), @cv1 varbinary(8000)
SELECT @s1_len = LEN(@s1), @s2_len = LEN(@s2), @cv1 = 0x0000, @j = 1, @i = 1, @c = 0
WHILE @j <= @s2_len
SELECT @cv1 = @cv1 + CAST(@j AS binary(2)), @j = @j + 1
WHILE @i <= @s1_len
BEGIN
SELECT @s1_char = SUBSTRING(@s1, @i, 1), @c = @i, @cv0 = CAST(@i AS binary(2)), @j = 1
WHILE @j <= @s2_len
BEGIN
SET @c = @c + 1
SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j-1, 2) AS int) +
CASE WHEN @s1_char = SUBSTRING(@s2, @j, 1) THEN 0 ELSE 1 END
IF @c > @c_temp SET @c = @c_temp
SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j+1, 2) AS int)+1
IF @c > @c_temp SET @c = @c_temp
SELECT @cv0 = @cv0 + CAST(@c AS binary(2)), @j = @j + 1
END
SELECT @cv1 = @cv0, @i = @i + 1
END
RETURN @c
END

## Great Circle Distance Calculation

Oct 15, 2007

Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation

## How To Control The Distance Between The Two Matrix? Or (matirx And Table )

Feb 1, 2007

hi everyone:

the report show two tables two matrixs

how can i control the distance between them

I want to set the same distance between the table and matrix

or (table and table )

## Any Distance Limited For Failover Clustering Solution?

Jan 2, 2008

Could I implement a failover cluster solution on the two DBs which are based in two different cities?
Possible?

## Calculating Distance Based On Latitude And Longitude

Jun 14, 2006

I need to be able to take the latitude and logitude of two locations and compare then to determine the number of miles between each point. It doesn't need to account for elevation, but assumes a flat plane with lat and long.

Does anyone have any algorithms in T-SQL to do this?

## T-SQL (SS2K8) :: How To GROUP BY With Shortest Distance By Account Number

Mar 11, 2014

Given the following example;

declare @CustIfno table (AccountNumber int, StoreID int, Distance decimal(14,10))
insert into @CustIfno values ('1','44','2.145223'),('1','45','4.567834'),
('1','46','8.4325654'),('2','44','7.8754345'),('2','45','1.54654323'),
('2','46','11.5436543'), ('3','44','9.145223'),('3','45','8.567834'),
('3','46','17.4325654'),('4','44','7.8754345'),('4','45','1.54654323'),
('4','46','11.5436543')

How can I show the shortest Distance by AccountID and StoreID. Results would look like this;

AccountNumberStoreID Distance
1 44 2.1452230000
2 45 1.5465432300
3 45 8.5678340000
4 45 1.5465432300

## Great Circle Distance Function - Haversine Formula

Mar 28, 2007

This function computes the great circle distance in Kilometers using the Haversine formula distance calculation.

If you want it in miles, change the average radius of Earth to miles in the function.

create function dbo.F_GREAT_CIRCLE_DISTANCE
(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
returns float
as
/*
fUNCTION: F_GREAT_CIRCLE_DISTANCE

Computes the Great Circle distance in kilometers
between two points on the Earth using the
Haversine formula distance calculation.

Input Parameters:
@Longitude1 - Longitude in degrees of point 1
@Latitude1 - Latitude in degrees of point 1
@Longitude2 - Longitude in degrees of point 2
@Latitude2 - Latitude in degrees of point 2

*/
begin

declare @lon1 float
declare @lon2 float
declare @lat1 float
declare @lat2 float

declare @a float
declare @distance float

-- Sets average radius of Earth in Kilometers

set @lon1 = radians( @Longitude1 )
set @lon2 = radians( @Longitude2 )
set @lat1 = radians( @Latitude1 )
set @lat2 = radians( @Latitude2 )

set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) +
(cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )

set @distance =
@radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

return @distance

end

Edit: corrected spelling

CODO ERGO SUM

## Stored Procedure To Retrieve Zipcodes Within A Specified Zipcode And Distance

Apr 28, 2008

Hi All,
Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.

## SQL Server 2008 :: Spatial Data Not Returning Correct Distance

Apr 29, 2015

I have the two following locations.

They're both towns in Australia , State of Victoria

Fitzroy,-37.798701, 144.978687
Footscray,-37.799736, 144.899734

After running geography::Point(Latitude, Longitude , 4326) on the latitude and longitude provided for each location, my Geography column for each row is populated with the following:

Fitzroy, 0xE6100000010C292499D53BE642C0A7406667511F6240
Footscray, 0xE6100000010C89B7CEBF5DE642C02D23F59ECA1C6240

In my SQL Query, I have the following which works out the distance between both towns. Geo being my Geography column

DECLARE @s geography = 0xE6100000010C292499D53BE642C0A7406667511F6240 -- Fitzroy
DECLARE @t geography = 0xE6100000010C89B7CEBF5DE642C02D23F59ECA1C6240 -- Footscray
SELECT @s.STDistance(@t)

The result I get is

6954.44911927616

I then looked at formatting this as in Australia we go by KM so after some searching I found two solutions one for Miles and the other KM

So I changed Select statement to look like this

select @s.STDistance(@t)/1000 -- format to KM

My result is then

6.95444911927616

When I go to google maps and do a direction request between the locations provided above it says 10.2km (depending on traffic)

Now I'm new to this spatial data within SQL, why would I get a different result from google maps?

Also I would like to round this number so its easier to use within my where statement so I'm using Ceiling as shown here:

SELECT CEILING(@s.STDistance(@t)/1000)

Is ceiling the correct way to go?

Reason I need to round this is because we are allowing the end user to search by radius so if they pass in 50km I will then say

Where CEILING(@s.STDistance(@t)/1000) < 50

## T-SQL (SS2K8) :: Returning Rows Within Certain Distance In Miles From City Using Longitude And Latitude

Nov 19, 2014

I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.

Two things I don't like about query:

1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results.
2. very slow to retrieve results (> 1 minute)

Sample DDL: 2 tables
(
date_created datetime,
[contact_first_name] varchar(20),
[contact_last_name] varchar(20),

[Code] .....

## Can This Be Done Just In Database (I Have Given Codes)

Oct 4, 2006

Hi , I have a database that records the users entrance to a building.The dates are recorded also .I have written some codes that to detect the period of dates that the person has entered.Lets say that a person named as jhon has entered the building on the days 02/08/2006 and 05/08/2006 and 11/08/2006 .Those dates are formated as dd/mm/yyyy . So that john has entered building for 3 times and the periods for the dates are one after another entrance is 3 days + 6 days =9 days .If you divide 9 by 3 we get the arithmetic average number 3 .So that we can say as john visits this building every 3 days and we can guess the next day that he may come. I have coded this and works great .I will paste the codes to the end of my message.But a master programmer friend of mine has said that I should have get this result by without writing code , by just using sql database .But some kind of stored procedure I mustn't use.So that I thought it can be done by views in sqldb .By using sql server enterprise manager I tried to use views but I could not succees. Can anyone guess this ? Here are my vb codes ... database data types : entry_id : int , identity user_id : int movie : nvarchar (50) dateenter : datetime stored procedure that selects the data from db: CREATE PROCEDURE veri_al     (         @user_id int     ) AS SELECT entry_id ,user_id, movie, dateenter from uye_aktiviteleri where (user_id=@user_id) ORDER BY entry_id ASC GO  Code:Dim conn As New SqlClient.SqlConnection         conn.ConnectionString = "data source=localhost;initial catalog=AFM;uid=nusret;pwd=araz"         Dim command As New SqlClient.SqlCommand         command.CommandText = "[veri_al]"         command.CommandType = CommandType.StoredProcedure         command.Connection = conn         command.Parameters.Add("@user_id", SqlDbType.Int, 4).Value = Val(TextBox1.Text)         If Not IsNumeric(TextBox1.Text) Then             Exit Sub         End If         Try             Dim adapter As New SqlClient.SqlDataAdapter             adapter.SelectCommand = command             Dim ds As New DataSet             adapter.Fill(ds, "uyeler")             DataGrid1.DataSource = ds.Tables("uyeler")             Dim recordcount As Integer 'Found the recordcount             recordcount = ds.Tables("uyeler").Rows.Count             Dim mydatarow_ilk As DataRow             Dim mydatarow_son As DataRow             'in stored procedure I used ORDER BY entry_id ASC so that first datarow             'will always be the first visit date and the last record of the             'datarow will be the last visit date             mydatarow_first= ds.Tables("uyeler").Rows(0)             mydatarow_last = ds.Tables("uyeler").Rows(recordcount - 1)             Dim first_date As String             Dim last_date As String             first_date = mydatarow_first("dateenter")              last_date =mydatarow_last("dateenter")             Dim average_ As Integer             'What is the aveare of the visits             ortalama = DateDiff("d", first_date , last_date ) / recordcount             Label2.Text = "Member had visited for " & recordcount & "times"             Label3.Text = "by average he/she comes here " & ortalama & " day to another."             Dim last_time_visit As Integer            last_time_visit = DateDiff("d", last-date , DateTime.Today())             Label4.Text = "Until the last visit it had been" & last_time_visit & " days"             Label5.Text = "Guess for the next visit is" & DateAdd("d", average_ , last_date)             ' MsgBox("average visits" & ortalama & " days" )         Catch z As Exception             MsgBox("error : " & z.Message())         Finally             MsgBox("Successfully calculated")         End Try     End Sub

## Brick Codes

Jul 20, 2005

Hi,I have a table of Locations around the country. My system produces reportsbased on these Locations. I also have a table containing Brick Codes e.g.Brick Post CodeAB51 AB51AB52 AB52AB55 AB55AB56 AB56AL01 AL1AL02 AL2AL03 AL3AL04 AL4How can I compare the first 3 or 4 letters of the postcode in the Locationstable to the corresponding entry in the Brick Codes table so I can add it tomy report?Thanks for your help

Oct 10, 2006

Hi,

I have another question about ADO (using C++). I have searched the MSDN but I haven't found a answer to my question (maybe I'm just too stupid)... If I make a connection to my SQL Server there may occur some errors, but how to find out what went wrong ?

In terms of code I have e.g. something like that:
...} catch (_com_error& e) { long numErrors = connection->Errors->Count; for (long i=0; i<numErrors; i++) { ADODB::ErrorPtr pErr = connection->Errors->GetItem(i); ..... }}

Now I could get the error number by pErr->GetNumber().
But with which symbolic constant has this to be compared to find out which error occured ?? I didn't find any...Or is there another better way to do this ?

P.S. I am using SQL Server 2005 Express

## Test Sql Codes

Jan 8, 2008

How can I test sql codes and don't want to connect to company database?
My company is using sql server 2000.
Below is the test codes which create a simple table, calculate and just want to check before
writing more complicated codes.

Thanks
Daniel Ku

-------------------------------------------------------------------------------------------------------------------------------

create table EquipmentUptime (
equipmentId int not null
,transactionId int not null
,dateOccured datetime not null
)
go
--
insert into EquipmentUptime values (1,200,'01/01/2007')
insert into EquipmentUptime values (2,200,'01/01/2007')
insert into EquipmentUptime values (3,200,'01/01/2007')
insert into EquipmentUptime values (2,100,'02/12/2007')
insert into EquipmentUptime values (1,100,'02/25/2007')
insert into EquipmentUptime values (3,100,'03/10/2007')
insert into EquipmentUptime values (1,200,'03/14/2007')
go
--
select
equipmentId
,100*(cast((cast(sum(UpDays)as decimal(6,3))/cast(365 as decimal(6,3))) as decimal(4,3))) 'PctUpTime'
from
(
select
c.equipmentId
,datediff(dd,c.[UpDate],c.DownDate) 'UpDays'
from
(
select
a.equipmentId
,a.dateOccured as 'UpDate'
,coalesce(
(select top 1
b.dateOccured
from
EquipmentUptime b
where
transactionId=100
and b.equipmentId=a.equipmentId
and b.dateOccured >= a.dateOccured
order by
b.dateOccured asc
),'01/01/2008') as 'DownDate'
from
EquipmentUptime a
where
a.transactionId=200
) c
) d
group by
d.equipmentId

## Error Codes?

Jun 28, 2007

My data flow component is throwing an error and the only help I get is the following:

error code: -1071607694

error column: 257

What in the world does this mean? Can it get more cryptic than this?

## Generating Serial Codes

Jul 29, 2001

Hi,

I have a table with a primary key, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do, automatically incrementing as fields are inserted.

## Inconsistent Return Codes Using BCP

Mar 2, 1999

I apologize for the length of this message, but I think I need to include all this info so that the problem is understood. I am having what appears to be a problem capturing the return code from a failed BCP.

I create a stored proc to use BCP to load a table:

declare @RC int
execute @RC = master..xp_cmdshell "bcp JON..W4KPV in e:inetpubftprootfinreslaw4kpv.g4000.data /Sdbmtss1 /m 0 /f d:mssqluserdatafinresW4KPV.fmt /Usa /P /e d:mssqluserdatafinrescp1.err /t""|"" /r "

select 'Return code from bcp = ', @RC
if @RC <> 0
BEGIN
print 'BCP Error.'
return (8)
END
GO

If I execute the SP, and encounter a transaction log full error, the return code is still zero:

1000 rows sent to SQL Server. 45000 total
1000 rows sent to SQL Server. 46000 total
Msg 1105, Level 17, State 2:
Server 'DBMTSS1', Line 1:

Can't allocate space for object 'Syslogs' in database 'Jon' because
the 'logsegment' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

(54 row(s) affected)

----------------------- -----------
Return code from bcp = 0

If I execute the SP again, it correctly returns a non-zero value:

Msg 1105, Level 17, State 2:
Server 'DBMTSS1', Line 1:
Can't allocate space for object 'Syslogs' in database 'Jon' because
the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

(6 row(s) affected)

----------------------- -----------
Return code from bcp = 1

(1 row(s) affected)

BCP Error.

Does anybody have an idea why this behaves this way? Any suggestions on how to trap an error on the first call?

Thanks,
Jon Carter

## Splitting 9 Digit Zip Codes

Jul 6, 2004

hey all
i am stuck with this little problem
I have a table with people's names and addresses and i have one column for the zip codes. sometimes it includes 5 digit zip codes like '70820' and some times it includes all nine digits like '70820-4565'

is there anyway to move the last 4 digits of the long zip codes into a new column? and remove the dash?

thanks