SQL Query Examples

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

Optional. You can set the Source to "none" if you just need to render a map without any data.

• 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.

  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.

• Heatmap

  This query structure is required if you use Visualisation Heatmap.

  SELECT lat, lng, weight
  FROM mydata;
  

• 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)
  

• 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:

  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