Nasrul Hazim Bin Mohamad

RDBMS Data Limit

Apr
01

== Microsoft SQL Server ==
Max DB Size: 524,272 TB (32 767 files * 16 TB max file size)
Max Table Size: 524,272 TB
Max Row Size: 8,060 bytes (Unlimited)
Max Columns Per Row: 30,000
Max Blob / Clob Size: 2 GB
Max Char Size: 2 GB
Max Number Size: 126 bits
Min Date Value: 0001
Max Date Value: 9999
Max Column Name Size: 128

== MySQL ==
Max DB Size: Unlimited
Max Table Size: MyISAM storage limits: 256 TB; Innodb storage limits: 64 TB
Max Row Size: 64 KB
Max Columns Per Row: 4,096
Max Blob / Clob Size: 4 GB (longtext, longblob)
Max Char Size: 64 KB (text)
Max Number Size: 64 bits
Min Date Value: 1000
Max Date Value: 9999
Max Column Name Size: 64

== PostgreSQL ==
Max DB Size: Unlimited
Max Table Size: 32TB
Max Row Size: 1.6TB
Max Columns Per Row: 250 – 1600 ( depending on type)
Max Blob / Clob Size:
– 1 GB (text, bytea)[http://grokbase.com/t/postgresql/pgsql-general/12bsww982c/large-insert-leads-to-invalid-memory-alloc] – stored inline or
– 4 TB (stored in pg_largeobject)[http://www.postgresql.org/docs/9.3/static/lo-intro.html]
Max Char Size: 1GB
Max Number Size: Unlimited
Min Date Value: −4,713
Max Date Value: 5,874,897
Max Column Name Size: 63

== Oracle ==
Max DB Size: Unlimited (4 GB * block size per tablespace)
Max Table Size: 4 GB * block size (with BIGFILE tablespace)
Max Row Size: 8 KB
Max Columns Per Row: 1,000
Max Blob / Clob Size: 128 TB
Max Char Size: 32,767 B
Max Number Size: 126 bits
Min Date Value: −4712
Max Date Value: 9999
Max Column Name Size: 30

== SQLite ==
Max DB Size: 128 TB (231 pages * 64 KB max page size)
Max Table Size: Limited by file size
Max Row Size: Limited by file size
Max Columns Per Row: 32,767
Max Blob / Clob Size: 2 GB
Max Char Size: 2 GB
Max Number Size: 64 bits
Min Date Value: No DATE type
Max Date Value: No DATE type
Max Column Name Size: Unlimited

Reference: http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

PostgreSQL: Export to CSV

Mar
11
COPY TABLE_NAME
TO FILE_PATH
WITH DELIMITER ','
CSV HEADER

Please ensure the file path is writeable

PostgreSQL – Extract Digit Only from Column with Text Value

Dec
08
select substring(mycolumn from 'd+') from mytable

You may want to copy digit values from one column to another column which it’s data type is integer. Below is the sample of usage:

update dev_01 set lot_number = substring(lot from 'd+')::int;

PostGIS Enabled Schema with Different Schema Name

Nov
27

PLEASE MAKE SURE YOU BACKUP YOUR PUBLIC SCHEMA !

Steps:

  1. Open up pgAdmin III and connect to PostgreSQL.
  2. Select desired database and click on schemas
  3. Open up SQL Editor
  4. Open & execute public_schema.sql
  5. Open & execute public_data.sql
  6. Open rename_schema.sql and replace `new_schema_name` with the name you want, then execute the query

Files can be found here: PostGIS Enabled Schema

PostgreSQL – Import data from CSV

Mar
10

You CSV file similar to the following:

"a",1
"b",2
"c",3
"d",4

The SQL:

COPY table_name(col1,col2) FROM 'C:UsersPublicDocumentsCSVdata.csv' WITH DELIMITER AS ',' CSV QUOTE AS '"';

References:

  1. http://forums.enterprisedb.com/posts/list/2819.page
  2. http://dba.stackexchange.com/questions/18821/how-to-import-a-csv-file-into-a-postgresql-database-using-copy
  3. http://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgres-table
  4. http://www.postgresql.org/docs/9.1/static/sql-copy.html
  5. http://stackoverflow.com/questions/10079682/copy-function-in-postgresql

PostGIS – Get Table SRID

Mar
08

Please check geometry_columns table if the result return `-1`. You may change the srid value if you knew the value of the target table. Value can be 900913, 4326, 3857, etc.

select Find_SRID('public', 'table', 'geomtry_column');

PostGIS – Convert EPSG:900913 to EPSG:4326 using Query

Mar
08

ST_AsText – represent geometry as WKT

ST_Transform – transform geometry from one projection to another

ST_GeomFromText – represent WKT as geometry

SELECT ST_AsText(ST_Transform(ST_GeomFromText(ST_AsText(geometry_column),900913), 4326)) from TABLE_NAME;

Reference: PostGIS 1.5 Manual

Android – Connect to PHP

Feb
13

Search keyword: android connect to php

Here, I’m going to show the simplest way to connect to the PHP in Android – receiving JSON(JavaScript Object Notation) upon successful. You could make a simple JSON response in a PHP script like the following and deploy it to the server / localhost.

$arr = (object) array();
$arr->str = "String";
$arr->number = 1;
$arr->rightOrWrong = true;
$arr->nothing = null;
$arr->decimal = 10.209;
$arr->arr = array(1,2,3,4);
$arr->obj = (object) array('one' => 1, 'two' => 2, 'three' => 3, 'four' => 4);
$arr->title = "This is a JSON object";

echo json_encode($arr);

I’m creating a new Android project in Eclipse and extending the AsyncTask class in order to connect to PHP, and using HtppGet to make a connection to the server(can be Windows / Linux base).

package com.rndxpress.androidphpmysql;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.os.AsyncTask;
import android.util.Log;

public class Conn extends AsyncTask<Void, Void, Void> {
	
	private static String convertStreamToString(InputStream is) {
        
        BufferedReader reader = new BufferedReader(new InputStreamReader(is));
        StringBuilder sb = new StringBuilder();
 
        String line = null;
        try {
            while ((line = reader.readLine()) != null) {
                sb.append(line + "n");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return sb.toString();
    }
	@Override
	protected Void doInBackground(Void... params) {
	String url = "http://[domain-name]/[json-interface]";
		
        HttpClient httpclient = new DefaultHttpClient();
 
        HttpGet httpget = new HttpGet(url); 
 
        HttpResponse response;
        try {
        	
            response = httpclient.execute(httpget);
            // Examine the response status
            Log.i("Conn::response",response.getStatusLine().toString());
 
            HttpEntity entity = response.getEntity();
            
            if (entity != null) {
            	 
                // A Simple JSON Response Read
                InputStream instream = entity.getContent();
                String result = convertStreamToString(instream);
                Log.i("Conn::result",result);
 
                // A Simple JSONObject Creation
                JSONObject json=new JSONObject(result);
                Log.i("Conn::JSONObject",json.toString());
                
                instream.close();
            }
        } catch (ClientProtocolException e) {
        	Log.i("Conn::ClientProtocolException",e.getMessage());
        } catch (IOException e) {
        	Log.i("Conn::IOException",e.getMessage());
        } catch (JSONException e) {
        	Log.i("Conn::JSONException",e.getMessage());
        } catch (Error e) {
        	Log.i("Conn::Error",e.getMessage());
        } 
		return null;
	}
}

Following are the screenshot from my Galaxy Nexus & result logged in Eclipse.

Once you able to received result / response, next step you can further develop apps for more complex operations with PHP, MySQL or any further processing on the server side.

Simple UI - a button with click event to connect to the server.

Simple UI – a button with click event to connect to the server.

Logged result - JSON.toString()

Logged result – JSON.toString();

PostGIS – 1.5 to 2.0

Jan
21

Just a note, do run ‘legacy.sql‘ when migrate from PostGIS 1.5 to PostGIS 2.0 because there’s functions being removed from 1.5. so we need it back.

Reference: http://postgis.refractions.net/docs/PostGIS_FAQ.html#legacy_faq

GeoServer, OpenLayers, PostGis – WFS-T

Jan
21

A good tutorial on WFS-T. Didn’t try it yet, but will try it soon.

http://www.gistutor.com/openlayers/22-advanced-openlayers-tutorials/47-openlayers-wfs-t-using-a-geoserver-hosted-postgis-layer.html