Android and HttpRequest I

public String getHttpRequest(String urlstr) {
// TODO Auto-generated constructor stub
String result="";

try
{
HttpClient hc = new DefaultHttpClient();
HttpEntity entity = null;
HttpGet httpget = new HttpGet(urlstr);
HttpResponse rp = hc.execute(httpget);

if(rp.getStatusLine().getStatusCode() == HttpStatus.SC_OK)
{
entity = rp.getEntity();
result = EntityUtils.toString(entity);
}
else
{
result="err";
}
hc.getConnectionManager().shutdown();
}
catch (Exception se)
{
result=se.getMessage();
}
return result;
}

Android and SQLite II

private String mDBPath = android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+ "/";
private String mDBFilename = "";
SQLiteDatabase database;
String mDirectory ="myDB";

//設定資料庫路徑
public void setDatabase(String Directory,String DBFile ) {
// TODO Auto-generated constructor stub
mDirectory = Directory;
mDBFilename = DBFile;
}

//將資料庫傳送到記憶卡
public SQLiteDatabase OpenDB(Context con,int RFile)
{
try
{
String DBFile = mDBPath + mDirectory+"/" + mDBFilename;
File dr = new File(mDBPath + mDirectory);
if(!dr.exists()) dr.mkdir();

if(!(new File(DBFile)).exists())
{
InputStream is = con.getResources().openRawResource(RFile);
FileOutputStream fos = new FileOutputStream(DBFile);
byte[] b = new byte[8192];
int count=0;

while((count=is.read(b))>0)
{
fos.write(b,0, count);
}
fos.close();
is.close();
}

database = SQLiteDatabase.openOrCreateDatabase(DBFile, null);
return database;
}
catch(Exception se)
{
throw new RuntimeException(se.getMessage());
}
}
//讀取資料庫內容
// [HASPMap[String,String]] > < 不能用 ][取代
public ArrayList [HASPMap[String,String]] getDBTable(String table, String[] Fields)
{
ArrayList[HASPMap[String,String]] dbdata = new ArrayList[HASPMap[String,String]]();
Cursor c= database.query(table, Fields, null, null, null, null, null);
for(int i=0;i {
c.moveToPosition(i);
[HASPMap[String,String]] d = new [HASPMap[String,String]] ();
for(int j=0; j {
d.put(Fields[j], c.getString(j));
}
dbdata.add(d);
}
return dbdata;
}

Android and SQLite I

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.text.Editable;
import android.text.TextWatcher;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AutoCompleteTextView;
import android.widget.Button;
import android.widget.TextView;

public class main extends Activity implements OnClickListener, TextWatcher {

private final String DATABASE_PATH = android.os.Environment.getExternalStorageDirectory().getAbsolutePath()+ "/myDB";
private final String DATABASE_FILENAME = "etraffic.sqlite";
SQLiteDatabase database;
Button btnSelectWord;
AutoCompleteTextView actvWord;

@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);

database = openDatabase();
btnSelectWord = (Button) findViewById(R.id.btnSelectWord);
actvWord = (AutoCompleteTextView) findViewById(R.id.actvWord);
btnSelectWord.setOnClickListener(this);
actvWord.addTextChangedListener(this);

}

public void onClick(View view)
{
String sql = "SELECT * FROM FreewayPoint_CCTV where cctv_id=?";
Cursor cursor = database.rawQuery(sql, new String[]{
actvWord.getText().toString()
});

String result = "找不到";

if(cursor.getCount()>0)
{
cursor.moveToFirst();
result=cursor.getString(cursor.getColumnIndex("roadsection"));
}

myClass cl =new myClass();
cl.Alert(result, "Title", this);
}

private SQLiteDatabase openDatabase() {
try {

String databaseFilename = DATABASE_PATH + "/" + DATABASE_FILENAME;
File dir = new File(DATABASE_PATH);
if (!dir.exists())
dir.mkdir();

if (!(new File(databaseFilename)).exists()) {

InputStream is = getResources().openRawResource(
R.raw.etraffic);
FileOutputStream fos = new FileOutputStream(databaseFilename);
byte[] buffer = new byte[8192];
int count = 0;

while ((count = is.read(buffer)) > 0) {
fos.write(buffer, 0, count);
}

fos.close();
is.close();
}

SQLiteDatabase database = SQLiteDatabase.openOrCreateDatabase(
databaseFilename, null);
return database;
} catch (Exception e) {
}
return null;
}

@Override
public void afterTextChanged(Editable s) {
// TODO Auto-generated method stub

}

@Override
public void beforeTextChanged(CharSequence s, int start, int count,
int after) {
// TODO Auto-generated method stub

}

@Override
public void onTextChanged(CharSequence s, int start, int before, int count) {
// TODO Auto-generated method stub

}

PostGIS and PostgreSql 功能說明

環域
select * from "GISpoint" where st_contains (buffer(st_geomfromtext('Point(2 2)'),1.0),"gisPoints");
select * from "GISpoint" where st_contains (buffer(st_geomfromtext('Point(2 2)'),1.1),"gisPoints");
select * from "GISpoint" where st_within "gisPoints“,buffer(st_geomfromtext('Point(2 2)'),1.1))

交集
select "CountyNameCht","TownNameCht","CountyNameEng","TownNameEng","CountyId","TownId"
from "CityTown" where st_intersects("geom",buffer(st_geomfromtext('Point({0} {1})'),{2})

吸附
select astext("geom"),line_interpolate_point("geom", line_locate_point("geom", PointFromText('Point(322655 2778900)')))
from "RoadSpeed"
order by Distance("geom",PointFromText('Point(322655 2778900)')) limit 1

坐標轉換
spatial_ref_sys
select * from "spatial_ref_sys"
select * from "spatial_ref_sys" where "srid"=4326 (WGS84)
select * from "spatial_ref_sys" where "srid"=3828 (TWD67 -121)
select * from "spatial_ref_sys" where "srid"=3826 (TWD97 -121)

select "Id", "TownId","TownCode","CountyNameCht", "TownNameCht","CountyNameEng", "TownNameEng", "CountyId",
st_transform(setsrid(geom,4326),3826) as geom
into "CityTown_67“ from "CityTown“
CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2)

由空間資訊轉換坐標字串
select st_astext(geom) from \"BusPathLine\"

算距離
st_distance(geom,st_geomfromtext('Point(121.12341 24.23432)')