RDBMS Data Limit

== 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

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

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

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

You CSV file similar to the following:

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

The SQL:

COPY table_name(col1,col2) FROM 'C:\Users\Public\Documents\CSV\data.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

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

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

How to install pgRouting in Windows.

Assalamualaikum,

I ran through a day trying to get everything works for PostgreSQL 9.1, PostGIS 2.0 & pgRouting 1.03.

But so far, pgRouting not working when run in Windows, missing librouting.dll when execute the SQL file provided from pgRouting page, even thought the library is there…not sure why…so I skip the part investigating this issue.

And for you Ubuntu, i’m pretty sure it’s working fine with PostgresSQL 9.1 version even thought am yet try to install it. 😉

For Windows, I need PostgreSQL 8.4, PostGIS 1.5.4 and pgRouting 1.03 in order to use pgRouting.

Here the installation process:

1. Install PostgreSQL 8.4.4 (follow the instructions – and remember the password! 😉 )
2. Install PostGIS 2.0 (follow the instructions)
3. Extract pgRouting-1.03_pg-8.4.2.zip then, copy & paste following to PostgreSQL/8.4/lib.
   1. librouting.dll
   2. librouting_dd.dll
   3. librouting_tsp.dll
4. Open up pgAdmin3(which installed together in #1) & connect to the PostgreSQL. Proceed with following
   1. Open up SQL Pane.
   2. Execute all SQL located in ‘<path_to_the_folder>/pgRouting-1.03_pg-8.4.2/Share/Contrib’

Now, we done with pgRouting core.

Next, to the table schema & data. For this purpose, If you already have routes in shapefile ( or any other format ), you can insert it to PostgreSQL using any tools you like to. In my case, I use QGis – Spit.

Once you already export it to the database, ensure the table have the following columns:

1. gid (integer)
2. id (integer)
3. the_geom ()

You may need to add id column if you don’t have one – my case, I don’t have..so just add the `id` column and update `id` = `gid`.

Next, you need 3 main columns – source, target & length. Just run following command and of course, change the table name. 🙂

1. ALTER TABLE <table_name> ADD COLUMN source integer;
2. ALTER TABLE <table_name> ADD COLUMN target integer;
3. ALTER TABLE <table_name> ADD COLUMN length double precision;

OK, now we’re done with table fields. Next, with table index, run following queries:

1. SELECT assign_vertex_id(‘<table_name>’, 0.001, ‘the_geom’, ‘gid’);
2. UPDATE <table_name> SET length = length(the_geom);
3. CREATE INDEX source_idx ON <table_name>(source);
4. CREATE INDEX target_idx ON <table_name>(target);
5. CREATE INDEX geom_idx ON <table_name> USING GIST(the_geom GIST_GEOMETRY_OPS);

Hopefully there’s no issue so far… ^_^

Now, it’s the test part…let’s test it! Run following queries!

1. Test core function of shortest path – SELECT * FROM shortest_path(‘SELECT gid as id,source::integer,target::integer,length::double precision as cost FROM road_malaysia’,<source>,<target>, false, false);
2. Test Dijkstra – SELECT gid, AsText(the_geom) AS the_geom FROM dijkstra_sp(‘road_malaysia’,<source>,<target>);

If you get the results, means all OK! if not…it’s better to run through step by step…or..diagnose what’s the error message…or can drop by the message here. 😉

Reference: http://www.pgrouting.org/docs/foss4g2007/ch02.html

Wassalam.

 

How to reset user’s password in PostgreSQL using Terminal

Assalamualaikum,

A quick solution to reset PostgreSQL user’s password.

Just run following commands after open up the terminal window:

1. sudo su – postgres
2. psql -d template1
3. template1=# ALTER USER postgres WITH PASSWORD ‘some password’g
4. template1=# q

Then login as usual. 🙂

p/s: I’m using psql 9.1 & ubuntu 12.04 LTS. 🙂

Regards,

Nas