PostGIS Installation - Revision history

Ed: /* Tiger Geocoder Extension */

2019-10-08T17:39:15Z

Tiger Geocoder Extension

Peterjalbert at 20:37, 31 October 2017

2017-10-31T20:37:11Z

Peterjalbert at 20:59, 30 October 2017

2017-10-30T20:59:44Z

Peterjalbert at 20:59, 30 October 2017

2017-10-30T20:59:07Z

OliverC: /* Installation Commands */

2017-10-25T21:59:32Z

Installation Commands

Peterjalbert at 19:46, 25 October 2017

2017-10-25T19:46:07Z

Peterjalbert at 20:56, 24 October 2017

2017-10-24T20:56:51Z

Peterjalbert at 20:39, 24 October 2017

2017-10-24T20:39:31Z

Peterjalbert at 20:39, 24 October 2017

2017-10-24T20:39:08Z

Ed: /* Translating Table names to corresponding States */

2017-09-29T22:19:42Z

Translating Table names to corresponding States

← Older revision		Revision as of 17:39, 8 October 2019
Line 312:		Line 312:
	[https://postgis.net/docs/postgis_installation.html#install_tiger_geocoder_extension This link] outlines the process to enable our Postgres Database to support Tiger functionality.		[https://postgis.net/docs/postgis_installation.html#install_tiger_geocoder_extension This link] outlines the process to enable our Postgres Database to support Tiger functionality.

−	This documentation has been moved to the [~~http://mcnair.bakerinstitute.org/wiki/Tiger_Geocoder~~ Tiger Geocoder] wiki page.	+	This documentation has been moved to the [[Tiger Geocoder]] wiki page.

← Older revision		Revision as of 20:37, 31 October 2017
Line 312:		Line 312:
	[https://postgis.net/docs/postgis_installation.html#install_tiger_geocoder_extension This link] outlines the process to enable our Postgres Database to support Tiger functionality.		[https://postgis.net/docs/postgis_installation.html#install_tiger_geocoder_extension This link] outlines the process to enable our Postgres Database to support Tiger functionality.

−	~~I began by adding the extension listed above. First, enter into Postgres by using the psql command. Then:~~	+	This documentation has been moved to the [http://mcnair.bakerinstitute.org/wiki/Tiger_Geocoder Tiger Geocoder] wiki page.
−	~~--Add Extensions to database~~
−	~~CREATE EXTENSION postgis;~~
−	~~CREATE EXTENSION fuzzystrmatch;~~
−	~~CREATE EXTENSION postgis_tiger_geocoder;~~
−	~~CREATE EXTENSION address_standardizer;~~
−
−	~~You can test that the installation worked by running the following query:~~
−	~~SELECT na.address, na.streetname,na.streettypeabbrev, na.zip~~
−	~~FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;~~
−
−	This ~~should return the following:~~
−	~~address \| streetname \| streettypeabbrev \| zip~~
−	~~---------+------------+------------------+-------~~
−	~~1 \| Devonshire \| Pl \| 02109~~
−
−	~~Next, a new profile needs~~ to ~~be created by using the following command.~~
−	~~INSERT INTO tiger.loader_platform(os, declare_sect, pgbin, wget, unzip_command, psql, path_sep,~~
−	~~loader, environ_set_command, county_process_command)~~
−	~~SELECT 'newuser', declare_sect, pgbin, wget, unzip_command, psql, path_sep,~~
−	~~loader, environ_set_command, county_process_command~~
−	~~FROM tiger.loader_platform~~
−	~~WHERE os = 'sh';~~
−
−	~~The installation instructions also provide the following note:~~
−
−	As of PostGIS 2.4.1 the Zip code-5 digit tabulation area zcta5 load step was revised to load current zcta5 data and is part of the Loader_Generate_Nation_Script when enabled. It is turned off by default because it takes quite a bit of time to load (20 to 60 minutes), takes up quite a bit of disk space, and is not used that often.
−
−	~~If you would like this feature, you can enable it by using the following command. This should be done before loading the script.~~
−
−	~~UPDATE tiger.loader_lookuptables SET load = true WHERE table_name = 'zcta510';~~
−
−	~~The paths in declare_sect need to be edited so they match our server locations. One option is to edit the declare_sect column in the tiger.loader_platform table. If so, the declare_sect looks like~~ the ~~following~~:
−	~~export PGHOST=localhost +~~
−	~~export PGUSER=postgres +~~
−	~~export PGPASSWORD=yourpasswordhere +~~
−	~~export PGDATABASE=geocoder +~~
−	~~PSQL=${PGBIN}/psql +~~
−	~~SHP2PGSQL=shp2pgsql +~~
−	~~cd ${staging_fold} +~~
−
−	~~TMPDIR="${staging_fold}/temp/" +~~
−	~~UNZIPTOOL=unzip +~~
−	~~WGETTOOL="~~/~~usr~~/~~bin/wget" +~~
−	~~export PGBIN=/usr/lib/postgresql/9~~.~~6/bin +~~
−	~~export PGPORT=5432 +~~
−	~~export PGHOST=localhost +~~
−	~~export PGUSER=postgres +~~
−	~~export PGPASSWORD=yourpasswordhere +~~
−	~~export PGDATABASE=geocoder +~~
−	~~PSQL=${PGBIN}/psql +~~
−	~~SHP2PGSQL=shp2pgsql +~~
−	~~cd ${staging_fold}~~
−
−	~~Another option is to edit the sh file before running the script~~. ~~The downloaded script is located in the following directory:~~
−	/~~gisdata~~
−
−	~~There needs to be a directory called "temp" in the gisdata directory. To make the script, use the following from the command line:~~
−	~~psql -c "SELECT Loader_Generate_Nation_Script('debbie')" -d databasename -tA >~~ /~~gisdata/nation_script_load.sh~~
−
−	This will create a script in the gisdata directory. Change to that directory. If you did not edit the paths in the declare_sect table in psql, then you will need to edit this file to contain the correct paths. Run the script by using:
−	~~sh nation_script_load.sh~~
−
−	~~We changed the PGUSER and PGPASSWORD fields to:~~
−	~~PGUSER=postgres~~
−	~~PGPASSWORD=(Ask Anne for this password)!~~
−	~~Everything else remains the same~~.

@@ Line 370: / Line 370: @@
 There needs to be a directory called "temp" in the gisdata directory. To make the script, use the following from the command line:
-  psql -c "SELECT Loader_Generate_Nation_Script('debbie')" -d geocoder -tA > /gisdata/nation_script_load.sh
+  psql -c "SELECT Loader_Generate_Nation_Script('debbie')" -d databasename -tA > /gisdata/nation_script_load.sh
 This will create a script in the gisdata directory. Change to that directory. If you did not edit the paths in the declare_sect table in psql, then you will need to edit this file to contain the correct paths. Run the script by using:

← Older revision		Revision as of 20:59, 30 October 2017
Line 374:		Line 374:
	This will create a script in the gisdata directory. Change to that directory. If you did not edit the paths in the declare_sect table in psql, then you will need to edit this file to contain the correct paths. Run the script by using:		This will create a script in the gisdata directory. Change to that directory. If you did not edit the paths in the declare_sect table in psql, then you will need to edit this file to contain the correct paths. Run the script by using:
	sh nation_script_load.sh		sh nation_script_load.sh
		+
		+	We changed the PGUSER and PGPASSWORD fields to:
		+	PGUSER=postgres
		+	PGPASSWORD=(Ask Anne for this password)!
		+	Everything else remains the same.

@@ Line 17: / Line 17: @@
 $ sudo apt-get install --no-install-recommends postgis</nowiki>
-Note the last line. Without the <code>--no-install-recommends</code> line postgres assumes you're using the default configuration (in this case, postgis for postgresql 9.6). Since we're using a different flavor of postgis, we do not want this.
+Note the last line. Without the <code>--no-install-recommends</code> line postgres assumes you're using the default configuration (in this case, postgis for postgresql 9.6). Since we're using a different flavor of postgresql, we do not want this.
 The <code>postgis</code> apt package is not the extension to postgres. It is a collection of command line utilities for importing and exporting data to a postgis db.

@@ Line 336: / Line 336: @@
     WHERE os = 'sh';
-The paths in declare_sect need to be edited so they match our server locations.
+The installation instructions also provide the following note:
-The declare_sect looks like the following:
   export PGHOST=localhost                       +
   export PGUSER=postgres                        +
@@ Line 358: / Line 365: @@
   SHP2PGSQL=shp2pgsql                           +
   cd ${staging_fold}

← Older revision		Revision as of 20:56, 24 October 2017
Line 327:		Line 327:
	---------+------------+------------------+-------		---------+------------+------------------+-------
	1 \| Devonshire \| Pl \| 02109		1 \| Devonshire \| Pl \| 02109
		+
		+	Next, a new profile needs to be created by using the following command.
		+	INSERT INTO tiger.loader_platform(os, declare_sect, pgbin, wget, unzip_command, psql, path_sep,
		+	loader, environ_set_command, county_process_command)
		+	SELECT 'newuser', declare_sect, pgbin, wget, unzip_command, psql, path_sep,
		+	loader, environ_set_command, county_process_command
		+	FROM tiger.loader_platform
		+	WHERE os = 'sh';
		+
		+	The paths in declare_sect need to be edited so they match our server locations.
		+	The declare_sect looks like the following:
		+	export PGHOST=localhost +
		+	export PGUSER=postgres +
		+	export PGPASSWORD=yourpasswordhere +
		+	export PGDATABASE=geocoder +
		+	PSQL=${PGBIN}/psql +
		+	SHP2PGSQL=shp2pgsql +
		+	cd ${staging_fold} +
		+
		+	TMPDIR="${staging_fold}/temp/" +
		+	UNZIPTOOL=unzip +
		+	WGETTOOL="/usr/bin/wget" +
		+	export PGBIN=/usr/lib/postgresql/9.6/bin +
		+	export PGPORT=5432 +
		+	export PGHOST=localhost +
		+	export PGUSER=postgres +
		+	export PGPASSWORD=yourpasswordhere +
		+	export PGDATABASE=geocoder +
		+	PSQL=${PGBIN}/psql +
		+	SHP2PGSQL=shp2pgsql +
		+	cd ${staging_fold}

@@ Line 321: / Line 321: @@
 You can test that the installation worked by running the following query:
   SELECT na.address, na.streetname,na.streettypeabbrev, na.zip
-	FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
+ 	FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
 This should return the following:

← Older revision		Revision as of 20:39, 24 October 2017
Line 306:		Line 306:
	\| Virgin Islands		\| Virgin Islands
	\|}		\|}
		+
		+	==Tiger Geocoder Extension==
		+	This section details the process to install and use the Tiger Geocoder Extension of PostGIS. The official docmentation can be found [https://postgis.net/docs/Extras.html here].
		+
		+	[https://postgis.net/docs/postgis_installation.html#install_tiger_geocoder_extension This link] outlines the process to enable our Postgres Database to support Tiger functionality.
		+
		+	I began by adding the extension listed above. First, enter into Postgres by using the psql command. Then:
		+	--Add Extensions to database
		+	CREATE EXTENSION postgis;
		+	CREATE EXTENSION fuzzystrmatch;
		+	CREATE EXTENSION postgis_tiger_geocoder;
		+	CREATE EXTENSION address_standardizer;
		+
		+	You can test that the installation worked by running the following query:
		+	SELECT na.address, na.streetname,na.streettypeabbrev, na.zip
		+	FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
		+
		+	This should return the following:
		+	address \| streetname \| streettypeabbrev \| zip
		+	---------+------------+------------------+-------
		+	1 \| Devonshire \| Pl \| 02109

← Older revision		Revision as of 22:19, 29 September 2017
Line 65:		Line 65:

	See: https://www.census.gov/geo/reference/ansi_statetables.html		See: https://www.census.gov/geo/reference/ansi_statetables.html
		+	(note that the numbers are FIPS state numeric codes)

	{\| class="wikitable"		{\| class="wikitable"