Loading Point Data from CSV file into PostgreSQL using PostGIS

Published: 31 December 2020
on channel: ivinamri

1,357

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

On this page of the site you can watch the video online Loading Point Data from CSV file into PostgreSQL using PostGIS with a duration of hours minute second in good quality, which was uploaded by the user ivinamri 31 December 2020, share the link with friends and acquaintances, this video has already been watched 1,357 times on youtube and it was liked by 14 viewers. Enjoy your viewing!

312