SQL Server 2008 - Trả về các bản ghi theo thứ tự thời gian, với một yếu tố phân tách


2

Câu hỏi này ban đầu được đăng ở đây https://stackoverflow.com/questions/8492149/return-records-in-chronological-order-given-a-separation-factor và đăng lại trên diễn đàn này để đưa ra lời khuyên cho người dùng.

Tôi có một bảng lớn trong SQL Server 2008, nó chứa vị trí được báo cáo bởi các kỹ thuật viên mỗi phút.Tôi cần báo cáo trên bảng này nhưng để kiểm soát số lượng hồ sơ được hiển thị trong báo cáo, cả hai yếu tố phân tách thời gian và khoảng cách cần phải được tính đến.

Vì vậy, một truy vấn có thể trông giống như: "Trả lại tất cả các bản ghi với không ít hơn 5 phút và/hoặc 300 feet giữa chúng".

Phần thời gian đã xong, nhưng tôi đang gặp khó khăn với yếu tố khoảng cách.Tôi có vĩ độ và kinh độ cho từng điểm và tôi không gặp vấn đề gì nếu tôi cần bao gồm một UDT không gian SQL Server 2008 để giải quyết vấn đề.

Những điều tôi đã xem xét:

  • Mang các bản ghi theo hệ số thời gian và áp dụng giới hạn phân tách trong máy khách bằng cách tính khoảng cách giữa các điểm liền kề và loại bỏ các điểm nằm trong hệ số.(dễ nhất, nhưng nó phải là thứ tiêu tốn nhiều tài nguyên hơn).

  • Giữ bản ghi cuối cùng cho mỗi kỹ thuật viên trong bộ đệm, tính toán trước khoảng cách giữa bản ghi và tiền thân của nó và giải quyết các ràng buộc trong máy khách.(nên tiêu thụ ít tài nguyên hơn 1) do khoảng cách được tính toán trước, tuy nhiên và vì bảng là LỚN, nó sẽ tăng kích thước của tập dữ liệu, không chắc khoảng trống có đáng để tiết kiệm xử lý hay không).

  • Sử dụng các hàm không gian trong SQL Server 2008, nhưng thật lòng tôi đã đọc và tôi không thể tìm thấy bất cứ điều gì giúp tôi giải quyết loại yêu cầu này.Bất kỳ chuyên gia GIS ??

Tôi muốn sử dụng tùy chọn tốt nhất có thể (có thể không được liệt kê ở trên?) Và IMO sẽ là ứng dụng sử dụng các tính năng của SQL Server một cách hiệu quả nhất.

1

Trước SQL 2008, giải pháp phổ biến nhất là sử dụng UDF để tính khoảng cách vòng tròn lớn giữa hai điểm trên một mặt cầu.The Haversine formula có lẽ là phương pháp được sử dụng phổ biến nhất.

Tất nhiên Trái đất không thực sự là một hình cầu hoàn hảo, nhưng điều này được coi là "đủ tốt" cho hầu hết các mục đích sử dụng.

Trong SQL 2008, như bạn dự đoán, các phép tính như vậy được đơn giản hóa và được thực hiện chính xác hơn bằng cách giới thiệu các kiểu dữ liệu Địa lý và Hình học.Đây là một mẫu ngắn gọn về cách bạn có thể sử dụng chúng để đơn giản hóa các phép tính khoảng cách.

DECLARE @locations TABLE(locname VARCHAR(100), coord geography)
DECLARE @loc1 geography
DECLARE @loc2 geography


INSERT INTO @locations 
VALUES('HOME', geography::Point(-81.810194, 41.478156, 4326))  --Note: Lat, Long, SRID
                                   --The 4326 is the SRID (spatial reference id) used by SQL as 
                                   --a reference to the WGS 84 Standard. This is the same reference
                                   --used by the GPS system
INSERT INTO @locations 
VALUES('WORK', geography::Point(-81.687771, 41.498227, 4326))

SELECT * FROM @locations

SELECT @loc1 = coord FROM @locations WHERE locname = 'HOME'
SELECT @loc2 = coord FROM @locations WHERE locname = 'WORK'

SELECT @loc1.STDistance(@loc2) * 3.2808399 --STDistance is in meters so we multiply to convert to feet 

SELECT @loc1.STIntersects(@loc2.STBuffer(300 / 3.2808399)) as isWithin300Ft --This formula returns True when the point @loc1 intersects with the 300ft buffer zone around @loc2

SRID là chìa khóa cho độ chính xác được cải thiện.Đặc WGS 84 mà nó đề cập đến bao gồm một hệ tọa độ được tiêu chuẩn hóa và một ellipsoid tham chiếu.Nói cách khác, nó chiếm tính chất phi hình cầu của Trái đất, cho kết quả tốt hơn so với phép tính Great Circle hình cầu thuần túy.

Nếu độ chính xác của GIS là quan trọng đối với công việc của bạn, thì đây là cách đơn giản nhất để triển khai nó trong SQL 2008.

  0

Jonathan, trong khi điều này thật tuyệt khi tính khoảng cách giữa 2 điểm, vấn đề của tôi thực sự là làm thế nào để (_given một tập hợp các điểm_) chỉ trích xuất những điểm nằm trên ngưỡng khoảng cách nhất định hoặc (_maybe same_) coi chúng là một cụm sao cho X gần nhau dưới ngưỡng có thể được coi là một điểm duy nhất.

** Lý tưởng nhất ** Tôi muốn làm điều này với việc phải lặp đi lặp lại trong toàn bộ bộ sưu tập;và tôi đã hy vọng tìm thấy một chức năng không gian làm điều đó cho tôi.
14 dec. 112011-12-14 14:36:46

  0

Tìm điểm trong một ngưỡng khá đơn giản.Tôi đã thêm một dòng bổ sung vào khối mã để trình bày cách bạn có thể kiểm tra giao điểm của một điểm với bộ đệm xung quanh điểm khác.Nếu bạn cần thực hiện phân cụm lân cận gần nhất cũng khả thi, nhưng phức tạp hơn và tốn kém hơn về mặt tính toán. 14 dec. 112011-12-14 19:17:12

  0

Jonathan, xin lỗi, tôi không phải là người của hệ thống GIS nên tôi không quen thuộc 100% với các điều khoản. 14 dec. 112011-12-14 19:56:51

  0

Giả sử bạn bắt đầu đi bộ và bật một ứng dụng báo cáo vị trí của bạn mỗi phút.Giả sử ở tốc độ nhất định, bạn sẽ báo cáo vị trí của mình sau mỗi 100 feet, nhưng đối với loại phân tích tôi cần thực hiện vào cuối ngày, tôi chỉ quan tâm đến vị trí của bạn cứ sau 500 feet.Nếu bạn di chuyển với tốc độ không đổi (giả sử đơn giản như vậy) có nghĩa là tôi cần báo cáo cứ sau 4 điểm. 14 dec. 112011-12-14 20:02:28

  0

Vì vậy, toàn bộ thỏa thuận không phải là khoảng 2 điểm, mà là về cách trả về số lượng bản ghi đã đưa ra ngưỡng mà toàn bộ tập hợp phải lặp qua tất cả các bản ghi _at ít nhất không phải trong máy khách hoặc bằng cách sử dụng con trỏ SQL_. 14 dec. 112011-12-14 20:04:24

  0

Vì vậy, nếu tôi hiểu bạn đúng, bạn đang giải quyết vấn đề này: Lấy mẫu tất cả các điểm trong khoảng thời gian 5 phút nhất định.Nếu tất cả chúng tập hợp trong phạm vi 300ft của nhau, hãy coi một trong các điểm là pt đại diện và loại bỏ phần còn lại.Nếu không phải tất cả các cụm trong vòng 300ft, sau đó tạo 2 cụm và chọn 2 pts đại diện.Nếu điều đó không bao gồm tất cả các điểm, 3 cụm, v.v. tức là bạn đang cố gắng giảm số lượng nút trong đường dẫn trong khi vẫn có đường dẫn kết quả có phù hợp với đường dẫn thực không? 14 dec. 112011-12-14 20:36:59

  0

Chính xác, và khi tôi nghĩ rằng vấn đề lặp lại xuất hiện một cách tự nhiên và tôi đang cố gắng tránh nó với kích thước của tập dữ liệu.Tính năng này sẽ cung cấp cho người giám sát khả năng xem cách các kỹ thuật viên di chuyển trong những khoảng thời gian nhất định, với ngưỡng nhất định để anh ta có thể loại bỏ những điểm đó đủ gần (tiêu chí mở) như được coi là đứng yên. 14 dec. 112011-12-14 21:55:04