Prev: 527333 Reserch anything, nerw content up to date all sites 56
Next: Concatenate Mutiple Rows Data to Single RowColumn comma separated Data
From: James Riches on 24 Jan 2010 12:56
Hello there,

I have a database with positional information in it along the lines of
waypoints of various journeys ('tracks'). I am currenetly using SQL
Server 2000 so am unable to make use of the GIS types. The columns
are simply two floatst for lat and long, a timestamp and a track ID.

I would like to produce a query that will linearly interpolate between
the waypoints and tell me which tracks, if any were in a certain
ellipse at a given time.

I have constructed a very inefficient query to do this, but it takes
far too long to compute. Essentially my query does this:

1. For each unique track, get the waypoint before and after the given
time. I think i have to use 2 self joins to achieve this.

2. Use these fields to produce a row in a temp table for each track
that gives the estimated lat and long for each track at the given time

3. Use a complicated trigonometric expression to filter any tracks
not in the ellipse

Clearly this is very inefficient.

My question is, what is the best way to do this type of thing (query
based on an interpolated quantity, estimated position)? I am sure
that the GIS types in SQL Server 2008 will help but will they be able
to do this?

Many thanks
James
From: Erland Sommarskog on 24 Jan 2010 18:01
James Riches (jr2054(a)hotmail.com) writes:
> I have a database with positional information in it along the lines of
> waypoints of various journeys ('tracks'). I am currenetly using SQL
> Server 2000 so am unable to make use of the GIS types. The columns
> are simply two floatst for lat and long, a timestamp and a track ID.
>
> I would like to produce a query that will linearly interpolate between
> the waypoints and tell me which tracks, if any were in a certain
> ellipse at a given time.
>
> I have constructed a very inefficient query to do this, but it takes
> far too long to compute. Essentially my query does this:
>
> 1. For each unique track, get the waypoint before and after the given
> time. I think i have to use 2 self joins to achieve this.
>
> 2. Use these fields to produce a row in a temp table for each track
> that gives the estimated lat and long for each track at the given time
>
> 3. Use a complicated trigonometric expression to filter any tracks
> not in the ellipse
>
> Clearly this is very inefficient.
>
> My question is, what is the best way to do this type of thing (query
> based on an interpolated quantity, estimated position)? I am sure
> that the GIS types in SQL Server 2008 will help but will they be able
> to do this?

In Adam Machanic's "Expert SQL Server 2005 Development" there is a
chapter on spatial programming, written Hugo Kornelis. This chapter
may give you some ideas.

The spatial stuff in SQL 2008 goes completely over my head, so I cannot
tell whether it can help you with this problem. But since they are
designed for this kind of problems, I would expect them to. I would
certainly encourage you to download the Evaluation version of SQL 2008
and play around. It seems to me that if there is a fit, you can save
tons of time, which very well can pay off the upgrade cost.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: 527333 Reserch anything, nerw content up to date all sites 56
Next: Concatenate Mutiple Rows Data to Single RowColumn comma separated Data