Today, I have downloaded map of a country and imported it into the postgresql database through osm2pqsql.
All the relations in the database were having millions of rows. So, while selecting all of them at once I was getting error.
First I here tell how to open the psql shell. If you are not sudo user. Don’t Worry. We have a solution for that also.
To open shell as a superuser.
$sudo -u postgres psql
It will prompt to psql command line. To connect to particular database
If you are a normal user. Request to sudo user to create new role for you and create new database for you.
postgres=# create role tamil login password ‘tamil’;
postgres=# CREATE DATABASE mydatabase WITH OWNER = tamil;
Then you access that particular database without sudo power.
$ psql -h localhost -d mydatabase -U tamil
Then , Dr. H S Rai suggested me to limit the number of rows. From there I got a keyword Limit and searched relevant query. I was not having an idea about this query initially. Without wasting more time in telling my story I here directly code the query.
gis=# select * from planet_osm_nodes LIMIT 3;
-[ RECORD 1 ]—
id | 15382126
lat | 115301682
lon | 849624182
-[ RECORD 2 ]—
id | 15382127
lat | 115310352
lon | 849612464
-[ RECORD 3 ]—
id | 15382129
lat | 115327713
lon | 849590461
Another postgresql query to clear the screen of the psql command line.
gis=# \! clear
You can also use shortcut Ctrl +l
Command to check the extensions created in the postgres.
List of installed extensions
Name | Version | Schema | Description
hstore | 1.3 | public | data type for storing sets of (key, value) pairs
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 2.2.1 | public | PostGIS geometry, geography, and raster spatial types and functions
I want to know what basically happen when we create an extension. By creating an extension “postgis” it creates the relation “spatial_ref_sys”. You can check it also.
new=# create extension postgis;
It created a table
List of relations
Schema | Name | Type | Owner
public | spatial_ref_sys | table | postgres
To retrieve the value of a first ten rows of column tag with particular key
gis=# SELECT tags->’name:hi’ from planet_osm_polygon limit 10;
COALESCE function will output the first not null value among the arguments.
gis=# SELECT coalesce(tags->’name:hi’,name) from planet_osm_polygon limit 10;
To play more with the column having datatype hstore you can follow the tutorial.
Command to check database size.