Loading Point Data from CSV file into PostgreSQL using PostGIS

SGHG 4653 (Advance Database)
Assignment #03 Walk-through

Loading Point Data from a CSV file into PostgreSQL using PostGIS

Chapters:
0:00 Intro
0:03 Run pgAdmin 4 interface locally
0:17 Connect to database sekolah2012
1:05 Prepare the CSV file from MS Excel format
2:33 Create a new spatial (Geo-DBMS) table
7:09 Import the CSV file into the Geo-DBMS table
7:58 Analyzing the input file (CSV) for errors
8:44 Error found and need to remove the additional empty/null lines
10:24 Count the number of records populated from the import
11:14 Add a 2D PostGIS point geometry column
to the table structure
13:27 Verifying the EPSG code for RSO coordinate system from EPSG.io
14:11 Found another error. This error associated with PostGIS extension
14:38 Create PostGIS extension for the new table and verify the table structure
16:14 Perform SQL update to populate point data from column rsoX and rsoY to column Geometry (geom) using ST_MakePoint
19:14 Verify the geometry using PostGIS ST_AsText
20:02 Create a spatial index for the GeoDBMS table
20:56 Perform SQL LIKE to retrieve the data with PostgreSQL's LOWER() function
23:39 SQL statement to view current geometry_columns in a table
25:35 Find schools which doesn't have X and Y coordinates (using LEFT JOIN)
31:27 Count how many schools which doesn't have X and Y coordinates in each City
33:32 Launch Quantum GIS software and visualize the Geo-DBMS data
33:47 Create a new PostGIS connection
34:56 Add the school point data into the canvas view
35:30 Add an overlay layer of XYZ Tiles service (Openstreet Map and Google Maps)

Settings for adding a new XYZ Tiles service:

Google Maps (Hybrid)
Name: Google Satellite Hybrid
URL: https://mt1.google.com/vt/lyrs=y&x=%7Bx%7D&y=%7By%7D&z=%7Bz%7D
Min. zoom level: 0
Max. zoom level: 19

OpenStreetMap
Name: OpenStreetMap
URL: https://tile.openstreetmap.org/{z}/{x}/{y}.png
Min. zoom level: 0
Max. zoom level: 19