Basic Spatial Query with PostGIS

SGHG 4653 (Advance Database)
Lab #04 Walk-through

Basic Spatial Query with PostGIS

Chapters:
0:00 Intro
0:03 Run HeidiSQl tool locally
0:18 Create a new connection to a remote PostgreSQL database server
1:31 Open the remote database
1:57 Check the remote PostGIS version
2:26 Get current version of the remote PostgreSQL and system information
3:03 Add an existing local PostgreSQL database connection (sekolah2012 DB)
3:38 Export both lokasisekolah and sekolah table as an .sql file
4:51 Disconnect the local database connection
7:37 Analyze and troubleshooting the import error
9:44 Populate the lokasisekolah data into table by executing SQL Insert
10:37 Re-create the spatial indexing using the generic index structure (GIST) for lokasisekolah table
12:22 Open QGIS software to visualize the imported spatial data
12:38 Create a new PostGIS connection to the remote Database Server
13:54 Add the schools data into map view
14:07 Add overlay maps using XYZ Tiles (Openstreet Map and Google Maps)
14:46 Add attribute table sekolah using DB Manager tool
16:47 Save the QGIS project
17:22 Re-define the spatial reference of lokasisekolah table by altering the geom column (see warnings at 15:00)
18:30 Verify the spatial reference changes in QGIS software
21:07 Import the new Shapefile data (Selangor SHP RSO) into the database using the DB Manager tool
22:34 Add the imported Selangor layer into the map view
26:07 Examine the schools data remotely
29:08 Perform Spatial Query (spatial selection) using PostGIS ST_MakeBox2D function
33:43 Get the min. bounding box coordinates (lower-left) in QGIS
34:01 Next, get the max. bounding box coordinates (upper-right) from the map
34:34 Execute the spatial query
34:50 Re-create the similar spatial selection in QGIS using the Select Fetaures by Area or Single Click tool
35:32 Compare both results side-by-side
36:25 Perform another Spatial Query using Select by Location function
37:32 In QGIS, select any polygon feature from Selangor layer
39:50 Then, using the Select by Location tool in QGIS, find schools that are within a selected polygon feature. View the results in attribute table
41:55 Re-create the similar query from QGIS using PostGIS function ST_WITHIN
46:04 Compare both results side-by-side
49:42 Next query is Spatial Intersects in QGIS
51:37 In PostGIS, use the ST_INTERSECTS function
52:40 Perform query Spatial Contain in QGIS
53:56 In QGIS, find schools that are not located in Selangor state (or not mapped correctly)
56:07 Execute the same query using PostGIS ST_CONTAINS function
1:01:35 Export the results as a new Shapefile in QGIS
1:03:00 Add the new exported Shapefile data into map view
1:03:33 Execute the same query using PostGIS ST_WITHIN function (Method #1, (WHERE NOT IN)
1:06:59 Method#2 (WHERE NOT EXISTS) of implementing PostGIS ST_INTERSECTS function
1:09:04 Export the SQL results as a new spatial layer in PostGIS
1:11:01 View the new created spatial layer in QGIS
1:13:42 Export the SQL results as a GeoJSON layer using JSON decomposed binary format (jsonb) in PostgreSQL
1:22:10 Save the result (BLOB) as a file (as .json file)
1:23:37 View the GeoJSON file in QGIS and compare it with other generated layers

Settings for adding a new XYZ Tiles service:

Google Maps (Hybrid)
Name: Google Satellite Hybrid
Min. zoom level: 0
Max. zoom level: 19

OpenStreetMap
Name: OpenStreetMap
Min. zoom level: 0
Max. zoom level: 19