Spatial data management using PostGIS - Revision history

Alex Fortin: /* References */

2025-04-02T21:27:33Z

References

Alex Fortin: /* References */

2025-04-02T20:27:48Z

References

Smitch: /* Data */

2024-11-21T13:39:24Z

Data

Smitch: /* About the Tutorial */

2024-11-21T13:38:09Z

About the Tutorial

Alex Fortin: /* References */

2024-10-05T00:36:25Z

References

Alex Fortin: /* Managing Data */

2024-10-05T00:34:26Z

Managing Data

Alex Fortin: /* PostGIS */

2024-10-05T00:33:35Z

PostGIS

Alex Fortin: /* PgAdmin Optional /

2024-10-05T00:27:21Z

PgAdmin *Optional

Alex Fortin: /* References */

2024-10-05T00:26:43Z

References

Alex Fortin at 00:18, 5 October 2024

2024-10-05T00:18:08Z

@@ Line 974: / Line 974: @@
 *Carleton Orthophotos. Carleton Library. https://library.carleton.ca/find/gis/geospatial-data/carleton-campus-aerial-images
 [[Category:Tutorials]]
-* [[Twin Lakes Ranch NCC Land Cover Classification Source]] [[user: Alex Fortin| Alex Fortin]] (2025)

@@ Line 974: / Line 974: @@
 *Carleton Orthophotos. Carleton Library. https://library.carleton.ca/find/gis/geospatial-data/carleton-campus-aerial-images
 [[Category:Tutorials]]
+* [[Twin Lakes Ranch NCC Land Cover Classification Source]] [[user: Alex Fortin| Alex Fortin]] (2025)

@@ Line 59: / Line 59: @@
 == '''Data''' ==
-The original data for this tutorial was acquire from [https://web.archive.org/web/20231015081720/http://www.geobase.ca/ geobase.ca] for the Ottawa region. Unfortunately, this site is no longer accessible but its collections are available on [https://open.canada.ca/en open.canada.ca].
+The original data for this tutorial were acquired from [https://web.archive.org/web/20231015081720/http://www.geobase.ca/ geobase.ca] for the Ottawa region. Unfortunately, this site is no longer accessible but its collections are available on [https://open.canada.ca/en open.canada.ca].
@@ Line 69: / Line 69: @@
 === Windows Data ===
-In this section, data was acquire from Open Canada for the Kapuskasing region. For simplicity, it is suggested to download <u>identical or similar</u> data from Open Canada.
+In this section, data were acquired from Open Canada for the Kapuskasing region. For simplicity, it is suggested to download <u>identical or similar</u> data from Open Canada.
-This data can be found in the National Hydro Network and can be downloaded [https://ftp.maps.canada.ca/pub/nrcan_rncan/vector/geobase_nhn_rhn/shp_en/04/ here] (Kapuskasing Data only).
+These data can be found in the National Hydro Network and can be downloaded [https://ftp.maps.canada.ca/pub/nrcan_rncan/vector/geobase_nhn_rhn/shp_en/04/ here] (Kapuskasing Data only).
@@ Line 80: / Line 80: @@
-Raster Data was also used in the form of a Carleton University Orthophoto which can be found [https://library.carleton.ca/find/gis/geospatial-data/carleton-campus-aerial-images here].
+Raster data were also used in the form of a Carleton University Orthophoto which can be found [https://library.carleton.ca/find/gis/geospatial-data/carleton-campus-aerial-images here].
 *[https://sign.library.carleton.ca/resources/gis-data/access/Carleton_University_Campus/Images/Drape2019/Carleton%20University.ZIP 2019 orthophotos] -  Ontario Ministry of Natural Resources (16cm) - Carleton University_1 which was renamed carleton
 === Linux Data ===
-In this tutorial, all the data was acquired from geobase.ca for the Ottawa region. For simplicity, it is suggested to download <u>identical or '''similar'''</u> data from Geobase.
+In this tutorial, all the data were acquired from geobase.ca for the Ottawa region. For simplicity, it is suggested to download <u>identical or '''similar'''</u> data from Geobase.
 The data utilized in this tutorial, can be found at www.geobase.ca, under National Hydro Network by simply performing a search for "02LH000", and downloading the ESRI Shapefile. In order to download data from Geobase, it is necessary to have an account, but the accounts are freely available.

← Older revision		Revision as of 13:38, 21 November 2024
Line 47:		Line 47:


	~~Ubuntu~~ Linux ~~which~~ we ~~access~~ through OSGeoLive is unfortunately ~~using~~ ~~older~~ ~~software~~ without ~~the~~ ~~possibility~~ to ~~bring~~ up to ~~date (Example: PostGIS 3.3.3, QGIS 3.28.5, pgAdmin 3)~~. As such, a focus on the Windows method is suggested.		The Linux-based tutorial used Ubuntu Linux accessed through OSGeoLive; that particular version unfortunately can't be updated without a much newer overall operating system installation. As such, a focus on the Windows method is suggested until this portion of the tutorial can be updated.

@@ Line 947: / Line 947: @@
 *PostgreSQL 8.4 Documentation.2009. http://www.postgresql.org/docs/8.4/static/
 *Obe, Regina O. & Leo S. Hsu. 2010. PostGIS in Action. Np:Manning Publishing.
-*PostGIS Template Database in GeoLabs. 2006. http://geospatial.nomad-labs.com/2006/12/24/postgis-template-database/
+*PostGIS Template Database in Bitnami. [https://docs.bitnami.com/virtual-machine/infrastructure/postgresql/configuration/create-postgis-template/= https://docs.bitnami.com/template]
 *PostGIS Raster Frequently Asked Questions. http://postgis.refractions.net/documentation/manual-svn/RT_FAQ.html
 *pgAdmin 4 v8.12 release. [https://www.postgresql.org/about/news/pgadmin-4-v812-released-2937/#:~:text=pgAdmin%20is%20the%20leading%20Open,information%2C%20please%20see%20the%20website. https://www.postgresql.org/about]
 Here is a list of links that are either additional support or other tutorials of interest;
@@ Line 966: / Line 967: @@
 *Ubuntu - http://www.ubuntu.com/
 *OSGeo - http://www.osgeo.org/
 Data

← Older revision		Revision as of 00:34, 5 October 2024
Line 597:		Line 597:

	::Now you should have a postgres icon in the dropdown below server on the left-hand side. Click on this to expand the postgres server. Now click on databases, and select the database you created using the template_postgis.		::Now you should have a postgres icon in the dropdown below server on the left-hand side. Click on this to expand the postgres server. Now click on databases, and select the database you created using the template_postgis.

@@ Line 479: / Line 479: @@
 There should be a similar output to what was seen when postgresql was installed.
-*Now we will create a postgis spatial database template, which will be used as a model to create spatial databases. ([[#References | GeoLabs]])[http://geospatial.nomad-labs.com/2006/12/24/postgis-template-database/]. The template is a quick method of creating a spatial database without having to go through the entire process each time, as seen in the creation of the spatial template.
+*Now we will create a postgis spatial database template, which will be used as a model to create spatial databases.([[#References |Bitnami by vmware]]) [https://docs.bitnami.com/virtual-machine/infrastructure/postgresql/configuration/create-postgis-template/=]. The template is a quick method of creating a spatial database without having to go through the entire process each time, as seen in the creation of the spatial template.
 ::If you are not already in the postgres user shell, enter the following

@@ Line 165: / Line 165: @@
 <br>
-pgAdmin is the leading open source management tool for PostgreSQL, the world’s most advanced open source database. pgAdmin 4 is designed to meet the needs of both novice and experienced Postgres users alike, providing a powerful graphical interface that simplifies the creation, maintenance, and use of database objects.(1)<br>
+pgAdmin is the leading open source management tool for PostgreSQL, the world’s most advanced open source database. pgAdmin 4 is designed to meet the needs of both novice and experienced Postgres users alike, providing a powerful graphical interface that simplifies the creation, maintenance, and use of database objects.[https://www.postgresql.org/about/news/pgadmin-4-v812-released-2937/#:~:text=pgAdmin%20is%20the%20leading%20Open,information%2C%20please%20see%20the%20website]<br>
 PgAdmin is available for multiple platforms ([https://www.pgadmin.org/download/ PgAdmin Download Page])

@@ Line 27: / Line 27: @@
 '''Windows'''
-*Installation of PostgreSQL, pgAdmin4* & PostGIS
+*Installation of PostgreSQL, pgAdmin 4* & PostGIS
 *Uploading Data
 *Retrieving Data
@@ Line 44: / Line 44: @@
-The Windows method is the most up to date (2024) using the latest versions (most stable versions) of PostgreSQL (v16.4-1), pgAdmin4 (v8), PostGIS (v3.4.2) & QGIS (v3.34.11).
+The Windows method is the most up to date (2024) using the latest versions (most stable versions) of PostgreSQL (v16.4-1), pgAdmin 4 (v8), PostGIS (v3.4.2) & QGIS (v3.34.11).
-Ubuntu Linux which we access through OSGeoLive is unfortunately using older software without the possibility to bring up to date (Example: PostGIS 3.3.3, QGIS 3.28.5, pgAdmin3). As such, a focus on the Windows method is suggested.
+Ubuntu Linux which we access through OSGeoLive is unfortunately using older software without the possibility to bring up to date (Example: PostGIS 3.3.3, QGIS 3.28.5, pgAdmin 3). As such, a focus on the Windows method is suggested.
@@ Line 450: / Line 450: @@
 *In the terminal enter the following line;
 ::<pre>sudo apt-get install postgresql postgresql-client postgresql-contrib pgadmin3 </pre>
-::This line will get and install packages for postgresql, the postgresql-client, postgresql-contrib, and pgadmin3. The postgresql  package is the database itself, the postgresql-client  is the client to communicate to the database from the shell(terminal),  the postgresql-contrib is a library of additional tools for postgresql, and pgadmin3 is a gui interface that will easily allow user interaction with the database.
+::This line will get and install packages for postgresql, the postgresql-client, postgresql-contrib, and pgAdmin 3. The postgresql  package is the database itself, the postgresql-client  is the client to communicate to the database from the shell(terminal),  the postgresql-contrib is a library of additional tools for postgresql, and pgAdmin 3 is a gui interface that will easily allow user interaction with the database.
@@ Line 662: / Line 662: @@
 ::In this example I will use SRID: 92149 which is NAD83 UTM Zone 18N
-::NOTE; There is a possibility that the SRID may not be in the Spatial_ref_sys table. If that is the case go to http://spatialreference.org/ and find the projection, and there should be an option labeled “PostGIS spatial_ref_sys INSERT statement” click on that link, copy the insert command, and execute the command in the SQL query browser in pgadmin3.
+::NOTE; There is a possibility that the SRID may not be in the Spatial_ref_sys table. If that is the case go to http://spatialreference.org/ and find the projection, and there should be an option labeled “PostGIS spatial_ref_sys INSERT statement” click on that link, copy the insert command, and execute the command in the SQL query browser in pgAdmin 3.
-::In our case it will be necessary to execute the following line to add the spatial reference, which will be done within pgadmin3 in the SQL query editor before adding the data.
+::In our case it will be necessary to execute the following line to add the spatial reference, which will be done within pgAdmin 3 in the SQL query editor before adding the data.
 <pre>
@@ Line 720: / Line 720: @@
-NOTE Before Starting: Open Jump DataStore Query is finicky with names, ensure that all names are lower case for the schema, table, and column names. You may need to modify the table or schema name for the data you want. This is done within pgadmin3;
+NOTE Before Starting: Open Jump DataStore Query is finicky with names, ensure that all names are lower case for the schema, table, and column names. You may need to modify the table or schema name for the data you want. This is done within pgAdmin 3;
 :*Right-click on the schema or the table you want to modify.
@@ Line 818: / Line 818: @@
 ==== Advance Functionality performed Using Pgadmin3 ====
-In pgadmin3 using the sql query builder, queries can ran on the data to get characteristics of the data without having the visualize the data.
+In pgAdmin 3 using the sql query builder, queries can ran on the data to get characteristics of the data without having the visualize the data.
 *In this first query, the area of a polygon will be returned.
-::Enter the following command in the pgadmin3 SQL query tool.
+::Enter the following command in the pgAdmin 3 SQL query tool.
 ::<pre>SELECT ST_AREA(the_geom) As "Area of Extent",* FROM watershed.extent;</pre>
@@ Line 835: / Line 835: @@
-::Enter the following command in the pgadmin3 SQL query tool.
+::Enter the following command in the pgAdmin 3 SQL query tool.
 ::<pre>SELECT ST_Within(waterbodies.the_geom, extent.the_geom) As "Is the water boby within this extent" FROM watershed.extent, watershed.waterbodies;</pre>
@@ Line 841: / Line 841: @@
 ::This will create a list of all the waterbodies records and indicate whether true or false, the waterbody is within the extent.
-*In this last example of using pgadmin3, the distance between two features will measured using the ST_Distance function.
+*In this last example of using pgAdmin 3, the distance between two features will measured using the ST_Distance function.
-""Enter the following command in the pgadmin3 SQL query tool.
+""Enter the following command in the pgAdmin 3 SQL query tool.
 <pre>

Spatial data management using PostGIS - Revision history

Alex Fortin: /* References */

Alex Fortin: /* References */

Smitch: /* Data */

Smitch: /* About the Tutorial */

Alex Fortin: /* References */

Alex Fortin: /* Managing Data */

Alex Fortin: /* PostGIS */

Alex Fortin: /* PgAdmin *Optional */

Alex Fortin: /* References */

Alex Fortin at 00:18, 5 October 2024

Alex Fortin: /* PgAdmin Optional /