7,612
edits
Changes
Jump to navigation
Jump to search
no edit summary
{{Project
|Has project output=Data
|Has title=Restoring vcdb3
|Has owner=Ed Egan,
==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]. Instead the problem seems to be in the loading and unloading of tables through the \COPY command. Regardless, we have decimal degrees to six decimal places from Google Maps, which is equivalent to a staggering 11cm of accuracy at the equator. See http://wiki.gis.com/wiki/index.php/Decimal_degrees.
There are some notes on [[VentureXpert_Data#GeoCoding_Companies.2C_Firms.2C_and_Branch_Offices]] and the SQL appears to be in:
===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:*Geocore 43628 (with nulls), keyed by coname (colevelsimple)*Geoallcoords 44999 (with nulls), keyed by coname, datefirst, statecode*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.
The fix to the geo data is at the top of:
E:\projects\vcdb3\RevisedDbaseCode.sql
Essentially this fix:
*DROPs portcogeo; oldgeocords; geoallcoords; geoallcoords1; goodgeoold; geoallcoords2; geoallcoords3; goodgeonew; geocodesportco;
*Load geoallcoords1fromvcdb2 (which comes from vcdb2!)
*Merge the results of '''geoallcoords1fromvcdb2''' with the resutls of '''remaining'''
*Mark the out-of-bounds exclusions
*Produce '''portcogeo''' 47715
===Firm Process===
