SQL Query Examples

Source Location

May be left blank ("- Select -") or set to Local Database.

If set to Local Database, the Source Type may be set to Table / View, SQL Query, or PL/SQL Function Body returning SQL Query.

Table / View

Set the source table or view to get the data from. The plugin will get the data from the source according to column name(s) expected by the selected Visualisation; if the table or view does not have columns with the correct names, the plugin will fail to load the data. Any other columns will simply be ignored.

• Pin visualisations (including Route Map, Marker Clustering, Spiderfier): lat, lng, name, id

• Heatmap visualisation: lat, lng, weight

• GeoJson visualisation: geojson

SQL Query

The plugin uses the positional order of the columns in your query. See below for examples. Any additional columns after the ones it interprets will be ignored. The columns can be named however you like.

PL/SQL Function Body returning SQL Query

The PL/SQL function must return a SQL query string that conforms to the same rules as per SQL Query. See below for examples.

SQL Query Examples

• Basic query

  The query must have at least four columns - the first two must be latitude and longitude values, and the third and fourth columns are the name and id for the pin. The id may be any data type that can be converted to a string, and for best results should be unique within the dataset.

  Applicable to all visualisations EXCEPT for Heatmap and GeoJson.

  SELECT lat, lng, name, id FROM mydata;
  

• Add some popup info (HTML content allowed) when the user clicks a point

  SELECT lat, lng, name, id, info FROM mydata;
  

SECURITY WARNING: the info column (column #5), if supplied, is used to supply raw HTML to render within the info window shown when a marker is clicked. If this is supplied with data entered by an end user, this may represent a XSS attack vulnerability; therefore any such data must be sanitised before allowing it to be loaded into the map.

• Show each point with a selected icon

  SELECT lat, lng, name, id, info, icon FROM mydata;
  

• Labels

  A 1-character label on each pin.

  SELECT lat, lng, name, id, info, '' as icon, lbl as label
  FROM mydata;
  

• Flex fields - up to 10 extra data fields

  SELECT lat, lng, name, id, '' AS info, '' AS icon, '' AS label
         col1, col2, ... col10
  FROM mydata;
  

  Note: The extra fields are available from Dynamic Actions via this.data.attr01, this.data.attr02, etc.

  By default, any special characters (such as < and >) will be escaped prior to loading into the map. If your application requires the original data to be preserved, set the plugin attribute Escape special characters to No.

• Get only the data within a certain distance from a chosen point

  select t.lat as lat
        ,t.lng as lng
        ,t.name
        ,t.id as id
        ,t.info
  from   mytable t
  where  t.lat is not null
  and    t.lng is not null
  and    (:p1_latlng is null
          or :p1_radius is null
          or sdo_geom.sdo_distance
               (geom1 => sdo_geometry
                   (sdo_gtype     => 2001 /* 2-dimensional point */
                   ,sdo_srid      => 8307 /* Longitude / Latitude (WGS 84) */
                   ,sdo_point     => sdo_point_type(t.lng, t.lat, null)
                   ,sdo_elem_info => null
                   ,sdo_ordinates => null)
               ,geom2 => sdo_geometry
                   (sdo_gtype     => 2001 /* 2-dimensional point */
                   ,sdo_srid      => 8307 /* Longitude / Latitude (WGS 84) */
                   ,sdo_point     => sdo_point_type
                        (to_number(substr(:p1_latlng,instr(:p1_latlng,',')+1))
                        ,to_number(substr(:p1_latlng,1,instr(:p1_latlng,',')-1)), null)
                   ,sdo_elem_info => null
                   ,sdo_ordinates => null)
               ,tol   => 0.0001 /*metres*/
               ,unit  => 'unit=KM') < :p1_radius)
  

• Heatmap

  This query structure is required if you use Visualisation Heatmap.

  SELECT lat, lng, weight
  FROM mydata;
  

• geoJson [v1.3]

  This query structure is required if you use Visualisation geoJson.

  Features may be loaded via a SQL query that returns them in geoJson format.

  The query may simply return a VARCHAR2 or CLOB column containing one or more geoJson documents:

  select geojson
  from   mytable
  

  Additional fields may optionally be used to add the name, id properties, and up to 10 flex fields:

  select geojson
        ,name
        ,id
        ,col1, col2, ... col10
  from   mytable
  

  This example uses a data source that only provides the geometry for the features; the query wraps the geometry data with a geoJson document:

  select json_object(
           'type' is 'Feature',
           'geometry' is c.geometry
           returning clob
         ) as geojson
        ,country as name
  from   country_borders
  where  geometry is not null
  

  This example embeds properties in the geoJson document; this is preferred because any arbitrary properties may be added without using "flex" fields:

  select json_object(
           'type' is 'Feature',
           'geometry' is c.geometry,
           'properties' is json_object(
               'id' is c.id,
               'name' is c.country,
               'population' is c.pop
           )
           returning clob
       ) as geojson
  from   country_borders c
  where  c.geometry is not null