Archive for February, 2024
Add PostGIS to PostgreSQL
The following blog post shows you how to add PostGIS and PgRouting to your existing install of PostgeSQL 14 on the Ubuntu Desktop, Version 22.0.4. This blog post relies on information in this earlier Install and Configure PostgreSQL on Ubuntu post. Generalized documentation on PostGIS exists at this URL.
You install the postgis libraries:
sudo apt install -y postgis |
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: gdal-data libaec0 libaom3 libarmadillo10 libarpack2 libblosc1 libboost-serialization1.74.0 libcfitsio9 libcharls2 libdav1d5 libde265-0 libfreexl1 libfyba0 libgdal30 libgeos-c1v5 libgeos3.10.2 libgeotiff5 libgmpxx4ldbl libhdf4-0-alt libhdf5-103-1 libhdf5-hl-100 libheif1 libkmlbase1 libkmldom1 libkmlengine1 libminizip1 libnetcdf19 libodbc2 libodbcinst2 libogdi4.1 libproj22 libprotobuf-c1 libqhull-r8.0 librttopo1 libsfcgal1 libsnappy1v5 libspatialite7 libsuperlu5 libsz2 liburiparser1 libx265-199 libxerces-c3.2 postgis-doc postgresql-14-postgis-3 postgresql-14-postgis-3-scripts proj-bin proj-data unixodbc-common Suggested packages: geotiff-bin gdal-bin libgeotiff-epsg libhdf4-doc libhdf4-alt-dev hdf4-tools odbc-postgresql tdsodbc ogdi-bin The following NEW packages will be installed: gdal-data libaec0 libaom3 libarmadillo10 libarpack2 libblosc1 libboost-serialization1.74.0 libcfitsio9 libcharls2 libdav1d5 libde265-0 libfreexl1 libfyba0 libgdal30 libgeos-c1v5 libgeos3.10.2 libgeotiff5 libgmpxx4ldbl libhdf4-0-alt libhdf5-103-1 libhdf5-hl-100 libheif1 libkmlbase1 libkmldom1 libkmlengine1 libminizip1 libnetcdf19 libodbc2 libodbcinst2 libogdi4.1 libproj22 libprotobuf-c1 libqhull-r8.0 librttopo1 libsfcgal1 libsnappy1v5 libspatialite7 libsuperlu5 libsz2 liburiparser1 libx265-199 libxerces-c3.2 postgis postgis-doc postgresql-14-postgis-3 postgresql-14-postgis-3-scripts proj-bin proj-data unixodbc-common 0 upgraded, 49 newly installed, 0 to remove and 6 not upgraded. Need to get 42.0 MB of archives. After this operation, 173 MB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 gdal-data all 3.4.1+dfsg-1build4 [216 kB] Get:2 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libaec0 amd64 1.0.6-1 [20.1 kB] Get:3 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libaom3 amd64 3.3.0-1 [1,748 kB] Get:4 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libarpack2 amd64 3.8.0-1 [92.4 kB] Get:5 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libsuperlu5 amd64 5.3.0+dfsg1-2 [183 kB] Get:6 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libarmadillo10 amd64 1:10.8.2+dfsg-1 [105 kB] Get:7 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libsnappy1v5 amd64 1.1.8-1build3 [17.5 kB] Get:8 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libblosc1 amd64 1.21.1+ds2-2 [35.8 kB] Get:9 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libboost-serialization1.74.0 amd64 1.74.0-14ubuntu3 [327 kB] Get:10 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libcfitsio9 amd64 4.0.0-1 [519 kB] Get:11 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libcharls2 amd64 2.3.4-1 [87.0 kB] Get:12 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libdav1d5 amd64 0.9.2-1 [463 kB] Get:13 http://us.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 libde265-0 amd64 1.0.8-1ubuntu0.1 [289 kB] Get:14 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libfyba0 amd64 4.1.1-7 [113 kB] Get:15 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libfreexl1 amd64 1.0.6-1 [33.5 kB] Get:16 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libgeos3.10.2 amd64 3.10.2-1 [713 kB] Get:17 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libgeos-c1v5 amd64 3.10.2-1 [82.5 kB] Get:18 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 proj-data all 8.2.1-1 [10.0 MB] Get:19 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libproj22 amd64 8.2.1-1 [1,257 kB] Get:20 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libgeotiff5 amd64 1.7.0-2build1 [67.1 kB] Get:21 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libhdf4-0-alt amd64 4.2.15-4 [290 kB] Get:22 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libsz2 amd64 1.0.6-1 [5,354 B] Get:23 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libhdf5-103-1 amd64 1.10.7+repack-4ubuntu2 [1,295 kB] Get:24 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libx265-199 amd64 3.5-2 [1,170 kB] Get:25 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libheif1 amd64 1.12.0-2build1 [196 kB] Get:26 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libminizip1 amd64 1.1-8build1 [20.2 kB] Get:27 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 liburiparser1 amd64 0.9.6+dfsg-1 [36.4 kB] Get:28 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libkmlbase1 amd64 1.3.0-9 [45.0 kB] Get:29 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libkmldom1 amd64 1.3.0-9 [150 kB] Get:30 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libkmlengine1 amd64 1.3.0-9 [71.7 kB] Get:31 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libhdf5-hl-100 amd64 1.10.7+repack-4ubuntu2 [59.1 kB] Get:32 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libnetcdf19 amd64 1:4.8.1-1 [456 kB] Get:33 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libodbc2 amd64 2.3.9-5 [159 kB] Get:34 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 unixodbc-common all 2.3.9-5 [9,228 B] Get:35 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libodbcinst2 amd64 2.3.9-5 [31.9 kB] Get:36 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libogdi4.1 amd64 4.1.0+ds-5 [197 kB] Get:37 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libqhull-r8.0 amd64 2020.2-4 [196 kB] Get:38 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 librttopo1 amd64 1.1.0-2 [178 kB] Get:39 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libspatialite7 amd64 5.0.1-2build2 [2,092 kB] Get:40 http://us.archive.ubuntu.com/ubuntu jammy-updates/universe amd64 libxerces-c3.2 amd64 3.2.3+debian-3ubuntu0.1 [929 kB] Get:41 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libgdal30 amd64 3.4.1+dfsg-1build4 [7,642 kB] Get:42 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libgmpxx4ldbl amd64 2:6.2.1+dfsg-3ubuntu1 [9,580 B] Get:43 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libprotobuf-c1 amd64 1.3.3-1ubuntu2.1 [20.3 kB] Get:44 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 libsfcgal1 amd64 1.4.1-1 [2,179 kB] Get:45 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgis amd64 3.2.0+dfsg-1ubuntu1 [350 kB] Get:46 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgis-doc all 3.2.0+dfsg-1ubuntu1 [2,922 kB] Get:47 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgresql-14-postgis-3-scripts all 3.2.0+dfsg-1ubuntu1 [1,018 kB] Get:48 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgresql-14-postgis-3 amd64 3.2.0+dfsg-1ubuntu1 [3,696 kB] Get:49 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 proj-bin amd64 8.2.1-1 [154 kB] Fetched 42.0 MB in 7s (5,994 kB/s) Extracting templates from packages: 100% Selecting previously unselected package gdal-data. (Reading database ... 249055 files and directories currently installed.) Preparing to unpack .../00-gdal-data_3.4.1+dfsg-1build4_all.deb ... Unpacking gdal-data (3.4.1+dfsg-1build4) ... Selecting previously unselected package libaec0:amd64. Preparing to unpack .../01-libaec0_1.0.6-1_amd64.deb ... Unpacking libaec0:amd64 (1.0.6-1) ... Selecting previously unselected package libaom3:amd64. Preparing to unpack .../02-libaom3_3.3.0-1_amd64.deb ... Unpacking libaom3:amd64 (3.3.0-1) ... Selecting previously unselected package libarpack2:amd64. Preparing to unpack .../03-libarpack2_3.8.0-1_amd64.deb ... Unpacking libarpack2:amd64 (3.8.0-1) ... Selecting previously unselected package libsuperlu5:amd64. Preparing to unpack .../04-libsuperlu5_5.3.0+dfsg1-2_amd64.deb ... Unpacking libsuperlu5:amd64 (5.3.0+dfsg1-2) ... Selecting previously unselected package libarmadillo10. Preparing to unpack .../05-libarmadillo10_1%3a10.8.2+dfsg-1_amd64.deb ... Unpacking libarmadillo10 (1:10.8.2+dfsg-1) ... Selecting previously unselected package libsnappy1v5:amd64. Preparing to unpack .../06-libsnappy1v5_1.1.8-1build3_amd64.deb ... Unpacking libsnappy1v5:amd64 (1.1.8-1build3) ... Selecting previously unselected package libblosc1:amd64. Preparing to unpack .../07-libblosc1_1.21.1+ds2-2_amd64.deb ... Unpacking libblosc1:amd64 (1.21.1+ds2-2) ... Selecting previously unselected package libboost-serialization1.74.0:amd64. Preparing to unpack .../08-libboost-serialization1.74.0_1.74.0-14ubuntu3_amd64.deb ... Unpacking libboost-serialization1.74.0:amd64 (1.74.0-14ubuntu3) ... Selecting previously unselected package libcfitsio9:amd64. Preparing to unpack .../09-libcfitsio9_4.0.0-1_amd64.deb ... Unpacking libcfitsio9:amd64 (4.0.0-1) ... Selecting previously unselected package libcharls2:amd64. Preparing to unpack .../10-libcharls2_2.3.4-1_amd64.deb ... Unpacking libcharls2:amd64 (2.3.4-1) ... Selecting previously unselected package libdav1d5:amd64. Preparing to unpack .../11-libdav1d5_0.9.2-1_amd64.deb ... Unpacking libdav1d5:amd64 (0.9.2-1) ... Selecting previously unselected package libde265-0:amd64. Preparing to unpack .../12-libde265-0_1.0.8-1ubuntu0.1_amd64.deb ... Unpacking libde265-0:amd64 (1.0.8-1ubuntu0.1) ... Selecting previously unselected package libfyba0:amd64. Preparing to unpack .../13-libfyba0_4.1.1-7_amd64.deb ... Unpacking libfyba0:amd64 (4.1.1-7) ... Selecting previously unselected package libfreexl1:amd64. Preparing to unpack .../14-libfreexl1_1.0.6-1_amd64.deb ... Unpacking libfreexl1:amd64 (1.0.6-1) ... Selecting previously unselected package libgeos3.10.2:amd64. Preparing to unpack .../15-libgeos3.10.2_3.10.2-1_amd64.deb ... Unpacking libgeos3.10.2:amd64 (3.10.2-1) ... Selecting previously unselected package libgeos-c1v5:amd64. Preparing to unpack .../16-libgeos-c1v5_3.10.2-1_amd64.deb ... Unpacking libgeos-c1v5:amd64 (3.10.2-1) ... Selecting previously unselected package proj-data. Preparing to unpack .../17-proj-data_8.2.1-1_all.deb ... Unpacking proj-data (8.2.1-1) ... Selecting previously unselected package libproj22:amd64. Preparing to unpack .../18-libproj22_8.2.1-1_amd64.deb ... Unpacking libproj22:amd64 (8.2.1-1) ... Selecting previously unselected package libgeotiff5:amd64. Preparing to unpack .../19-libgeotiff5_1.7.0-2build1_amd64.deb ... Unpacking libgeotiff5:amd64 (1.7.0-2build1) ... Selecting previously unselected package libhdf4-0-alt. Preparing to unpack .../20-libhdf4-0-alt_4.2.15-4_amd64.deb ... Unpacking libhdf4-0-alt (4.2.15-4) ... Selecting previously unselected package libsz2:amd64. Preparing to unpack .../21-libsz2_1.0.6-1_amd64.deb ... Unpacking libsz2:amd64 (1.0.6-1) ... Selecting previously unselected package libhdf5-103-1:amd64. Preparing to unpack .../22-libhdf5-103-1_1.10.7+repack-4ubuntu2_amd64.deb ... Unpacking libhdf5-103-1:amd64 (1.10.7+repack-4ubuntu2) ... Selecting previously unselected package libx265-199:amd64. Preparing to unpack .../23-libx265-199_3.5-2_amd64.deb ... Unpacking libx265-199:amd64 (3.5-2) ... Selecting previously unselected package libheif1:amd64. Preparing to unpack .../24-libheif1_1.12.0-2build1_amd64.deb ... Unpacking libheif1:amd64 (1.12.0-2build1) ... Selecting previously unselected package libminizip1:amd64. Preparing to unpack .../25-libminizip1_1.1-8build1_amd64.deb ... Unpacking libminizip1:amd64 (1.1-8build1) ... Selecting previously unselected package liburiparser1:amd64. Preparing to unpack .../26-liburiparser1_0.9.6+dfsg-1_amd64.deb ... Unpacking liburiparser1:amd64 (0.9.6+dfsg-1) ... Selecting previously unselected package libkmlbase1:amd64. Preparing to unpack .../27-libkmlbase1_1.3.0-9_amd64.deb ... Unpacking libkmlbase1:amd64 (1.3.0-9) ... Selecting previously unselected package libkmldom1:amd64. Preparing to unpack .../28-libkmldom1_1.3.0-9_amd64.deb ... Unpacking libkmldom1:amd64 (1.3.0-9) ... Selecting previously unselected package libkmlengine1:amd64. Preparing to unpack .../29-libkmlengine1_1.3.0-9_amd64.deb ... Unpacking libkmlengine1:amd64 (1.3.0-9) ... Selecting previously unselected package libhdf5-hl-100:amd64. Preparing to unpack .../30-libhdf5-hl-100_1.10.7+repack-4ubuntu2_amd64.deb ... Unpacking libhdf5-hl-100:amd64 (1.10.7+repack-4ubuntu2) ... Selecting previously unselected package libnetcdf19:amd64. Preparing to unpack .../31-libnetcdf19_1%3a4.8.1-1_amd64.deb ... Unpacking libnetcdf19:amd64 (1:4.8.1-1) ... Selecting previously unselected package libodbc2:amd64. Preparing to unpack .../32-libodbc2_2.3.9-5_amd64.deb ... Unpacking libodbc2:amd64 (2.3.9-5) ... Selecting previously unselected package unixodbc-common. Preparing to unpack .../33-unixodbc-common_2.3.9-5_all.deb ... Unpacking unixodbc-common (2.3.9-5) ... Selecting previously unselected package libodbcinst2:amd64. Preparing to unpack .../34-libodbcinst2_2.3.9-5_amd64.deb ... Unpacking libodbcinst2:amd64 (2.3.9-5) ... Selecting previously unselected package libogdi4.1. Preparing to unpack .../35-libogdi4.1_4.1.0+ds-5_amd64.deb ... Unpacking libogdi4.1 (4.1.0+ds-5) ... Selecting previously unselected package libqhull-r8.0:amd64. Preparing to unpack .../36-libqhull-r8.0_2020.2-4_amd64.deb ... Unpacking libqhull-r8.0:amd64 (2020.2-4) ... Selecting previously unselected package librttopo1:amd64. Preparing to unpack .../37-librttopo1_1.1.0-2_amd64.deb ... Unpacking librttopo1:amd64 (1.1.0-2) ... Selecting previously unselected package libspatialite7:amd64. Preparing to unpack .../38-libspatialite7_5.0.1-2build2_amd64.deb ... Unpacking libspatialite7:amd64 (5.0.1-2build2) ... Selecting previously unselected package libxerces-c3.2:amd64. Preparing to unpack .../39-libxerces-c3.2_3.2.3+debian-3ubuntu0.1_amd64.deb ... Unpacking libxerces-c3.2:amd64 (3.2.3+debian-3ubuntu0.1) ... Selecting previously unselected package libgdal30. Preparing to unpack .../40-libgdal30_3.4.1+dfsg-1build4_amd64.deb ... Unpacking libgdal30 (3.4.1+dfsg-1build4) ... Selecting previously unselected package libgmpxx4ldbl:amd64. Preparing to unpack .../41-libgmpxx4ldbl_2%3a6.2.1+dfsg-3ubuntu1_amd64.deb ... Unpacking libgmpxx4ldbl:amd64 (2:6.2.1+dfsg-3ubuntu1) ... Selecting previously unselected package libprotobuf-c1:amd64. Preparing to unpack .../42-libprotobuf-c1_1.3.3-1ubuntu2.1_amd64.deb ... Unpacking libprotobuf-c1:amd64 (1.3.3-1ubuntu2.1) ... Selecting previously unselected package libsfcgal1. Preparing to unpack .../43-libsfcgal1_1.4.1-1_amd64.deb ... Unpacking libsfcgal1 (1.4.1-1) ... Selecting previously unselected package postgis. Preparing to unpack .../44-postgis_3.2.0+dfsg-1ubuntu1_amd64.deb ... Unpacking postgis (3.2.0+dfsg-1ubuntu1) ... Selecting previously unselected package postgis-doc. Preparing to unpack .../45-postgis-doc_3.2.0+dfsg-1ubuntu1_all.deb ... Unpacking postgis-doc (3.2.0+dfsg-1ubuntu1) ... Selecting previously unselected package postgresql-14-postgis-3-scripts. Preparing to unpack .../46-postgresql-14-postgis-3-scripts_3.2.0+dfsg-1ubuntu1_all.deb ... Unpacking postgresql-14-postgis-3-scripts (3.2.0+dfsg-1ubuntu1) ... Selecting previously unselected package postgresql-14-postgis-3. Preparing to unpack .../47-postgresql-14-postgis-3_3.2.0+dfsg-1ubuntu1_amd64.deb ... Unpacking postgresql-14-postgis-3 (3.2.0+dfsg-1ubuntu1) ... Selecting previously unselected package proj-bin. Preparing to unpack .../48-proj-bin_8.2.1-1_amd64.deb ... Unpacking proj-bin (8.2.1-1) ... Setting up libgeos3.10.2:amd64 (3.10.2-1) ... Setting up libaom3:amd64 (3.3.0-1) ... Setting up libxerces-c3.2:amd64 (3.2.3+debian-3ubuntu0.1) ... Setting up proj-data (8.2.1-1) ... Setting up libogdi4.1 (4.1.0+ds-5) ... Setting up libcharls2:amd64 (2.3.4-1) ... Setting up libminizip1:amd64 (1.1-8build1) ... Setting up libarpack2:amd64 (3.8.0-1) ... Setting up libsuperlu5:amd64 (5.3.0+dfsg1-2) ... Setting up libqhull-r8.0:amd64 (2020.2-4) ... Setting up libproj22:amd64 (8.2.1-1) ... Setting up postgresql-14-postgis-3-scripts (3.2.0+dfsg-1ubuntu1) ... update-alternatives: using /usr/share/postgresql/14/extension/postgis-3.control to provide /usr/share/postg resql/14/extension/postgis.control (postgresql-14-postgis.control) in auto mode Setting up libprotobuf-c1:amd64 (1.3.3-1ubuntu2.1) ... Setting up libaec0:amd64 (1.0.6-1) ... Setting up gdal-data (3.4.1+dfsg-1build4) ... Setting up libgeotiff5:amd64 (1.7.0-2build1) ... Setting up libsnappy1v5:amd64 (1.1.8-1build3) ... Setting up libcfitsio9:amd64 (4.0.0-1) ... Setting up postgis-doc (3.2.0+dfsg-1ubuntu1) ... Setting up libgmpxx4ldbl:amd64 (2:6.2.1+dfsg-3ubuntu1) ... Setting up libgeos-c1v5:amd64 (3.10.2-1) ... Setting up unixodbc-common (2.3.9-5) ... Setting up libhdf4-0-alt (4.2.15-4) ... Setting up libx265-199:amd64 (3.5-2) ... Setting up libboost-serialization1.74.0:amd64 (1.74.0-14ubuntu3) ... Setting up libodbc2:amd64 (2.3.9-5) ... Setting up liburiparser1:amd64 (0.9.6+dfsg-1) ... Setting up librttopo1:amd64 (1.1.0-2) ... Setting up libfreexl1:amd64 (1.0.6-1) ... Setting up libfyba0:amd64 (4.1.1-7) ... Setting up libkmlbase1:amd64 (1.3.0-9) ... Setting up libblosc1:amd64 (1.21.1+ds2-2) ... Setting up libsfcgal1 (1.4.1-1) ... Setting up libdav1d5:amd64 (0.9.2-1) ... Setting up libde265-0:amd64 (1.0.8-1ubuntu0.1) ... Setting up libsz2:amd64 (1.0.6-1) ... Setting up libkmldom1:amd64 (1.3.0-9) ... Setting up libspatialite7:amd64 (5.0.1-2build2) ... Setting up libodbcinst2:amd64 (2.3.9-5) ... Setting up libarmadillo10 (1:10.8.2+dfsg-1) ... Setting up libkmlengine1:amd64 (1.3.0-9) ... Setting up libheif1:amd64 (1.12.0-2build1) ... Setting up proj-bin (8.2.1-1) ... Setting up libhdf5-103-1:amd64 (1.10.7+repack-4ubuntu2) ... Setting up libhdf5-hl-100:amd64 (1.10.7+repack-4ubuntu2) ... Setting up libnetcdf19:amd64 (1:4.8.1-1) ... Setting up libgdal30 (3.4.1+dfsg-1build4) ... Setting up postgresql-14-postgis-3 (3.2.0+dfsg-1ubuntu1) ... Setting up postgis (3.2.0+dfsg-1ubuntu1) ... Processing triggers for postgresql-common (238) ... Building PostgreSQL dictionaries from installed myspell/hunspell packages... en_us Removing obsolete dictionary files: Processing triggers for libc-bin (2.35-0ubuntu3.6) ... Processing triggers for man-db (2.10.2-1) ... |
You install the postgresql-14-pgrouting libraries:
sudo apt install -y postgresql-14-pgrouting |
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: postgresql-14-pgrouting-scripts Suggested packages: postgresql-14-pgrouting-doc The following NEW packages will be installed: postgresql-14-pgrouting postgresql-14-pgrouting-scripts 0 upgraded, 2 newly installed, 0 to remove and 6 not upgraded. Need to get 705 kB of archives. After this operation, 4,314 kB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgresql-14-pgrouting-scripts all 3.3.0-2 [46.3 kB] Get:2 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 postgresql-14-pgrouting amd64 3.3.0-2 [659 kB] Fetched 705 kB in 1s (721 kB/s) Selecting previously unselected package postgresql-14-pgrouting-scripts. (Reading database ... 250431 files and directories currently installed.) Preparing to unpack .../postgresql-14-pgrouting-scripts_3.3.0-2_all.deb ... Unpacking postgresql-14-pgrouting-scripts (3.3.0-2) ... Selecting previously unselected package postgresql-14-pgrouting. Preparing to unpack .../postgresql-14-pgrouting_3.3.0-2_amd64.deb ... Unpacking postgresql-14-pgrouting (3.3.0-2) ... Setting up postgresql-14-pgrouting-scripts (3.3.0-2) ... Setting up postgresql-14-pgrouting (3.3.0-2) ... Processing triggers for postgresql-common (238) ... Building PostgreSQL dictionaries from installed myspell/hunspell packages... en_us Removing obsolete dictionary files: |
You should also install ogr2ogr program, which is a command-line utility for converting data between GIS data formats, including common file formats and common spatial databases. You install the ogr2ogr libraries:
sudo apt install -y gdal-bin |
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: python3-gdal python3-numpy Suggested packages: libgdal-grass python-numpy-doc python3-pytest The following NEW packages will be installed: gdal-bin python3-gdal python3-numpy 0 upgraded, 3 newly installed, 0 to remove and 11 not upgraded. Need to get 4,381 kB of archives. After this operation, 24.9 MB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 python3-numpy amd64 1:1.21.5-1ubuntu22.04.1 [3,467 kB] Get:2 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 python3-gdal amd64 3.4.1+dfsg-1build4 [673 kB] Get:3 http://us.archive.ubuntu.com/ubuntu jammy/universe amd64 gdal-bin amd64 3.4.1+dfsg-1build4 [241 kB] Fetched 4,381 kB in 1s (2,965 kB/s) Selecting previously unselected package python3-numpy. (Reading database ... 249006 files and directories currently installed.) Preparing to unpack .../python3-numpy_1%3a1.21.5-1ubuntu22.04.1_amd64.deb ... Unpacking python3-numpy (1:1.21.5-1ubuntu22.04.1) ... Selecting previously unselected package python3-gdal. Preparing to unpack .../python3-gdal_3.4.1+dfsg-1build4_amd64.deb ... Unpacking python3-gdal (3.4.1+dfsg-1build4) ... Selecting previously unselected package gdal-bin. Preparing to unpack .../gdal-bin_3.4.1+dfsg-1build4_amd64.deb ... Unpacking gdal-bin (3.4.1+dfsg-1build4) ... Setting up python3-numpy (1:1.21.5-1ubuntu22.04.1) ... Setting up python3-gdal (3.4.1+dfsg-1build4) ... Setting up gdal-bin (3.4.1+dfsg-1build4) ... Processing triggers for man-db (2.10.2-1) ... |
Verify the installation by using the which utility, like
which -a ogr2ogr |
It should return:
/usr/bin/ogr2ogr |
You can qualify the installed PostGIS packages with the following command:
dpkg -l | grep -i postgis |
It should display:
ii postgis 3.2.0+dfsg-1ubuntu1 amd64 Geographic objects support for PostgreSQL ii postgis-doc 3.2.0+dfsg-1ubuntu1 all Geographic objects support for PostgreSQL -- documentation ii postgresql-14-pgrouting 3.3.0-2 amd64 Routing functionality support for PostgreSQL/PostGIS ii postgresql-14-pgrouting-scripts 3.3.0-2 all Routing functionality support for PostgreSQL/PostGIS - SQL scripts ii postgresql-14-postgis-3 3.2.0+dfsg-1ubuntu1 amd64 Geographic objects support for PostgreSQL 14 ii postgresql-14-postgis-3-scripts 3.2.0+dfsg-1ubuntu1 all Geographic objects support for PostgreSQL 14 -- SQL scripts |
Connect as the postgres user by becoming the root user with this command:
sudo sh |
Then, assume the role of the postgres user with this command:
su - postgres |
Connect to the PostgreSQL database as the privileged postgres owner/user:
psql postgres |
You will see the following prompt after connecting to the PostgreSQL database:
postgres@student-virtual-machine:~$ psql psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1)) Type "help" for help. postgres=# |
As the the privileged postgres owner/user issue the following commands to create the gisdb database and set a new search path for it:
CREATE DATABASE gisdb; ALTER DATABASE gisdb SET search_path=public,postgis,contrib,tiger; |
Connect to the gisdb database:
\connect gisdb |
You are now connected to database gisdb as the postgres user. You change to the postgis schema, and create the following extensions in this schema.
CREATE SCHEMA postgis; CREATE EXTENSION postgis SCHEMA postgis; CREATE EXTENSION postgis_raster SCHEMA postgis; CREATE EXTENSION fuzzystrmatch SCHEMA postgis; CREATE EXTENSION address_standardizer_data_us SCHEMA postgis; |
You query the modified catalog with this query:
SELECT n.nspname AS "Name" , pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner" FROM pg_catalog.pg_namespace n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ORDER BY 1; |
It should return:
Name | Owner ---------+---------- postgis | postgres public | postgres (2 rows) |
You must assign the postgis_tiger_geocoder and postgis_topology without a schema assignment. The
postgis_tiger_eeocoder must be assigned by default to the tiger schema, and the postgis_topology schema.
CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION postgis_topology; |
You reuse the same above referenced query to see the modified catalog with this query:
It should return:
Name | Owner ------------+---------- postgis | postgres public | postgres tiger | postgres tiger_data | postgres topology | postgres (5 rows) |
Connect as the gisdb database with this command:
\connect gisdb |
You can see the active PostGIS extension with this command:
\dx postgis |
It shows:
List of installed extensions Name | Version | Schema | Description ---------+---------+---------+------------------------------------------------------------ postgis | 3.2.0 | postgis | PostGIS geometry and geography spatial types and functions (1 row) |
Now, you can use this query:
SELECT postgis_full_version(); |
to discover what PostGIS version is installed:
postgis_full_version -------------------------------------------------------------------------------------------------------------------------------------------------------------------- POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.12" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (1 row) |
Connect back to as the privileged postgres owner/user with this command:
\connect postgres |
Next, check the available databases with this command:
\l |
It should display the following:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
gisdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
videodb | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/student +
| | | | | student=CTc/student +
| | | | | dba=CTc/student
(5 rows) |
As the privileged postgres owner/user make the following grants with these commands:
GRANT TEMPORARY, CONNECT ON DATABASE gisdb TO PUBLIC; GRANT ALL PRIVILEGES ON DATABASE gisdb TO postgres; GRANT ALL PRIVILEGES ON DATABASE gisdb TO dba; |
After making the grants, check the available databases access with this \l command:
\l |
It should display the following:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
gisdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | dba=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
videodb | student | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/student +
| | | | | student=CTc/student +
| | | | | dba=CTc/student
(5 rows) |
At this point, you can exit psql, the postgres user’s account, and the root user’s account. This should return you to your sudoer account, which in my case is the student user.
Connect to the gisdb with the following command-line interface command:
psql -U student -W -d gisdb |
You can create the following, as per instructions in PostGIS Chapter 9 instructions:
-- Conditionally drop table. DROP TABLE IF EXISTS geometries; -- Create table with geometry column in table. CREATE TABLE geometries ( geometries_id INT , name VARCHAR , geometry_obj GEOMETRY); INSERT INTO geometries ( name , geometry_obj ) VALUES ('Point', 'POINT(0 0)') ,('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)') ,('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') ,('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))') ,('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))'); SELECT name , ST_AsText(geometry_obj) FROM geometries; |
Unfortunately, it raises the following error when attempting to create the geometries table:
psql:/home/student/Code/postgis/geometry.sql:7: ERROR: type "geometry" does not exist LINE 4: , geometry_obj GEOMETRY); |
As always, I hope the solutions presented helps move forward implementations of the technology. You can also find an excellent tutorial to learning PostGIS in the Introduction to PostGIS tutorial.
Sqlite on Ubuntu
We decided to include some existing Sqlite databases in our AWS Ubuntu learning lab because they’re used by the Data Science courses (specifically, DS 250). Installing Sqlite is quite simple:
sudo apt install -y sqlite |
You can check the install by using the which utility, like:
which -a sqlite3 |
On Ubuntu, it should return:
/usr/bin/sqlite3 |
There is a friendly help document online that can provide insight in how to use Sqlite. You can create a new student.db database with the following syntax from the Ubuntu CLI (Command-Line Interface):
sqlite3 student.db |
It would return the following:
SQLite version 3.37.2 2022-01-06 13:25:41 Enter ".help" FOR usage hints. sqlite> |
A little warning about the simple example and how it opens only a transient in-memory database. If you want a persistent database, you must open sqlite3 without any arguments. Then, you must use the .open method to open a persistent student.db database create a file in the relative directory path where you launched sqlite3 executable. You can read more about persistent in Sqlite in the documentation.
.open student.db |
The alternative opens the student.db file in a fully qualified path:
.open /home/student/Code/sqlite/db/student.db |
If you type .databases at the sqlite> prompt it would return:
sqlite> .databases main: /home/student/Code/sqlite/db/student.db r/w |
Let’s create a script file that creates two tables, a foreign key reference from one of the tables to the other, and some data with the following create_sample.sql script:
-- Drop knight table if exists. DROP TABLE IF EXISTS knight; -- Drop kingdom table if exists. DROP TABLE IF EXISTS kingdom; -- Create normalized table kingdom CREATE TABLE kingdom ( kingdom_id INTEGER PRIMARY KEY , kingdom_name VARCHAR(20) , population INTEGER , book VARCHAR(40)); -- Insert kingdom into table. INSERT INTO kingdom ( kingdom_id , kingdom_name , population , book ) VALUES ( 1, 'Narnia', 42100, 'Prince Caspian' ) ,( 2, 'Narnia', 77600, 'The Lion, The Witch and The Wardrobe' ) ,( 3, 'Camelot', 15200, 'The Once and Future King' ); -- Create normalized knight table. CREATE TABLE knight ( knight_id INTEGER PRIMARY KEY , knight_name VARCHAR(22) , kingdom_allegiance_id INTEGER , allegiance_start_date text , allegiance_end_date text , book VARCHAR(40) , FOREIGN KEY (kingdom_allegiance_id) REFERENCES kingdom(kingdom_id)); -- Insert knights into table. INSERT INTO knight ( knight_id , knight_name , kingdom_allegiance_id , allegiance_start_date , allegiance_end_date , book ) VALUES ( 1, 'Peter the Magnificent', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' ) ,( 2, 'Edmund the Just', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' ) ,( 3, 'Susan the Gentle', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' ) ,( 4, 'Lucy the Valiant', 2, '1272-03-20', '1292-06-19', 'The Lion, The Witch and The Wardrobe' ) ,( 5, 'Peter the Magnificent', 1, '1531-04-12', '1328-05-31', 'Prince Caspian' ) ,( 6, 'Edmund the Just', 1, '1531-04-12', '1328-05-31', 'Prince Caspian' ) ,( 7, 'Susan the Gentle', 1, '1531-04-12', '1328-05-31', 'Prince Caspian' ) ,( 8, 'Lucy the Valiant', 1, '1531-04-12', '1328-05-31', 'Prince Caspian' ) ,( 9, 'King Arthur', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' ) ,( 10, 'Sir Lionel', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' ) ,( 11, 'Sir Bors', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' ) ,( 12, 'Sir Bors', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' ) ,( 13, 'Sir Galahad', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' ) ,( 14, 'Sir Gawain', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' ) ,( 15, 'Sir Tristram', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' ) ,( 16, 'Sir Percival', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' ) ,( 17, 'Sir Lancelot', 3, '0631-03-10', '0686-12-12', 'The Once and Future King' ); |
You can run the create_sample.sql script with the following syntax using an absolute path:
sqlite> .read /home/student/Code/sqlite/create_tables.sql |
Then, you can write a query like this to retrieve the data from two tables:
SELECT k.kingdom_name , kn.knight_name FROM kingdom k INNER JOIN knight kn ON k.kingdom_id = kn.kingdom_allegiance_id WHERE k.book = 'Prince Caspian'; |
It will return the following:
Narnia|Peter the Magnificent Narnia|Edmund the Just Narnia|Susan the Gentle Narnia|Lucy the Valiant |
You can exit sqlite3 by entering .quit or Control+D (the system End-Of-File character). If you can determine whether you have a transient or persistent student.db database file with the long list (ll) command.
The following command:
ll /home/student/Code/sqlite/db/student.db |
should return the following:
-rw-r--r-- 1 student student 12288 Feb 9 23:01 /home/student/Code/sqlite/db/student.db |
If the command returned a 0 sized student.db database file, you created a transient Sqlite table. You’ll need to redo the creation of the student.db database file with the .open command as qualified above.
If you want to detach a database from your active Sqlite session, you can issue the following command to remove it:
sqlite> DETACH DATABASE student.db |
Let’s jazz it up a bit with some Python. The first example verifies the ODBC driver’s ability to connect to Sqlite. Please note that it returns the same result for a transient and persistent database file. You can refer to the following documentation for Python examples.
#!/usr/bin/python # Import sqlite3 ODBC library. import sqlite3 try: # Open a connection to the student.db database db = sqlite3.connect('/home/student/Code/sqlite/db/student.db') # Print a string to say you've connected to the student.db database. print("Sqlite database connection success.") except sqlite3.Error as e: print('SQLite error: %s' % (' '.join(e.args))) print("Exception class is: ", e.__class__) print('SQLite traceback: ') exc_type, exc_value, exc_tb = sys.exc_info() print(traceback.format_exception(exc_type, exc_value, exc_tb)) sys.exit(1) finally: # Close the connection when it is open. if db: db.close() |
You can run the sqlite_connection.py script with the following syntax from its local directory:
Sqlite database connection success. |
Assuming you have created a persistent Sqlite database, as qualified above with the .open command and fully qualified file name. A fully qualified file name as a path from a Linux mount point to the file.
You must use the fully qualified file name for a persistent Sqlite student.db database as the database parameter for the sqlite3.connect() method, as shown on line #9 of the sqlite_query.py program below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | #!/usr/bin/python # Import sys library. import sqlite3 try: # Open a connection to a persistent database, which should use # a fully qualified file name, but may use a relative file # name when the Python code is in the same directory as a # persistent student.db sqlite3 database. db = sqlite3.connect('/home/student/Code/sqlite/db/student.db') # Create a cursor. cursor = db.cursor() # Define a query. query = "SELECT k.kingdom_name " \ ", kn.knight_name " \ "FROM kingdom k INNER JOIN knight kn " \ "ON k.kingdom_id = kn.kingdom_allegiance_id " \ "WHERE k.book = 'Prince Caspian'" # Execute the cursor with the query. cursor.execute( query ) # Display the rows returned by the query. for (kingdom_name, knight_name) in cursor: print('{0} has {1}'.format( kingdom_name.title(), knight_name.title())) except sqlite3.Error as e: print('SQLite error: %s' % (' '.join(e.args))) print("Exception class is: ", e.__class__) print('SQLite traceback: ') exc_type, exc_value, exc_tb = sys.exc_info() print(traceback.format_exception(exc_type, exc_value, exc_tb)) sys.exit(1) finally: # Close the connection when it is open. if db: db.close() |
As always, I hope this helps those trying to get up and running with Sqlite.
Git Hub Desktop on Ubuntu
I need to install Git Hub Desktop on Ubuntu. It was quite straightforward:
bash -c "$(curl -fsSL https://raw.githubusercontent.com/kontr0x/github-desktop-install/main/installGitHubDesktop.sh)" |
Display detailed console log →
Starting install script... Downloading GitHubDesktop-linux-amd64-3.3.8-linux2.deb ... ############################################################################################################### 100.0% Download complete! Should the package be installed? (y/n) y Installing package, this requires sudo privileges! Selecting previously unselected package github-desktop. (Reading database ... 245728 files and directories currently installed.) Preparing to unpack .../GitHubDesktop-linux-amd64-3.3.8-linux2.deb ... Unpacking github-desktop (3.3.8-linux2) ... Setting up github-desktop (3.3.8-linux2) ... Processing triggers for mailcap (3.70+nmu1ubuntu1) ... Processing triggers for gnome-menus (3.36.0-1ubuntu3) ... Processing triggers for desktop-file-utils (0.26-1ubuntu3) ... Processing triggers for hicolor-icon-theme (0.17-2) ... Package installed successfully Clean up downloaded file? (y/n) y File cleaned up! Thank you for using the script, have a nice day! |
You can find it in the 9-dot menu’s second page or launch it from the command line, like:
github-desktop |
Either will launch the GitHub Desktop, as shown below:
As always, I hope this helps those looking for concise and complete free answer on installing GitHub Desktop.
Ruby+PostgreSQL on Ubuntu
This extends the earlier post on installing and configuring Ruby 3.3.0 on Ubuntu 22.0.4. Please refer to that earlier post to install Ruby. This post shows you how to install the necessary libraries and Ruby Gems for PostgreSQL.
You need to install the libra-dev package, as shown:
sudo apt install postgresql libpq-dev |
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done postgresql is already the newest version (14+238). Suggested packages: postgresql-doc-16 The following NEW packages will be installed: libpq-dev 0 upgraded, 1 newly installed, 0 to remove and 6 not upgraded. Need to get 142 kB of archives. After this operation, 590 kB of additional disk space will be used. Do you want to continue? [Y/n] Y Get:1 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/jammy pgadmin4/main amd64 libpq-dev amd64 16.1-1.pgdg22.04+1 [142 kB] Fetched 142 kB in 7s (20.7 kB/s) Selecting previously unselected package libpq-dev. (Reading database ... 247065 files and directories currently installed.) Preparing to unpack .../libpq-dev_16.1-1.pgdg22.04+1_amd64.deb ... Unpacking libpq-dev (16.1-1.pgdg22.04+1) ... Setting up libpq-dev (16.1-1.pgdg22.04+1) ... Processing triggers for man-db (2.10.2-1) ... |
Next, you need to install the PG Gem:
gem install pg |
Display detailed console log →
Fetching pg-1.5.4.gem Building native extensions. This could take a while... Successfully installed pg-1.5.4 Parsing documentation for pg-1.5.4 Installing ri documentation for pg-1.5.4 Done installing documentation for pg after 3 seconds 1 gem installed |
You can now write a postgres_version.rb program to verify a connection to the PostgreSQL database, like:
# Include Ruby Gem libraries. require 'rubygems' require 'pg' # Begin block. begin # Create a new connection resource. db = PG::connect( 'localhost', 5432, '', '', 'videodb', 'student', 'student') # Create a result set. stmt = db.query('SELECT version() AS version') # Read through the result set hash. stmt.each do | row | puts "#{row['version']}" end # Release the result set resources. stmt.freeze rescue PG::Error => e # Print the error. puts "ERROR #{e.error} (#{e.sqlstate})" puts "Can't connect to the PostgreSQL database specified." # Signal an error. exit 1 ensure # Close the connection when it is open. db.close if db end |
Call the postgres_version.rb program with this syntax:
ruby mysql_version.rb |
It should return:
PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit |
The postgres_columns.rb script returns a couple columns concatenated into a single column:
# Include Ruby Gem libraries. require 'rubygems' require 'pg' # Begin block. begin # Create a new connection resource. db = PG::connect( 'localhost', 5432, '', '', 'videodb', 'student', 'student') # Create a result set. stmt = db.query("SELECT CONCAT(nh.last_name, ', ', nh.first_name) AS name " + \ "FROM new_hire nh " + \ "ORDER BY nh.last_name") # Read through the result set hash. stmt.each do | row | out = "" i = 0 while i < stmt.fields.count() # Check when not last column and use the: # - Hash returned by the result set for the value, and # - String array value returned by the statement object # as the name value of the hash by leveraging its # numeric index. if i < stmt.fields.count() - 1 out += "#{row[stmt.fields[i]]}" out += ", " else out += "#{row[stmt.fields[i]]}" end i += 1 end puts "#{out}" end # Release the result set resources. stmt.freeze rescue PG::Error => e # Print the error. puts "ERROR #{e.error} (#{e.sqlstate})" puts "Can't connect to PostgreSQL database specified." # Signal an error. exit 1 ensure # Close the connection when it is open. db.close if db end |
Call the postgres_columns.rb program with this syntax:
ruby mysql_columns.rb |
It should return:
Chabot, Henry Lewis, Malcolm |
As always, I hope this helps those looking to learn and solve a problem. You can find the PG Gem documentation here.
VSCode Package Error
While running an update on Ubuntu 22.0.4 with the following syntax I got an error on finding the VSCode Package. I ran this to update before adding Ruby and Rails to an Ubuntu virtual machine instance.
sudo apt-get update |
Display detailed console log →
Hit:1 http://us.archive.ubuntu.com/ubuntu jammy InRelease Get:2 http://security.ubuntu.com/ubuntu jammy-security InRelease [110 kB] Get:3 http://us.archive.ubuntu.com/ubuntu jammy-updates InRelease [119 kB] Hit:4 https://dl.google.com/linux/chrome/deb stable InRelease Ign:5 https://packages.microsoft.com/repos/vscode/dists stable InRelease Err:6 https://packages.microsoft.com/repos/vscode/dists stable Release 404 Not Found [IP: 13.90.56.68 443] Hit:7 https://download.vscodium.com/debs vscodium InRelease Hit:8 http://us.archive.ubuntu.com/ubuntu jammy-backports InRelease Hit:9 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/jammy pgadmin4 InRelease Reading package lists... Done E: The repository 'https://packages.microsoft.com/repos/vscode/dists stable Release' does not have a Release file. N: Updating from such a repository can't be done securely, and is therefore disabled by default. N: See apt-secure(8) manpage for repository creation and user configuration details. |
I manually moved the vscode.list file to my student user’s home directory and removed the file from /etc/apt/sources.list.d directory. This allowed me to update all other packages.
Don’t forget to replace the vscode.list file in the /etc/apt/sources.list.d directory.
Ruby+MySQL on Ubuntu
This post goes through installing and configuring Ruby and Ruby on Rails for MySQL. The first step requires updating the Ubuntu OS:
sudo apt-get update |
Interestingly, I found that the man-db service had inadvertently stopped. It raised the following error:
E: dpkg was interrupted, you must manually run 'sudo dpkg --configure -a' to correct the problem. |
You run this command to find the problem with the dpkg utility:
sudo dpkg --configure -a |
It returned:
Setting up man-db (2.10.2-1) ... Updating database of manual pages ... man-db.service is a disabled or a static unit not running, not starting it. |
The following command started the man-db service:
sudo systemctl start man-db.service |
Next, you install the prerequisite packages with this command:
sudo apt-get install -y git-core zlib1g-dev build-essential libssl-dev libreadline-dev libyaml-dev libsqlite3-dev sqlite3 libxml2-dev libxslt1-dev libcurl4-openssl-dev software-properties-common libffi-dev |
Display detailed console log →
Reading package lists... Done Building dependency tree... Done Reading state information... Done Note, selecting 'git' instead of 'git-core' build-essential is already the newest version (12.9ubuntu3). build-essential set to manually installed. libreadline-dev is already the newest version (8.1.2-1). libreadline-dev set to manually installed. git is already the newest version (1:2.34.1-1ubuntu1.10). git set to manually installed. software-properties-common is already the newest version (0.99.22.9). zlib1g-dev is already the newest version (1:1.2.11.dfsg-2ubuntu9.2). zlib1g-dev set to manually installed. The following additional packages will be installed: libssl3 Suggested packages: libcurl4-doc libidn11-dev libkrb5-dev libldap2-dev librtmp-dev libssh2-1-dev sqlite3-doc libssl-doc libyaml-doc The following NEW packages will be installed: libcurl4-openssl-dev libffi-dev libsqlite3-dev libxml2-dev libxslt1-dev libyaml-dev sqlite3 The following packages will be upgraded: libssl-dev libssl3 2 upgraded, 7 newly installed, 0 to remove and 18 not upgraded. Need to get 7,426 kB of archives. After this operation, 12.8 MB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libssl-dev amd64 3.0.2-0ubuntu1.13 [2,374 kB] Get:2 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libssl3 amd64 3.0.2-0ubuntu1.13 [1,902 kB] Get:3 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libcurl4-openssl-dev amd64 7.81.0-1ubuntu1.15 [386 kB] Get:4 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libsqlite3-dev amd64 3.37.2-2ubuntu0.3 [846 kB] Get:5 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libxml2-dev amd64 2.9.13+dfsg-1ubuntu0.3 [804 kB] Get:6 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 libxslt1-dev amd64 1.1.34-4ubuntu0.22.04.1 [219 kB] Get:7 http://us.archive.ubuntu.com/ubuntu jammy-updates/main amd64 sqlite3 amd64 3.37.2-2ubuntu0.3 [768 kB] Get:8 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libffi-dev amd64 3.4.2-4 [63.7 kB] Get:9 http://us.archive.ubuntu.com/ubuntu jammy/main amd64 libyaml-dev amd64 0.2.2-1build2 [62.8 kB] Fetched 7,426 kB in 1s (5,467 kB/s) Preconfiguring packages ... (Reading database ... 246735 files and directories currently installed.) Preparing to unpack .../libssl-dev_3.0.2-0ubuntu1.13_amd64.deb ... Unpacking libssl-dev:amd64 (3.0.2-0ubuntu1.13) over (3.0.2-0ubuntu1.12) ... Preparing to unpack .../libssl3_3.0.2-0ubuntu1.13_amd64.deb ... Unpacking libssl3:amd64 (3.0.2-0ubuntu1.13) over (3.0.2-0ubuntu1.12) ... Setting up libssl3:amd64 (3.0.2-0ubuntu1.13) ... Selecting previously unselected package libcurl4-openssl-dev:amd64. (Reading database ... 246735 files and directories currently installed.) Preparing to unpack .../0-libcurl4-openssl-dev_7.81.0-1ubuntu1.15_amd64.deb ... Unpacking libcurl4-openssl-dev:amd64 (7.81.0-1ubuntu1.15) ... Selecting previously unselected package libsqlite3-dev:amd64. Preparing to unpack .../1-libsqlite3-dev_3.37.2-2ubuntu0.3_amd64.deb ... Unpacking libsqlite3-dev:amd64 (3.37.2-2ubuntu0.3) ... Selecting previously unselected package libxml2-dev:amd64. Preparing to unpack .../2-libxml2-dev_2.9.13+dfsg-1ubuntu0.3_amd64.deb ... Unpacking libxml2-dev:amd64 (2.9.13+dfsg-1ubuntu0.3) ... Selecting previously unselected package libxslt1-dev:amd64. Preparing to unpack .../3-libxslt1-dev_1.1.34-4ubuntu0.22.04.1_amd64.deb ... Unpacking libxslt1-dev:amd64 (1.1.34-4ubuntu0.22.04.1) ... Selecting previously unselected package sqlite3. Preparing to unpack .../4-sqlite3_3.37.2-2ubuntu0.3_amd64.deb ... Unpacking sqlite3 (3.37.2-2ubuntu0.3) ... Selecting previously unselected package libffi-dev:amd64. Preparing to unpack .../5-libffi-dev_3.4.2-4_amd64.deb ... Unpacking libffi-dev:amd64 (3.4.2-4) ... Selecting previously unselected package libyaml-dev:amd64. Preparing to unpack .../6-libyaml-dev_0.2.2-1build2_amd64.deb ... Unpacking libyaml-dev:amd64 (0.2.2-1build2) ... Setting up libyaml-dev:amd64 (0.2.2-1build2) ... Setting up libffi-dev:amd64 (3.4.2-4) ... Setting up libxml2-dev:amd64 (2.9.13+dfsg-1ubuntu0.3) ... Setting up libsqlite3-dev:amd64 (3.37.2-2ubuntu0.3) ... Setting up libcurl4-openssl-dev:amd64 (7.81.0-1ubuntu1.15) ... Setting up libssl-dev:amd64 (3.0.2-0ubuntu1.13) ... Setting up sqlite3 (3.37.2-2ubuntu0.3) ... Setting up libxslt1-dev:amd64 (1.1.34-4ubuntu0.22.04.1) ... Processing triggers for man-db (2.10.2-1) ... Processing triggers for install-info (6.8-4build1) ... Processing triggers for libc-bin (2.35-0ubuntu3.6) ... |
Use the cd command to change to the student home directory. Clone the asdf as the multiple runtime version manager with this command:
git clone https://github.com/excid3/asdf.git ~/.asdf |
The following is the output of the git clone command:
Cloning into '/home/student/.asdf'... remote: Enumerating objects: 8756, done. remote: Counting objects: 100% (829/829), done. remote: Compressing objects: 100% (476/476), done. remote: Total 8756 (delta 428), reused 657 (delta 334), pack-reused 7927 Receiving objects: 100% (8756/8756), 3.10 MiB | 4.29 MiB/s, done. Resolving deltas: 100% (5148/5148), done. |
Next, you fix your .bashrc file by adding the following components:
echo '. "$HOME/.asdf/asdf.sh"' >> ~/.bashrc echo '. "$HOME/.asdf/completions/asdf.bash"' >> ~/.bashrc echo 'legacy_version_file = yes' >> ~/.asdfrc echo 'export EDITOR="code --wait"' >> ~/.bashrc |
Source the modifies shell, which you can do like this:
exec $SHELL |
or, like:
. ${HOME}/.bashrc |
Add the following asdf plug-ins:
asdf plugin add ruby asdf plugin add nodejs |
Install Ruby with the following command:
asdf install ruby 3.3.0 |
Display detailed console log →
Downloading ruby-build...
==> Downloading ruby-3.3.0.tar.gz...
-> curl -q -fL -o ruby-3.3.0.tar.gz https://cache.ruby-lang.org/pub/ruby/3.3/ruby-3.3.0.tar.gz
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 21.0M 100 21.0M 0 0 10.1M 0 0:00:02 0:00:02 --:--:-- 10.1M
==> Installing ruby-3.3.0...
-> ./configure "--prefix=$HOME/.asdf/installs/ruby/3.3.0" --enable-shared --with-ext=openssl,psych,+
-> make -j 2
-> make install
==> Installed ruby-3.3.0 to /home/student/.asdf/installs/ruby/3.3.0
asdf: Warn: You have configured asdf to preserve downloaded files (with always_keep_download=yes or --keep-download). But
asdf: Warn: the current plugin (ruby) does not support that. Downloaded files will not be preserved. |
Install Ruby Global with this syntax:
asdf global ruby 3.3.0 |
Update the Ruby Gems with this command:
gem update --system |
Display detailed console log →
Updating rubygems-update Fetching rubygems-update-3.5.5.gem Successfully installed rubygems-update-3.5.5 Parsing documentation for rubygems-update-3.5.5 Installing ri documentation for rubygems-update-3.5.5 Done installing documentation for rubygems-update after 1 seconds Parsing documentation for rubygems-update-3.5.5 Done installing documentation for rubygems-update after 0 seconds Installing RubyGems 3.5.5 Successfully built RubyGem Name: bundler Version: 2.5.5 File: bundler-2.5.5.gem Bundler 2.5.5 installed RubyGems 3.5.5 installed Regenerating binstubs Regenerating plugins Parsing documentation for rubygems-3.5.5 Installing ri documentation for rubygems-3.5.5 # 3.5.5 / 2024-01-18 ## Enhancements: * Installs bundler 2.5.5 as a default gem. ## Bug fixes: * Fix `require` activation conflicts when requiring default gems under some situations. Pull request [#7379](https://github.com/rubygems/rubygems/pull/7379) by deivid-rodriguez * Use cache_home instead of data_home in default_spec_cache_dir. Pull request [#7331](https://github.com/rubygems/rubygems/pull/7331) by mrkn ## Documentation: * Use squiggly heredocs in `Gem::Specification#description` documentation, so it doesn't add leading whitespace. Pull request [#7373](https://github.com/rubygems/rubygems/pull/7373) by bravehager # 3.5.4 / 2024-01-04 ## Enhancements: * Always avoid "Updating rubygems-update" message. Pull request [#7335](https://github.com/rubygems/rubygems/pull/7335) by deivid-rodriguez * Installs bundler 2.5.4 as a default gem. ## Bug fixes: * Make `gem update --system` respect ruby version constraints. Pull request [#7334](https://github.com/rubygems/rubygems/pull/7334) by deivid-rodriguez ------------------------------------------------------------------------------ RubyGems installed the following executables: /home/student/.asdf/installs/ruby/3.3.0/bin/gem /home/student/.asdf/installs/ruby/3.3.0/bin/bundle /home/student/.asdf/installs/ruby/3.3.0/bin/bundler Ruby Interactive (ri) documentation was installed. ri is kind of like man pages for Ruby libraries. You may access it like this: ri Classname ri Classname.class_method ri Classname#instance_method If you do not wish to install this documentation in the future, use the --no-document flag, or set it as the default in your ~/.gemrc file. See 'gem help env' for details. RubyGems system software updated |
You can confirm your Ruby install with two commands. First, use the which utility to check the Ruby install:
which -a ruby |
It should return:
/home/student/.asdf/shims/ruby |
Then, check the Ruby version:
ruby -v |
It should return:
ruby 3.3.0 (2023-12-25 revision 5124f9ac75) [x86_64-linux] |
Assuming you’ve installed and configured MySQL 8 on Ubuntu, you need this additional library to support the necessary Ruby Gem:
sudo apt-get install -y libmysqlclient-dev |
Now, you can install the current MySQL Ruby Gem:
gem install mysql2 |
You can now write a mysql_connection.rb program to verify a connection to the MySQL 8 database, like:
# Include Ruby Gem libraries. require 'rubygems' require 'mysql2' begin # Create new database connection. db = Mysql2::Client.new( :host => 'localhost' \ , :username => 'student' \ , :password => 'student' \ , :database => 'studentdb') # Create a result set. stmt = db.query('SELECT version() AS version') # Read through the result set hash. stmt.each do | row | puts "#{row['version']}" end # Release the result set resources. stmt.free rescue Mysql2::Error => e # Print the error. puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}" puts "Can't connect to the MySQL database specified." # Signal an error. exit 1 ensure # Close the connection when it is open. db.close if db end |
Call the program with this syntax:
ruby mysql_connection.rb |
It should return:
Connected to the MySQL database server. |
You can verify the version with this mysql_version.rb program:
# Include Ruby Gem libraries. require 'rubygems' require 'mysql2' begin # Create new database connection. db = Mysql2::Client.new( :host => 'localhost' \ , :username => 'student' \ , :password => 'student' \ , :database => 'studentdb') # Create a result set. rs = db.query('SELECT version() AS version') # Read through the result set hash. rs.each do | row | puts "#{row['version']}" end # Release the result set resources. rs.free rescue Mysql2::Error => e # Print the error. puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}" puts "Can't connect to the MySQL database specified." # Signal an error. exit 1 ensure # Close the connection when it is open. db.close if db end |
On Ubuntu, it should return:
8.0.35-0ubuntu0.22.04.1 |
If you don’t know anything about the mysql2 Ruby Gem, you should read the documentation. It’s very concise and requires a basic understanding of Ruby programming. The two specific pages who may want to check for the next examples are:
- The Mysql2 Statement Class list.
- The Mysql2 Result Class List
The mysql_version.rb version uses the known string literal for columns or column aliases returned by the SQL statement, which becomes the stmt (or statement) in the program. The next program eliminates the need to enumerate with the text-based columns from the query by using the Statement#fields array values by use of a numeric index. The numeric index returns the field names from the Statement#fields class to use in as the name for values in the Result#fields value found in the row variable of the for loop.
# Include Ruby Gem libraries. require 'rubygems' require 'mysql2' # Begin block. begin # Create a new connection resource. db = Mysql2::Client.new( :host => 'localhost' \ , :username => 'student' \ , :password => 'student' \ , :database => 'studentdb') # Create a result set. stmt = db.query("SELECT DISTINCT i.item_title, ra.rating " + \ "FROM item i INNER JOIN rating_agency ra " + \ "ON i.item_rating_id = ra.rating_agency_id " + \ "WHERE ra.rating_agency = 'MPAA'" + \ "ORDER BY 1") # Read through the result set hash. stmt.each do | row | out = "" i = 0 while i < stmt.fields.count() # Check when not last column and use the: # - Hash returned by the result set for the value, and # - String array value returned by the statement object # as the name value of the hash by leveraging its # numeric index. if i < stmt.fields.count() - 1 out += "#{row[stmt.fields[i]]}" out += ", " else out += "#{row[stmt.fields[i]]}" end i += 1 end puts "#{out}" end # Release the result set resources. stmt.free rescue Mysql2::Error => e # Print the error. puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}" puts "Can't connect to MySQL database specified." # Signal an error. exit 1 ensure # Close the connection when it is open. db.close if db end |
It returns the select two columns from the query:
A Man for All Seasons, G Around the World in 80 Days, G Beau Geste, PG Brave Heart, R Camelot, G Casino Royale, PG-13 ... Tomorrow Never Dies, PG-13 Tora! Tora! Tora!, G Tron, PG |
The following mysql_query_params.rb Ruby example accepts a single argument to leverage a wild card query in MySQL:
require 'rubygems' require 'mysql2' # Input external arguments. arguments = ARGV # Check for one input parameter and substitute an empty string # when one isn't found. if arguments.length == 1 argument = arguments[0] else argument = "" end # Begin block. begin # Create a new connection resource. db = Mysql2::Client.new( :host => 'localhost' \ , :username => 'student' \ , :password => 'student' \ , :database => 'studentdb') # Create a result set. stmt = db.prepare("SELECT DISTINCT i.item_title, ra.rating " + \ "FROM item i INNER JOIN rating_agency ra " + \ "ON i.item_rating_id = ra.rating_agency_id " + \ "WHERE ra.rating_agency = 'MPAA'" + \ "AND i.item_title LIKE CONCAT(?,'%')" + \ "ORDER BY 1") # Bind the variable into the query. rs = stmt.execute(argument) # Read through the result set hash. rs.each do | row | out = "" i = 0 while i < rs.fields.count() # Check when not last column and use the: # - Hash returned by the result set for the value, and # - String array value returned by the statement object # as the name value of the hash by leveraging its # numeric index. if i < rs.fields.count() - 1 out += "#{row[rs.fields[i]]}" out += ", " else out += "#{row[rs.fields[i]]}" end i += 1 end puts "#{out}" end # Release the result set resources. rs.free rescue Mysql2::Error => e # Print the error. puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}" puts "Can't connect to MySQL database specified." # Signal an error. exit 1 ensure # Close the connection when it is open. db.close if db end |
If you call the mysql_query_params.rb program with this syntax:
ruby mysql_aquery_params.rb Harry |
It’ll return the following from the studentdb database:
Harry Potter and the Chamber of Secrets, PG Harry Potter and the Deathly Hallows, Part 1, PG-13 Harry Potter and the Deathly Hallows, Part 2, PG-13 Harry Potter and the Goblet of Fire, PG-13 Harry Potter and the Half Blood Prince, PG Harry Potter and the Order of the Phoenix, PG-13 Harry Potter and the Prisoner of Azkaban, PG Harry Potter and the Sorcerer's Stone, PG |
After that, you should install Rails (check for current version beyond 1/2024). Install Ruby Global with this syntax:
gem install rails -v 7.1.3 |
Check the version installed:
rails -v |
It should return:
Rails 7.1.3 |
Run this command to enable Rails for MySQL 8:
rails new myapp -d mysql |
If you want to configure a username and password for MySQL, edit the config/database.yml file.
As always, I hope this helps somebody looking for step-by-step guide.
Subscribe via RSS