Nearest distance

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 appreciatedThey talk about something like that here, in their database section...

http://skyserver.fnal.gov/en/

As far as understanding it....I have enough trouble tieing my shoes...|||Hi
I looked through the site but is not of much help..can you elaborate
thanks

Originally posted by Brett Kaiser
They talk about something like that here, in their database section...

http://skyserver.fnal.gov/en/

As far as understanding it....I have enough trouble tieing my shoes...|||Originally posted by Brett Kaiser
As far as understanding it....I have enough trouble tieing my shoes...

You didn't get this part, huh...

What are you trying to do...maybe it's a simple answer...

"Distance" is a relative thing...

Got indexes?|||Hi
I need the nearest distance in miles of one point from the other.

Originally posted by namitao
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|||Nearest distance between two values? A distance can only be "nearest" to one point (in the general case), because you can't optimize for more than one criteria. Are you talking about some kind of linear regression between the points?

I think you need to post some sample data and an example of the result you are looking for.|||I'm confused...I originally thought you wanted the distance between rows of data...

Do you want the distance between points on a map?

I did this once for a delivery system...

You want to google the "Great Circle" trig function...I can't find the math...but here's someone who built something...

http://williams.best.vwh.net/gccalc.htm

But you need longitude and latitude of the addresses...

Is that what you're looking for?|||Hi
For Example I have 2 tables
Table A
Number Latitude Longitude Time total
1 48.2951 -122.276 1:22:49 -87
2 48.2952 -122.292 1:17:35 -92
3 48.2952 -122.292 1:16:35 -91
4 48.2952 -122.276 1:21:59 -86
5 48.2952 -122.276 1:22:48 -91
6 48.2953 -122.292 1:17:34 -87
Table B
Number Latitude Longitude Time total_c
1 48.2904 -122.271 1:24:30 -87
2 48.2904 -122.271 1:23:51 -88
3 48.2904 -122.271 1:24:29 -87
4 48.2904 -122.271 1:23:52 -85
5 48.2904 -122.271 1:24:28 -86.5
6 48.2904 -122.271 1:23:53 -86

I need to find the shortest distance between the points of the 2 tables.
I need to find the shortest distance of points in Table B to point in Table A.
I hope this can help someone answer my question
:(
Thanks


Originally posted by Brett Kaiser
I'm confused...I originally thought you wanted the distance between rows of data...

Do you want the distance between points on a map?

I did this once for a delivery system...

You want to google the "Great Circle" trig function...I can't find the math...but here's someone who built something...

http://williams.best.vwh.net/gccalc.htm

But you need longitude and latitude of the addresses...

Is that what you're looking for?|||I did this in Access once...

You need the formual

http://en2.wikipedia.org/wiki/Great_circle_distance

Then create it as a udf and join the tables passing the 2 ponts in...

Have to udf return the distance...

I should rewrite this in sql server...I'll have to dig it up...

It was actually a lot of fun building it (...geez what a geek)

Near parameter

Hello:
Somebody knows how much distance between words uses the "NEAR" parameter?
PD: Pardon for my English.
IIRC its 50 words, after 50 words with a FreeText query the rank drops to 0,
after IIRC 1365 words the words are no longer considered to be near.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vas" <vas@.europeadederecho.com> wrote in message
news:u3RkhIq6FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hello:
> Somebody knows how much distance between words uses the "NEAR" parameter?
> PD: Pardon for my English.
>
|||Hi Hilary:
My query uses "containstable" clause to search with "NEAR" parameter
(CONTAINSTABLE(table, field, '"actor" NEAR "gana"'),
but not return all records existent. I try with "freetexttable", but return
records that not correspond with the query.
Any suggestion?.
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:uTOC$Zq6FHA.4076@.tk2msftngp13.phx.gbl...
> IIRC its 50 words, after 50 words with a FreeText query the rank drops to
> 0, after IIRC 1365 words the words are no longer considered to be near.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vas" <vas@.europeadederecho.com> wrote in message
> news:u3RkhIq6FHA.476@.TK2MSFTNGP15.phx.gbl...
>
|||Vas,
Could you post the full output of -- SELECT @.@.version -- as this is helpful
info in understanding your environment.
Additionally, is the FT-enabled "table"'s column "field" defined as an IMAGE
datatype and contains binary files, such as MS Word or Adobe PDF files? If
not, does the text in the "field" column contain any Carriage Returns (CR)
or Line Feed (LF) characters? Finally, how many rows are in your FT-enabled
"table"?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Vas" <vas@.europeadederecho.com> wrote in message
news:ewjFtyq6FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Hi Hilary:
> My query uses "containstable" clause to search with "NEAR" parameter
> (CONTAINSTABLE(table, field, '"actor" NEAR "gana"'),
> but not return all records existent. I try with "freetexttable", but
> return records that not correspond with the query.
> Any suggestion?.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
> news:uTOC$Zq6FHA.4076@.tk2msftngp13.phx.gbl...
>
|||Hi John:
I will attempt answering to everything with my english, jeje.
Number of records: 384.000
Colum type: Image.
Colum Content: HTM Documents.
I have another colum with extensin of documents (htm) (varchar(3)).
My query is:
SELECT [KEY] FROM CONTAINSTABLE(ST, Texto, '"actor" near "gana"')
The number of result is 50, when really is 72.
SELECT @.@.version:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Thanks.
"John Kane" <jt-kane@.comcast.net> escribi en el mensaje
news:%23OPiXgs6FHA.1000@.tk2msftngp13.phx.gbl...
> Vas,
> Could you post the full output of -- SELECT @.@.version -- as this is
> helpful info in understanding your environment.
> Additionally, is the FT-enabled "table"'s column "field" defined as an
> IMAGE datatype and contains binary files, such as MS Word or Adobe PDF
> files? If not, does the text in the "field" column contain any Carriage
> Returns (CR) or Line Feed (LF) characters? Finally, how many rows are in
> your FT-enabled "table"?
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Vas" <vas@.europeadederecho.com> wrote in message
> news:ewjFtyq6FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
|||can you do this query? This will tell you which rows don't show up. Then
check the separation distance in these rows
select * from (select [key] from containstable(ST, Texto, '"actor" near
"gana"')) as X
right join (select [key] from containstable(ST, Texto, '"actor" OR
"gana"')) as Y on y.[key]=x.[key]
where x.[key] is null
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vas" <vas@.europeadederecho.com> wrote in message
news:eYi6JHt6FHA.224@.TK2MSFTNGP10.phx.gbl...
> Hi John:
> I will attempt answering to everything with my english, jeje.
> Number of records: 384.000
> Colum type: Image.
> Colum Content: HTM Documents.
> I have another colum with extensin of documents (htm) (varchar(3)).
> My query is:
> SELECT [KEY] FROM CONTAINSTABLEST, Texto, '"actor" near "gana"')
> The number of result is 50, when really is 72.
> SELECT @.@.version:
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
>
> Thanks.
> "John Kane" <jt-kane@.comcast.net> escribi en el mensaje
> news:%23OPiXgs6FHA.1000@.tk2msftngp13.phx.gbl...
>
|||Hi Hilary:
This query return all records that not correspond with the condition. Is
possible to change the number of words of distance to evaluate?.
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
news:ef$qphu6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> can you do this query? This will tell you which rows don't show up. Then
> check the separation distance in these rows
> select * from (select [key] from containstable(ST, Texto, '"actor" near
> "gana"')) as X
> right join (select [key] from containstable(ST, Texto, '"actor" OR
> "gana"')) as Y on y.[key]=x.[key]
> where x.[key] is null
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vas" <vas@.europeadederecho.com> wrote in message
> news:eYi6JHt6FHA.224@.TK2MSFTNGP10.phx.gbl...
>
|||No, if the data was stored in the text or char columns I could write
something to do this, but not when they are stored in binary.
What you need to do is look in these rows returned by the query. Figure out
what the word separation or if there is another problem preventing them from
showing up in your near searches. Sometimes the angle brackets in html tags
prevent words from being indexed.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vas" <vas@.europeadederecho.com> wrote in message
news:em7pxW06FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Hi Hilary:
> This query return all records that not correspond with the condition.
> Is possible to change the number of words of distance to evaluate?.
> Thanks.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> escribi en el mensaje
> news:ef$qphu6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>

NEAR operator

Is it possible in any way to control the NEAR operator so that it returns
only records containing the search words within a certain distance - e.g
within 3 words or 5 words or a paragraph etc.?
Apparently the way the NEAR operator works is that it returns all (or almost
all..) the records containing the specified words, and then ranks them based
on the words 'nearness'. The problem with this approach is that if the result
of the search are display ordered not by rank but by some other criteria
using 'NEAR' is exactly the same as using 'AND' - and as a matter of fact
newspaper librarians and journalist always sort the result of a search by
publishing date, not by ranking, so no NEAR operator with SQL full text for
them.
Thank you
- Michele
Michele,
Unfortunately, no. There is no way to control how the NEAR operator
determines "nearness" as it is hard-coded at 50 words and the "definition of
nearness is fixed inside mssearch", and are not user controllable :-(
The following quote (from a Microsoft FTS Developer) was taken from another
thread on this subject related to SQL Server 2005 (Yukon), but also applies
to SQL Server 2000 and proximity (or NEAR) searches and RANK:
"distance between terms for a match
number of matches
document length
etc..
so it is possible for a document with term1 right next to term2 to return a
lower rank than another document with many matches with greater distance
between terms:
eg:
document1 = term1 term2 word word word word word word word word word word
word word word.... word word
document2 = term1 word term1 word term1 word term2 word term1 word term2
word term1 word term2 word term1 word term2 word term1 word term2
document1 may have a lower rank that document2 because it has fewer matches
even though the one match it has is very "near"."
Hopefully this sheds more light on this subject.
Thanks,
John
"Michele Mottini" <Michele Mottini@.discussions.microsoft.com> wrote in
message news:380FD11C-01C0-4AC8-BC8B-82EC0524F6B7@.microsoft.com...
> Is it possible in any way to control the NEAR operator so that it returns
> only records containing the search words within a certain distance - e.g
> within 3 words or 5 words or a paragraph etc.?
> Apparently the way the NEAR operator works is that it returns all (or
almost
> all..) the records containing the specified words, and then ranks them
based
> on the words 'nearness'. The problem with this approach is that if the
result
> of the search are display ordered not by rank but by some other criteria
> using 'NEAR' is exactly the same as using 'AND' - and as a matter of fact
> newspaper librarians and journalist always sort the result of a search by
> publishing date, not by ranking, so no NEAR operator with SQL full text
for
> them.
> Thank you
> - Michele
>