01-Connect PostDB

PostgreSQL and PostGIS¶

PostgreSQL¶

• Open-source relational database management system (RDBMS).
• Highly extensible with support for custom functions and extensions.
• Offers advanced features like full-text search, JSON support, and multi-version concurrency control (MVCC).

PostGIS¶

• Spatial database extender for PostgreSQL.
• Enables storage, querying, and analysis of geographic information.
  • Adds support for geographic objects and functions for spatial data operations.

Install PostGIS Extension¶

• use CREATE EXTENSION postgis in query tool to install build-in functions of POSTGIS

  

• use SELECT * FROM spatial_ref_sys to check the build-in spatial reference table

  

Import data into PostGIS¶

1. Use GUI (PostGIS Shapefile Import/Export Manager)

   

2. Import data using QGIS (need to be connected first)

   • Choose DB Manager
   • Schema is like subfolder within a database
   • Check Create Spatial Index is very important, it will boost the spatial query speed.

   

Change column type using SQL¶

ALTER TABLE cities5000
ALTER COLUMN dem TYPE INT
USING dem::integer

Using PostgreSQL in Jupyter Notebook¶

• Reference Book
• Reference Class

1. Installation¶

conda create -n sql python
conda activate sql
conda install ipython-sql sqlalchemy psycopg2 notebook pandas -c conda-forge

Using ipython-sql¶

In [1]:

%load_ext sql

%load_ext sql

In [2]:

import os

import os

In [3]:

host = "localhost"
database = "sdb"
user = os.getenv('SQL_USER')
password = os.getenv('SQL_PASSWORD')

host = "localhost" database = "sdb" user = os.getenv('SQL_USER') password = os.getenv('SQL_PASSWORD')

pip install sqlalchemy==1.4.4 downgrade sqlalchemy to 1.4.4

In [4]:

connection_string = f"postgresql://{user}:{password}@{host}/{database}"

connection_string = f"postgresql://{user}:{password}@{host}/{database}"

In [5]:

%sql $connection_string

%sql $connection_string

Out[5]:

'Connected: postgres@sdb'

In [6]:

%%sql 

SELECT * FROM us_cities LIMIT 10

%%sql SELECT * FROM us_cities LIMIT 10

 * postgresql://postgres:***@localhost/sdb
10 rows affected.

Out[6]:

gid	id	pop_2010	elev_in_ft	state	geom
1	53	40888.000000000000000	1611.000000000000000	ND	0101000020E6100000982BDE23F65259C040410DDFC21D4840
2	101	52838.000000000000000	830.000000000000000	ND	0101000020E61000004481994A1A4258C0703398D06EF64740
3	153	15427.000000000000000	1407.000000000000000	ND	0101000020E610000064B4B00257AD58C0B04901B38C744740
4	177	105549.000000000000000	902.000000000000000	ND	0101000020E610000040518F238C3258C0F87F03A447704740
5	192	17787.000000000000000	2411.000000000000000	ND	0101000020E610000050C8F03389B259C080B878D388704740
6	199	18331.000000000000000	1647.000000000000000	ND	0101000020E6100000309303D1EE3859C070B33401D0694740
7	238	33039.000000000000000	115.000000000000000	ME	0101000020E6100000245888B3C73151C008D292228D664640
8	296	19989.000000000000000	138.000000000000000	NY	0101000020E6100000F87C4A84FC5C52C0D8BBC2CC88594640
9	297	11128.000000000000000	295.000000000000000	NY	0101000020E6100000AC36B52220DF52C05037C87FDC584640
10	302	19136.000000000000000	121.000000000000000	ME	0101000020E61000006044C428E37151C0D06CCF87C2274640

Using sqlalchemy¶

In [7]:

from sqlalchemy import create_engine
from sqlalchemy import inspect
import pandas as pd

from sqlalchemy import create_engine from sqlalchemy import inspect import pandas as pd

In [8]:

engine = create_engine(connection_string)
insp = inspect(engine)
insp.get_table_names()

engine = create_engine(connection_string) insp = inspect(engine) insp.get_table_names()

Out[8]:

['spatial_ref_sys',
 'us_states',
 'countries',
 'us_cities',
 'us_counties',
 'cities5000']

• Seems we have incompatible pandas and sqlalchemy,
• Usually, df = pd.read_sql('SELECT * from cities LIMIT 10', engine) will work

In [9]:

connection = engine.raw_connection()
df = pd.read_sql('SELECT * from cities5000 LIMIT 10', con = connection)
df

connection = engine.raw_connection() df = pd.read_sql('SELECT * from cities5000 LIMIT 10', con = connection) df

C:\Users\yyang\AppData\Local\Temp\ipykernel_11880\1650048968.py:2: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql('SELECT * from cities5000 LIMIT 10', con = connection)

Out[9]:

	id	geonameid	name	asciiname	alternatenames	latitude	longitude	feature_class	feature_code	country_code	cc2	admin1_code	admin2_code	admin3_code	admin4_code	population	elevation	dem	timezone	modification_date
0	1	3039163	Sant Julià de Lòria	Sant Julia de Loria	San Julia,San Julià,Sant Julia de Loria,Sant J...	42.46372	1.49129	P	PPLA	AD		06				8022		921	Europe/Andorra	2013-11-23
1	2	3039678	Ordino	Ordino	Ordino,ao er di nuo,orudino jiao qu,Ордино,オルデ...	42.55623	1.53319	P	PPLA	AD		05				3066		1296	Europe/Andorra	2018-10-26
2	3	3040051	les Escaldes	les Escaldes	Ehskal'des-Ehndzhordani,Escaldes,Escaldes-Engo...	42.50729	1.53414	P	PPLA	AD		08				15853		1033	Europe/Andorra	2008-10-15
3	4	3040132	la Massana	la Massana	La Macana,La Massana,La Maçana,La-Massana,la M...	42.54499	1.51483	P	PPLA	AD		04				7211		1245	Europe/Andorra	2008-10-15
4	5	3040686	Encamp	Encamp	Ehnkam,Encamp,en kan pu,enkanpu jiao qu,Энкам,...	42.53474	1.58014	P	PPLA	AD		03				11223		1257	Europe/Andorra	2018-10-26
5	6	3041204	Canillo	Canillo	Canillo,Kanil'o,ka ni e,kaniryo jiao qu,Каниль...	42.5676	1.59756	P	PPLA	AD		02				3292		1561	Europe/Andorra	2018-10-26
6	7	3041563	Andorra la Vella	Andorra la Vella	ALV,Ando-la-Vyey,Andora,Andora la Vela,Andora ...	42.50779	1.52109	P	PPLC	AD		07				20430		1037	Europe/Andorra	2020-03-03
7	8	290594	Umm Al Quwain City	Umm Al Quwain City	Oumm al Qaiwain,Oumm al Qaïwaïn,Um al Kawain,U...	25.56473	55.55517	P	PPLA	AE		07				62747		2	Asia/Dubai	2019-10-24
8	9	291074	Ras Al Khaimah City	Ras Al Khaimah City	Julfa,Khaimah,RAK City,RKT,Ra's al Khaymah,Ra'...	25.78953	55.9432	P	PPLA	AE		05				351943		2	Asia/Dubai	2019-09-09
9	10	291279	Muzayri‘	Muzayri`	Mezaira'a,Mezaira’a,Mizeir`ah,Mizeir‘ah,Mozayr...	23.14355	53.7881	P	PPL	AE		01				10000		123	Asia/Dubai	2013-10-24

QGIS¶

• Install basemap using python script

  

• Connect with PostGIS