SQL Query Examples

Basic query

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;

Show each point with a selected icon

SELECT lat, lng, name, id, info, icon 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)

Population map (show circles instead of pins) [REMOVED in v1.0]

SELECT lat, lng, name, id, '' AS info, '' AS icon,
       radius_km, '#cc0000' AS color, '0.05' as transparency
FROM mydata;

Note: the last two columns (color and transparency) are optional.

Flex fields - up to 10 extra data fields [REMOVED in v1.0]

SELECT lat, lng, name, id, '' AS info, '' AS icon, '' AS rad, '' AS col, '' as transp,
       col1, col2, ... col10
FROM mydata;

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

Labels [CHANGED in v1.0]

The first flex field is interpreted as a 1-character label on each pin if you set the plugin property Attribute1 to "Label".

SELECT lat, lng, name, id, '' AS info, '' AS icon, '' AS rad, '' AS col, '' as transp,
       lbl as label
FROM mydata;

Labels [v1.0 PLANNED]

A 1-character label on each pin.

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

Heatmap

This query structure is required if you use Visualisation Heatmap.

SELECT lat, lng, weight
FROM mydata;