Basic Spatial Query with PostGIS

Published: 13 January 2021
on channel: ivinamri

3,625

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
5:05 Import the sekolah.sql file into the remote database using a unique table name
6:48 Next, import lokasisekolah.sql file into the remote database using a unique table
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
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 Basic Spatial Query with PostGIS with a duration of hours minute second in good quality, which was uploaded by the user ivinamri 13 January 2021, share the link with friends and acquaintances, this video has already been watched 3,625 times on youtube and it was liked by 40 viewers. Enjoy your viewing!

608