SQL and Spatial Searches

It is possible to carry out spatial searches using the SPATIAL keyword, or between any two network layers or between a network layer and a background network layer or GIS layer as outlined below:

1. Choose the Search Type. (These are described in the table below.)

2. Select Layer Type:

   • Network layer: object layer in the current network
   • Background layer: network layer of network opened as a background network in the GeoPlan
   • GIS layer: GIS background layer displayed behind the network in the GeoPlan
   • Spatial rain layer: the rainfall radar cells used in the simulation, the results of which are displayed in the GeoPlan
3. Select the layer in the Layer box.

   When searching using a background network layer or GIS layer, the layer must be loaded in the GeoPlan View.

   When searching using a spatial rain layer, the results of the simulation must be loaded in the the GeoPlan View.

   The layer is then referred to in the query as spatial.

4. To include a field in the spatial layer search, select the field in the Field box, or enter directly into the edit box using the syntax shown in Figure 1 where the field name is included in the query - spatial.STRUCTURE
5. Use the spatial fields in the query in exactly the same way as network object fields

Click on the image below to show an example which uses GIS data to find damaged concrete pipes at least 300 mm in diameter that lie within 2 metres of a minor road.

Sequence of Operations

For example:

This query will: Search for any pipes crossing any object in the layer 'MMap1'. From the set of objects found in step 1, pipes with a width greater than 200 will be selected.

Spatial Search Type and Geometry

The combinations of geometry and search type listed in the table below are valid.

Note:

• For ArcGIS Engine and ArcGIS Desktop GIS layers, the "Inside" search type finds objects completely inside a GIS polygon.
• For MapXtreme GIS layers, network layers and background network layers, the "Inside" search type finds objects whose centre is inside a polygon boundary.
• The "Cross" search type includes all objects "Inside" a layer polygon and also those which intersect the boundary.
• The "Contains" search type looks for network polygons with layer objects completely inside the network polygon.

Spatial Search Type	Search Type Description	Geometry
Cross	Searches for network objects which intersect a layer line or polygon area.	InfoWorks ICM Geometry Layer Geometry Line Line Polygon Polygon Line Polygon
Inside	Searches for network objects inside a layer polygon.	InfoWorks ICM Geometry Layer Geometry Point Polygon Line Polygon Polygon Polygon
Contains	Searches for network polygons with layer objects inside the polygon.	InfoWorks ICM Geometry Layer Geometry Polygon Point Line Polygon
Distance	Searches for network objects within a search Distance of a layer line or polygon area.	InfoWorks ICM Geometry Layer Geometry Point Point Line Polygon Line Point Line Polygon Polygon Point Line Polygon
Nearest	Searches for nearest network object within a specified Distance. If there is more than one object within the specified distance, the first one found is returned as the result.	InfoWorks ICM Geometry Layer Geometry Point Point Line Polygon Line Point Line Polygon Polygon Point Line Polygon

Spatial searches using the SPATIAL keyword

The main advantage of using the SPATIAL keyword for spatial searches is that it allows a mixture of lines in the SQL block, some of which use the spatial search and some of which don’t.

The possible forms the clauses setting up the spatial search may take are:

• SPATIAL NONE - meaning do not use a spatial search i.e. the SQL clause has its normal meaning and acts on the data in the network without reference to any spatial search
• SPATIAL <geometry type> <layer type> <layer name>
• SPATIAL <geometry type> <layer type> <layer name> <distance>

The Geometry Types are:

• Cross
• Inside
• Contains
• Distance
• Nearest

Of these, Distance and Nearest require the distance field, the others must not have it.

The Distance field can be a numeric constant or a scalar variable.

The Layer Type can be:

• Network
• Background
• GIS
• Spatial rain

The layer name must be a string in quotes for GIS and a table name (not in quotes, optionally enclosed by [ ] if it is a single word without spaces, or compulsorily enclosed by [ ] if it isn’t) for the other types.

This works in the way as the other controls on the SQL dialog i.e. the options specified in the combo boxes and text box serves as the initial spatial configuration until it is changed by a SPATIAL statement.

Any SPATIAL clause only remains in force until the end of the block containing it i.e. if it is within an IF / ELSE / ELSEIF block then it ceases to have any effect at the end of that block, if it is in a WHILE block it ceases to have any effect at the end of that block. It remains in force in any new IF / WHILE blocks contained within that block.