mobile and gis dev notes

postgres=# \l
List of databases
Name       |  Owner   | Encoding
------------------+----------+----------
postgis          | postgres | UTF8
postgres         | postgres | UTF8
template0        | postgres | UTF8
template1        | postgres | UTF8
template_postgis | postgres | UTF8
(5 rows)

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'

-- Simple Query
SELECT SCORE(1) AS myscore, en_name, en_visname, py_name FROM poi_app WHERE CONTAINS(ft_meta_en_name, 'grammy center', 1) > 0 ORDER BY myscore DESC;
 
SELECT SCORE(1) AS myscore, en_name, en_visname, py_name FROM poi_app WHERE CONTAINS(ft_meta_en_name, 'cybersoft', 1) > 0;
 
-- Query Rewrite
SELECT en_name, en_visname, py_name
FROM poi_app
WHERE CONTAINS (ft_meta_en_name,
'<query>
<textquery lang="ENGLISH" grammar="CONTEXT"> international hotel boya
<progression>
<seq><rewrite>transform((TOKENS, "{", "}", "AND"))</rewrite></seq>
<seq><rewrite>transform((TOKENS, "{", "}", "ACCUM"))</rewrite></seq>
</progression>
</textquery>
<score datatype="INTEGER" algorithm="COUNT"/>
</query>')>0;
 
-- Query 'About'
SELECT en_name, en_visname, py_name, score(1)
FROM poi_app
WHERE CONTAINS(ft_meta_en_name, 'about(italian restaurants)', 1) > 0
ORDER BY SCORE(1) DESC;
 
-- Query logical
SELECT en_name, en_visname, py_name, score(1)
FROM poi_app
WHERE CONTAINS(ft_meta_en_name, 'beijing, international, hotel', 1) > 0
ORDER BY SCORE(1) DESC;