Monday, January 6, 2014

SpatiaLite query to join point features by distance

Sometimes I have two related point features in a SpatiaLite database, one feature layer (nodes) contains the identifier I need, and the other feature layer (stations) contains the attributes I want. The only relationship I can make use of is the spatial proximity between the two feature layers. The following screenshots illustrate the problem.
nodes and stations feature layers are within a certain distance from each other



The nodes table contain the required id and geometry. 

The stations table contains the desired attributes
The problem can be solved by formulating a simple spatial join query in SpatiaLite. The following shows how to do the job (using QGIS to submit the query to the SpatiaLite database).

  1. In QGIS, select Database | DB Manager | DB Manager.

    The DB Manager appears.
  2. In the tree view, select the SpatiaLite database containing the two feature layers, e.g. vancity.sqlite.
  3. Click SQL Window.

    The SQL  Window appears.
  4. Type in the following SQL query.

    select b.id as id,
    a.code,
    a.name,
    y(transform(a.geometry, 4326)) as lat,
    x(transform(a.geometry,4326)) as lng
    from stations as a
    join nodes as b
    on (ST_DISTANCE(a.Geometry,b.Geometry) < 10)


    Note: it may be necessary to adjust the ST_DISTANCE function's maximum distance between both features so that it doesn't leave out or include features.
  5. Click Execute.

    The join results appear.
  6. Optional. If you want to display the results on the map, then the query should include the desired geometry field as shown below.

    select b.id as id, 
    a.code, 
    a.name, 
    y(transform(a.geometry, 4326)) as lat,
    x(transform(a.geometry,4326)) as lng,b.geometry
    from stations as 
    join nodes as 
    on (ST_DISTANCE(a.Geometry,b.Geometry) < 10)



    Toggle on Load as new layer. Choose the primary key e.g. id in the Column with unique integer values field. Choose the geometry field in the Geometry column field. Type in a Layer name (prefix) e.g. tmp. Click Load now!.

    The results layer is loaded in the map.

No comments:

Related Posts with Thumbnails