mobile and gis dev notes

#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script
 
# Source function library.
 
. /etc/rc.d/init.d/functions
 
ORACLE_OWNER="oracle"
ORACLE_HOME="/opt/oracle/product/11.1.0/db_1"
 
case "$1" in
start)
echo -n $"Starting Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle DB:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop"
echo "OK"
;;
*)
echo $"Usage: $0 {start|stop}"
esac

#!/bin/bash
#
# oraemctl Starting and stopping Oracle Enterprise Manager Database Control.
# Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Enterprise Manager DB Control startup script
 
# Source function library.
 
. /etc/rc.d/init.d/functions
 
ORACLE_OWNER="oracle"
ORACLE_HOME="/opt/oracle/product/11.1.0/db_1"
 
case "$1" in
start)
echo -n $"Starting Oracle EM DB Console:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/emctl start dbconsole"
echo "OK"
;;
stop)
echo -n $"Stopping Oracle EM DB Console:"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/emctl stop dbconsole"
echo "OK"
;;
*)
echo $"Usage: $0 {start|stop}"
esac

SQL> SET autotrace ON
 
SQL> SELECT ename,dname
  2    FROM emp  INNER JOIN dept
  3      ON ( emp.deptno = dept.deptno )
  4  /
 
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING
 
14 rows selected.
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=14 Bytes=252)
   1    0   HASH JOIN (Cost=3 Card=14 Bytes=252)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=44)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=98)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1132  bytes sent via SQL*Net TO client
        503  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

CREATE USER "APPDEV" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
GRANT UNLIMITED TABLESPACE TO "APPDEV";
GRANT "CONNECT" TO "APPDEV";
GRANT "RESOURCE" TO "APPDEV";

SQLLDR username/password CONTROL=filename DATA=filename

LOAD DATA
CHARACTERSET UTF8
INFILE *
REPLACE INTO TABLE LOADER_TEST
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS (
USR_ID         INTEGER EXTERNAL,
USR_NAME       CHAR(50),
USR_LNK_NAME   CHAR(50),
USR_LNK_ORDER  INTEGER EXTERNAL
)

EXP scott/tiger@instance FILE=geo_entities.dmp TABLES=geo_entities
 
IMP sysman/password FILE=/path/TO/geo_entities.dmp FROMUSE=scott TOUSER=appdev IGNORE=Y INDEXES=N TABLES=geo_entities

ALTER TABLE table_name ADD (column_name  NUMBER);

ALTER TABLE geo_entities MODIFY meta_name varchar(255)

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name ADD CONSTRAINT table_name_col_pk PRIMARY KEY(column_name);

SELECT * FROM TABLE_NAME WHERE ROWNUM <= 100

CREATE SEQUENCE seq_table_name_pk INCREMENT BY 1 START WITH 10000;
 
INSERT INTO table_name (pid, en_name) 
SELECT seq_table_name_pk.NEXTVAL, en_name FROM other_table

SELECT concat('hello', 'rupert') FROM DUAL;
SELECT 'hello' || 'rupert' FROM DUAL;

UPDATE poi_temp pt
SET geom = (SELECT geom FROM poi_app WHERE poi_id = pt.poi_id)
WHERE EXISTS (SELECT 1 FROM poi_app WHERE poi_id = pt.poi_id)
 
UPDATE poi_app pa
SET (long_900913, lat_900913) = (SELECT pg.long_900913, pg.lat_900913 FROM poi_app_900913 pg WHERE pa.poi_id = pg.poi_id)
WHERE EXISTS (SELECT 1 FROM poi_app_900913 pg WHERE pa.poi_id = pg.poi_id)

SDO_GEOMETRY{
    SDO_GTYPE, - GeometryType: D00T
    D: Dimension (2: 2d, 3: 3d, 4:4d)
        T:GeometryType(
        0 - unknown
        1 - point
        2 - line
        3 - polygon
        4 - collection
        5 - multipoint
        6 - multiline
        7 - multipolygon
    )
    SDO_SRID,
    SDO_POINT, - NULL for line, polygon, etc.
    SDO_ELEM_INFO,
    SDO_ORDINATES
}

THE_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 3, 1), SDO_ORDINATE_ARRAY(116
.281578, 39.854501, 116.281491, 39.853828, 116.281236, 39.853181, 116.280821, ....39
.855174, 116.281578, 39.854501))
 
THE_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
 
SQL> SELECT g.the_geom.sdo_gtype FROM geo_entities g WHERE rownum <= 1;
 
THE_GEOM.SDO_GTYPE
------------------
3

INSERT INTO USER_SDO_GEOM_METADATA VALUES
(
    'table_name', -- TABLE_NAME
    'geom', -- COLUMN_NAME
    SDO_DIM_ARRAY -- DIMINFO attribute for storing dimension bounds, tolerance
    (
        SDO_DIM_ELEMENT
        (
            'LONGITUDE', -- DIMENSION NAME for first dimension
            -180, -- SDO_LB for the dimension
            180, -- SDO_UB for the dimension
            0.5 -- Tolerance of 0.5 meters
        ),
        SDO_DIM_ELEMENT
        (
            'LATITUDE', -- DIMENSION NAME for second dimension
            -90, -- SDO_LB for the dimension
            90, -- SDO_UB for the dimension
            0.5 -- Tolerance of 0.5 meters
        )
    ),
    4326 -- SRID value for specifying a geodetic coordinate system
);
 
CREATE INDEX idx_poi_app_geom ON poi_app(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
 
CREATE INDEX idx_poi_app_geom ON poi_app(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('LAYER_GTYPE=POINT')

SELECT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(GEOM, 0.000000005) FROM table_name

SELECT pc.postcode, au.authority_name, au.state, au.authority_type
FROM econ_authorities_valid_temp au, econ_postcodes pc
WHERE pc.POSTCODE = 3128
	  AND SDO_ANYINTERACT(
	  				  pc.GEOM,
	  				  au.GEOM
	  ) = 'TRUE

SELECT SDO_UTIL.GETVERTICES( GEOM ) FROM ECON_AUTHORITIES WHERE AUTHORITYID = 90009

SELECT 
  p1.poi_id, 
  p1.en_name, 
  p1.py_fullpoiadd, 
  p1.py_rdname, 
  p2.poi_id, 
  p2.en_name, 
  p2.py_fullpoiadd, 
  p2.py_rdname,
  SDO_GEOM.SDO_DISTANCE(p1.geom, p2.geom, 0.5) AS dist
FROM poi_test p1, poi_test p2
WHERE 
	Upper(p1.en_name) LIKE '%PARKSON%'
AND	Upper(p2.en_name) LIKE '%KFC%'
AND SDO_WITHIN_DISTANCE(p1.geom, p2.geom, 'DISTANCE=500 UNIT=METER' )='TRUE'
ORDER BY dist

SELECT
  p1.poi_id, 
  p1.en_name, 
  p1.py_fullpoiadd, 
  p1.py_rdname, 
  p2.poi_id, 
  p2.en_name, 
  p2.py_fullpoiadd, 
  p2.py_rdname,
  SDO_GEOM.SDO_DISTANCE(p1.geom, p2.geom, 0.5) AS dist
FROM poi_app p1, poi_app p2, geo_entities g
WHERE 
	Upper(p1.en_name) LIKE '%BAR BLU%'
AND	Upper(p2.en_name) LIKE '%KOKOMO%'
AND     Upper(g.meta_name) LIKE '%SANLITUN%'
AND SDO_WITHIN_DISTANCE(p1.geom, p2.geom, 'DISTANCE=500 UNIT=METER' )='TRUE'
AND SDO_WITHIN_DISTANCE(p1.geom, g.the_geom, 'DISTANCE=500 UNIT=METER' )='TRUE'
AND SDO_WITHIN_DISTANCE(p2.geom, g.the_geom, 'DISTANCE=500 UNIT=METER' )='TRUE'