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
      ,'' AS icon
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):

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:

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

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