SQL Query to Finding Nearest Neighbours (Spatial Data)

News

Navigation

SQL Query to Finding Nearest Neighbours (Spatial Data)

SQL Server 2008 menyediakan beberapa fungsi yang mendukung tipe data Basis data Spasial (geometry / geography). Untuk menghitung jarak antara dua instances anda dapat menggunakan fungsi STDistance() . Contoh penerapannya seperti berikut :

Query Berikut ini mencari 10 titik terdekat dengan titik POINT (210000 890000) di tabel Customer

        1: DECLARE @Point geometry

       2: SET @Point = geometry::STPointFromText('POINT (210000 890000)', 26986)

       3: SELECT TOP 10

       4:   Nama

       5:   Alamat,  

       6:   Lokasi.STDistance(@Point) AS Jarak

       7: FROM

       8:   Customer

       9: ORDER BY

      10:   Lokasi.STDistance(@Point)  ASC

Query diatas benar dan dapat dijalankan namun ternyata kurang efisien karna akan menscan semua row kemudian baru diurutkan (memerlukan waktu). Solusi yang dapat digunakan untuk memberikan efisiensi maka anda dapat menggunakan tambahan fungsi STBuffer() dan Filter() seperti berikut :

       1: DECLARE @Point geometry

       2: SET @Point = geometry::STPointFromText('POINT (210000 890000)', 26986)

       3:  

       4: DECLARE @Cari geometry

       5: SET @Cari = @Point.STBuffer(500)

       6:  

       7: SELECT TOP 10

       8:   Nama

       9:   Alamat,  

      10:   Lokasi.STDistance(@Point) AS Jarak

      11: FROM

      12:   Customer

      13: WHERE

      14:   Lokasi.Filter(@Cari) = 1

Query tersebut akan mencari 10 titik terdekat namun sudah difilter terlebih dahulu yang berada dalam radius 500 meter.

Implementasi di Web Application (menggunakan control ASPMap)

1. Download dan Attach Database GIS yang dapat diunduh disini.

2. Buatlah Stored Procedure seperti berikut ini

       1: Create procedure [dbo].getPoint(@x float , @y float,@tipe varchar(30))  

       2: as

       3: Begin

       4: DECLARE @titik geometry

       5: SET @titik =  geometry::Point(@x,@y,0)

       6: DECLARE @cari geometry

       7: SET @cari = @titik.STBuffer(0.005)

       8:  

       9: SELECT [name]

      10:       ,[type]

      11:       ,[geom].STAsText(),

      12:       geom.STDistance(@titik) AS Distance

      13: FROM

      14:   points 

      15: WHERE

      16: geom.Filter(@cari) = 1

      17: and [type]=@tipe

      18: End

3. Buka Visual Studio anda buat website baru, beri nama dengan GazaMap kemudian tambahkan ASPMap control ke halaman default.aspx seperti berikut :

4. Tambahkan Code seperti berikut.

       1: Imports System

       2: Imports System.Data

       3: Imports System.Data.SqlClient

       4: Imports AspMap

       5: Imports AspMap.Web

       6: Partial Class _Default

       7:     Inherits System.Web.UI.Page

       8:     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

       9:         LayerFromSHP()

        If IsPostBack = False Then

            LoadBangunan()

        End If

    End Sub

    Private Sub LayerFromSHP()

        Dim layer As AspMap.Layer

        Map1.MapUnit = MeasureUnit.Meter

        Dim MapDir As String = MapPath("MAP/")

        layer = Map1.AddLayer(MapDir & "roads.shp")

        layer.LabelField = "name"

        layer.ShowLabels = True

        layer.LabelFont.Color = Drawing.Color.Indigo

        layer.LabelFont.Name = "Verdana"

        layer.LabelFont.Size = 12

        layer.LabelFont.Bold = True

        layer.LabelStyle = LabelStyle.Default

    End Sub

    Private Sub LoadLayerFromDB(ByVal x As Double, ByVal y As Double, ByVal strTipe As String)

        Dim cn As New SqlConnection("Data Source=PDI-APP-LAPTOP\MOBILE;Initial Catalog=GIS;Trusted_Connection=yes;")

        cn.Open()

        Dim cmd As New SqlCommand("getPoint " & x & "," & y & ",'" & strTipe & "'", cn)

        Dim rdr As SqlDataReader

  

        Map1.Markers.Clear()

        rdr = cmd.ExecuteReader()

        While rdr.Read

            Dim strTemp() As String

            Dim strPoint As String

            Dim intPosisi As Byte

            intPosisi = InStr(Trim("" & rdr(2)), "(", CompareMethod.Text)

            strPoint = Right(Trim("" & rdr(2)), Len(Trim("" & rdr(2))) - intPosisi)

            strPoint = Left(strPoint, Len(strPoint) - 1)

            strTemp = Split(strPoint, " ")

  

            Dim marker As Marker = New Marker(New AspMap.Point(Val(strTemp(0)), Val(strTemp(1))), Trim("" & rdr(0)), "Nama Bangunan : " & Trim("" & rdr(0)) & "<BR>" & "Jarak dari titik : " & Val("" & rdr(3)).ToString)

            Map1.Markers.Add(marker)

  

        End While

        rdr.Close()

        cmd = Nothing

        cn.Close()

        cn = Nothing

    End Sub

    Sub LoadBangunan()

        Dim cn As New SqlConnection("Data Source=PDI-APP-LAPTOP\MOBILE;Initial Catalog=GIS;Trusted_Connection=yes;")

        cn.Open()

        Dim cmd As New SqlCommand("SELECT DISTINCT [type]   FROM  [points]", cn)

        Dim rdr As SqlDataReader

  

        ddlTipe.Items.Clear()

        rdr = cmd.ExecuteReader()

        While rdr.Read

            ddlTipe.Items.Add(Trim("" & rdr(0)))

        End While

        rdr.Close()

        cmd = Nothing

        cn.Close()

        cn = Nothing

    End Sub

    Protected Sub Map1_InfoTool(ByVal sender As Object, ByVal e As AspMap.Web.InfoToolEventArgs) Handles Map1.InfoTool

        Dim Titik As New AspMap.Point

        Titik = e.InfoPoint

        LoadLayerFromDB(Titik.X, Titik.Y, ddlTipe.SelectedValue)

    End Sub

End Class

5. Jalankan kemudian pilih tipe bangunannya, klik icon InfoTool klik peta di sembarang Peta “Gaza” maka akan tampil bangunan terdekat seperti berikut.