Basic Spatial Query with PostGIS

Publié le: 13 janvier 2021
sur la chaîne: 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

Sur cette page du site, vous pouvez voir la vidéo en ligne Basic Spatial Query with PostGIS durée heure minute seconde en bonne qualité , qui a été Téléchargé par l'utilisateur ivinamri 13 janvier 2021, Partagez le lien avec vos amis et connaissances, sur youtube cette vidéo a déjà été regardée 3,625 fois et il a aimé 40 téléspectateurs. Bon visionnage!

608