220
edits
Changes
Jump to navigation
Jump to search
→Cleaning geo table
==Cleaning geo table==
The geo table contains duplicate keys. The key for the geo table is (coname, city, startyear). Look at the different counts for select all keys and distinct keys from the table:
SELECT COUNT(*) FROM (SELECT DISTINCT city, coname, startyear FROM geo)a;
--43651
HAVING COUNT(*) > 1;
--3
Now manually check the longitude and latitude of each of these rows and delete one of each of them. Then create your core table and verify that all the keys are distinct.
DELETE FROM geo1 WHERE coname = 'New York Digital Health LLC' AND city = 'New York' AND startyear = 2015 AND lattitude = 44.933143::real
AND longitude = 7.540121::real;
--1
DELETE FROM geo1 WHERE coname = 'Mdeverywhere Inc' AND city = 'Hauppauge' AND endyear = 2011;
--1
DELETE FROM geo1 WHERE city = 'North Mankato' AND lattitude = 44.19030721::real AND longitude = -94.052706::real;
--1
CREATE TABLE geocore AS
SELECT *
FROM geo1;
--43628
SELECT COUNT(*) FROM (SELECT DISTINCT city, coname, startyear FROM geocore)a;
--43628
==Name Based Matching geo keys to companybase keys==
