A Spatial SQL – Selecting Objects from another Table based on their Proximity to a Selection

I’ll use the following example to demonstrate how, with a couple of simple SQL Queries, it is possible to select objects from another table based on their proximity to a current selection of objects.

In the following distribution network there are manholes that are not quite aligned to a fitting. The idea is to find all those that are say a distance of 0.1 meters from fittings. We can see from the image below that a couple of manholes (circled) are much further than 0.1m.

Unaligned Manholes.

Unaligned Manholes.

Example Distance Of Manholes.

Example Distance Of Manholes.

We begin by selecting the fittings. A way to achieve this is to use an SQL.

Select Fittings.

Select Fittings.

Fittings Selected.

Fittings Selected.

Now onto the important SQLs, of which you’ll need two, to do the selecting of the stray manholes. You’ll also need a spare user field, in this example I’ll simply use user_number_1.

The first SQL will set user_number_1 to 1 for the selection and 0 otherwise for required Fittings. In this instance the since all fittings are selected then all objects’ user_number_1 is updated. The user_number_1 = 0 is for when only a selection of objects (fittings) is required to make the spatial search from.

1st SQL.

1st SQL.

SET user_number_1=0;
UPDATE SELECTED SET user_number_1 = 1;

The second uses the spatial functionality with a network layer e.g.

2nd SQL.

2nd SQL.

spatial.user_number_1 = 1

Execution Of SQL.

Execution Of SQL.

The SQL selects the Manholes with a distance of 0.1m, and then an inversion of the selection leaves you with the manholes further than 0.1m.

Share this post!

    About Idris Nujjoo

    Idris is the Product Sector Leader for InfoNet and InfoMaster and has been working for Innovyze (and its earlier incarnations) since 2001 and specifically working on InfoNet and Asset Management for the past decade.

    This entry was posted in InfoNet, InfoWorks ICM. Bookmark the permalink.