Restoring vcdb3 - Revision history

Ed at 17:41, 21 September 2020

2020-09-21T17:41:46Z

Ed at 17:34, 21 September 2020

2020-09-21T17:34:21Z

Ed: /* Fix the Geocoding */

2019-09-03T19:59:15Z

Fix the Geocoding

Ed at 20:09, 13 June 2019

2019-06-13T20:09:59Z

Ed: /* Company Process */

2019-05-30T23:20:49Z

Company Process

Ed: /* Fix the Geocoding */

2019-05-30T19:28:02Z

Fix the Geocoding

Ed: /* Fix the Geocoding */

2019-05-30T19:05:16Z

Fix the Geocoding

Ed at 18:13, 30 May 2019

2019-05-30T18:13:33Z

Ed at 15:16, 30 May 2019

2019-05-30T15:16:52Z

Ed: Created page with "{{Project |Has title=Restoring vcdb3 |Has owner=Ed Egan, |Has project status=Complete }} ==Restore the dbase== Run the following: cd /bulk/mcnair/backups su researcher..."

2019-03-17T17:43:54Z

Created page with "{{Project |Has title=Restoring vcdb3 |Has owner=Ed Egan, |Has project status=Complete }} ==Restore the dbase== Run the following: cd /bulk/mcnair/backups su researcher..."

New page

{{Project
|Has title=Restoring vcdb3
|Has owner=Ed Egan,
|Has project status=Complete
}}
==Restore the dbase==

Run the following:
cd /bulk/mcnair/backups
su researcher
createdb vcdb3
pg_restore -Fc -d vcdb3 vcdb3_Fc_230818.dump
cd /bulk
mkdir vcdb3
cd vcdb3
psql vcdb3

==Reload the last changes==

#make vcdb3 in E:\projects
#copy C:\Users\ed\Dropbox\coauthoredprojects\NonMcNair\MatchingVCs\RevisedDbaseCode.sql to e:\projects\vcdb3
#Run that code!
1.6m queries running in <7s
index on 1.6m in <1s
Big table (99m) used to take ~2mins, now takes 2:15. Might want to reoptimize config.
Big reduction used to take 12mins. Now takes 55 secs!
In later queries (not as big), allocated cpu 97-100% but mem only 3.2%
Copy up to 3.6% mem usage. Max observed 4.3%.
'''All of the code ran correctly, with the right counts, etc.'''

==Consolidate code==

Code was in:
E:\mcnair\Projects\VentureXpert Database\vcdb3\LoadingScripts

The following files were copied to E:\projects\vcdb3\OriginalSQL
*Adding GDP.sql
*Agglomeration.SQL - this is the agglomeration work
*CohortPortCoMatch.sql
*GeoLoad.sql
*LoadingScriptsV1.sql - this does the load!
*MatchingEntrepsV3.sql - this is the main build, after the load
*MiscTasks.SQL
*Population.sql
*PortcoMatchMaster.SQL
*RoundRanking.sql - this is the city ranking script
*RoundRankingStates.sql - this is the state ranking script

Note that all code, including ranking code was run before backup.

@@ Line 56: / Line 56: @@
 ==Fix the Geocoding==
-The longitudes and latitudes were stored as numeric, rather than real, but this doesn't seem to the source of the problem as it has suitable precision[https://www.postgresql.org/docs/current/datatype-numeric.html].
+The longitudes and latitudes were stored as numeric, rather than real, but this doesn't seem to the source of the problem as it has suitable precision[https://www.postgresql.org/docs/current/datatype-numeric.html]. Instead the problem seems to be in the loading and unloading of tables through the \COPY command.
 There are some notes on [[VentureXpert_Data#GeoCoding_Companies.2C_Firms.2C_and_Branch_Offices]] and the SQL appears to be in:

@@ Line 72: / Line 72: @@
 The fix to the geo data is at the top of:
   E:\projects\vcdb3\RevisedDbaseCode.sql
 ===Firm Process===

@@ Line 63: / Line 63: @@
 ===Company Process===
-The company process began with a load of oldgeocords 44740 from companybasegeomaster.txt (which came from vcdb2). The problem is that companybasegeomaster has low precision data in it. This was documented in [[VC_Database_Rebuild#Re-Fixing_erroneous_geo-coordinates]]. There's good data, but keyed solely by companyname, in vcdb2:
+The company process began with a load of oldgeocords 44740 from companybasegeomaster.txt (which came from vcdb2). The problem is that companybasegeomaster has low precision data in it. This was documented in [[VC_Database_Rebuild#Re-Fixing_erroneous_geo-coordinates]]. There's good data in vcdb2:
-*Geocore 43628 (with nulls)
+*Geocore 43628 (with nulls), keyed by coname (colevelsimple)
-*Geoallcoords 44999 (with nulls)
+*Geoallcoords 44999 (with nulls), keyed by coname, datefirst, statecode
-*Geoallcoords1 44999 (with nulls)
+*Geoallcoords1 44999 (with nulls), keyed by coname, datefirst, statecode, with exclude flag
 The next step in the company geo process was to load remaining from RemainingLatLong.txt. This is high precision data.

← Older revision		Revision as of 19:28, 30 May 2019
Line 70:		Line 70:
	The next step in the company geo process was to load remaining from RemainingLatLong.txt. This is high precision data.		The next step in the company geo process was to load remaining from RemainingLatLong.txt. This is high precision data.

		+	The fix to the geo data is at the top of:
		+	E:\projects\vcdb3\RevisedDbaseCode.sql

	===Firm Process===		===Firm Process===

@@ Line 60: / Line 60: @@
 There are some notes on [[VentureXpert_Data#GeoCoding_Companies.2C_Firms.2C_and_Branch_Offices]] and the SQL appears to be in:
   E:\mcnair\Projects\VentureXpert Database\vcdb3\LoadingScripts\GeoLoad.sql
 The company process began with a load of oldgeocords 44740 from companybasegeomaster.txt (which came from vcdb2). The problem is that companybasegeomaster has low precision data in it. This was documented in [[VC_Database_Rebuild#Re-Fixing_erroneous_geo-coordinates]]. There's good data, but keyed solely by companyname, in vcdb2:
@@ Line 67: / Line 69: @@
 The next step in the company geo process was to load remaining from RemainingLatLong.txt. This is high precision data.

@@ Line 1: / Line 1: @@
 {{Project
-|Has project output=
+|Has project output=Data
 |Has title=Restoring vcdb3
 |Has owner=Ed Egan,

← Older revision		Revision as of 18:13, 30 May 2019
Line 53:		Line 53:

	Note that all code, including ranking code was run before backup.		Note that all code, including ranking code was run before backup.
		+
		+	==Fix the Geocoding==
		+
		+	The longitudes and latitudes were stored as numeric, rather than real, but this doesn't seem to the source of the problem as it has suitable precision[https://www.postgresql.org/docs/current/datatype-numeric.html].
		+
		+	There are some notes on [[VentureXpert_Data#GeoCoding_Companies.2C_Firms.2C_and_Branch_Offices]] and the SQL appears to be in:
		+	E:\mcnair\Projects\VentureXpert Database\vcdb3\LoadingScripts\GeoLoad.sql
		+
		+	The company process began with a load of oldgeocords 44740 from companybasegeomaster.txt (which came from vcdb2). The problem is that companybasegeomaster has low precision data in it. This was documented in [[VC_Database_Rebuild#Re-Fixing_erroneous_geo-coordinates]]. There's good data, but keyed solely by companyname, in vcdb2:
		+	*Geocore 43628 (with nulls)
		+	*Geoallcoords 44999 (with nulls)
		+	*Geoallcoords1 44999 (with nulls)
		+
		+	The next step in the company geo process was to load remaining from RemainingLatLong.txt. This is high precision data.

@@ Line 4: / Line 4: @@
 |Has project status=Complete
 }}
 ==Restore the dbase==