prob with query (deleting a row into result table)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

prob with query (deleting a row into result table)

Author

Topic

branding
Starting Member

3 Posts

Posted - 2011-03-25 : 13:49:11

Hi everyone I'm struggling with this query:

DECLARE @pathId uniqueidentifier
DECLARE @radius int

SET @pathId = '6D345B50-966C-4AED-9D6E-909B9CAD43C9'
SET @radius = 9000

DECLARE @point geography
SET @point = 0xE6100000010CFB3A70CE88AE4640000DE02D90E02440

SELECT g.PathId, MIN(g.[LatLng].STDistance(@point)) dist, g.Step
FROM [dbo].[GeoPathPoints] g
WHERE g.[PathId] <> @pathId
AND g.[Step] <> 9999
AND g.[LatLng].STDistance(@point) <= @radius
GROUP BY g.PathId, g.step

Result is these 5 rows:
E596CE8E-4FF8-4CDC-B1D9-868C91366177 4683.83503254203 0
D68DD7B2-9109-4871-813E-8AC9B3B4873C 1770.97441688209 0
D62E8544-539D-4D08-8BEE-027EB59E2FAD 6592.29834109322 2
8BCD3E0E-7611-444F-BE39-EB82E3975667 4284.66893134634 3
8BCD3E0E-7611-444F-BE39-EB82E3975667 8881.56245257416 4

Everything is fine but I dont want to have duplicated PathId (first column), it should only show the 4th row because the distance column (second one) is the little one for that PathId
Hope it makes sense for you, I'm struggling to this with a subquery but no joy as far as it goes...

Create table is the following:
CREATE TABLE [dbo].[GeoPathPoints](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PathId] [uniqueidentifier] NOT NULL,
[LatLng] [geography] NULL,
[Step] [int] NULL

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-25 : 14:15:02

can you please provide latlng values to populate data?

If you don't have the passion to help people, you have no passion

branding
Starting Member

3 Posts

Posted - 2011-03-25 : 14:25:45

There you go

Id PathId LatLng Step
24 E596CE8E-4FF8-4CDC-B1D9-868C91366177 0xE6100000010CF6622827DAB14640C00968226CC82440 0
25 E596CE8E-4FF8-4CDC-B1D9-868C91366177 0xE6100000010CC1A8A44E40C346408010018750652440 9999
30 E0343150-4E62-4331-B62F-A8A937312839 0xE6100000010CB0FECF61BE94464080EB34D252A92540 0
31 E0343150-4E62-4331-B62F-A8A937312839 0xE6100000010C3659A31EA2BD464000897B2C7D782440 9999
48 D62E8544-539D-4D08-8BEE-027EB59E2FAD 0xE6100000010C4B02D4D4B2A1464040384A5E9DC32540 0
49 D62E8544-539D-4D08-8BEE-027EB59E2FAD 0xE6100000010CE010AAD4ECBD464080183E22A6842440 9999
53 D62E8544-539D-4D08-8BEE-027EB59E2FAD 0xE6100000010CBF034D9539B44640803C1C1318FD2440 2
54 8BCD3E0E-7611-444F-BE39-EB82E3975667 0xE6100000010C1D554D107593464080D87C5C1B9A2540 0
55 8BCD3E0E-7611-444F-BE39-EB82E3975667 0xE6100000010C14799274CDBC4640C0D556EC2F7B2440 9999
107 8BCD3E0E-7611-444F-BE39-EB82E3975667 0xE6100000010C50447529649346404004053983622540 1
108 8BCD3E0E-7611-444F-BE39-EB82E3975667 0xE6100000010C34396BA63190464080826F9A3E0B2540 2
109 8BCD3E0E-7611-444F-BE39-EB82E3975667 0xE6100000010C72D7B738CFA946400098480E7DD82440 3
110 8BCD3E0E-7611-444F-BE39-EB82E3975667 0xE6100000010C628ACD226FB4464080BCA71721B12440 4

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-25 : 14:46:43

is this what you are looking for ?




       
       
DECLARE @pathId uniqueidentifier
DECLARE @radius int

SET @pathId = '6D345B50-966C-4AED-9D6E-909B9CAD43C9'
SET @radius = 9000


DECLARE @point geography
SET @point = 0xE6100000010CFB3A70CE88AE4640000DE02D90E02440

DECLARE @GeoPathPoints TABLE(
[Id] [int] IDENTITY(1,1) NOT NULL,
[PathId] [uniqueidentifier] NOT NULL,
[LatLng] [geography] NULL,
[Step] [int] NULL)

INSERT INTO @GeoPathPoints
        ( PathId, LatLng, Step )

SELECT 'E596CE8E-4FF8-4CDC-B1D9-868C91366177',
        0xE6100000010CF6622827DAB14640C00968226CC82440 ,
        0
UNION        
SELECT 'E596CE8E-4FF8-4CDC-B1D9-868C91366177',
       0xE6100000010CC1A8A44E40C346408010018750652440,
       9999
UNION
SELECT 'E0343150-4E62-4331-B62F-A8A937312839', 0xE6100000010CB0FECF61BE94464080EB34D252A92540, 0
UNION
SELECT 'E0343150-4E62-4331-B62F-A8A937312839',0xE6100000010C3659A31EA2BD464000897B2C7D782440, 9999
UNION
SELECT 'D62E8544-539D-4D08-8BEE-027EB59E2FAD', 0xE6100000010C4B02D4D4B2A1464040384A5E9DC32540, 0
UNION
SELECT 'D62E8544-539D-4D08-8BEE-027EB59E2FAD', 0xE6100000010CE010AAD4ECBD464080183E22A6842440, 9999
UNION
SELECT 'D62E8544-539D-4D08-8BEE-027EB59E2FAD', 0xE6100000010CBF034D9539B44640803C1C1318FD2440, 2
UNION
SELECT '8BCD3E0E-7611-444F-BE39-EB82E3975667', 0xE6100000010C1D554D107593464080D87C5C1B9A2540, 0
UNION
SELECT '8BCD3E0E-7611-444F-BE39-EB82E3975667', 0xE6100000010C14799274CDBC4640C0D556EC2F7B2440, 9999
UNION
SELECT '8BCD3E0E-7611-444F-BE39-EB82E3975667', 0xE6100000010C50447529649346404004053983622540, 1
UNION
SELECT '8BCD3E0E-7611-444F-BE39-EB82E3975667', 0xE6100000010C34396BA63190464080826F9A3E0B2540, 2
UNION
SELECT '8BCD3E0E-7611-444F-BE39-EB82E3975667', 0xE6100000010C72D7B738CFA946400098480E7DD82440, 3
UNION
SELECT '8BCD3E0E-7611-444F-BE39-EB82E3975667', 0xE6100000010C628ACD226FB4464080BCA71721B12440, 4                    

SELECT * FROM 
(
SELECT g.PathId,g.Step, g.[LatLng].STDistance(@point) AS dist,
ROW_NUMBER() OVER (PARTITION BY g.PathId ORDER BY g.[LatLng].STDistance(@point) ASC) AS yaay
 FROM @GeoPathPoints g
WHERE g.[PathId] <> @pathId
  AND g.[Step] <> 9999
  AND g.[LatLng].STDistance(@point) <= @radius)
  a
 WHERE yaay = 1

If you don't have the passion to help people, you have no passion

branding
Starting Member

3 Posts

Posted - 2011-03-25 : 17:00:54

It seems to work just great!
I'll do a lil bit of test tomorrow morning again - thx a lot for you help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources