Anyone imported GeoNames data into Qlikview?

[#1]

I'm wondering if anyone has imported the GeoNames geocoding data into Qlikview yet?

Ref:
GeoNames

Downloads available at:

http://www.geonames.org/export/

GeoNames Geocoding data is free under the Creative Commons license.

I downloaded the CSV flat files and loaded in the GeoNamesUS, PostalCodesUS, TimeZone, AdminCodes and FeatureCodes and dumped them to QVD's. But I'm having trouble getting things to "link up" correctly. When I try to link the GeoNamesUS and PostalCodes tables the city/state/latitude/longitude fields aren't matching up.

I basically have a link table to link our account file to to get the Latitude/Longitude for a city/state/zip - for eventually using for plotting on Google maps. If anyone is really interested in the QVD generator I have done so far for this I'm attaching it.

Here is my QVD load for the GeoNamesUS data from my QVD generator app (QVW):

[GeoNameUS]:

LOAD

     @1 as [GeoNameID], // integer id of record in geonames database

     @2 as [GeoPointName], //name of geographical point (utf8) varchar(200)

     @3 as [AsciiName], // name of geographical point in plain ascii characters, varchar(200)

     @4 as [AlternameNames], // alternatenames, comma separated varchar(5000)

     @5 as [Latitude], // latitude in decimal degrees (wgs84)

     @6 as [Longitude], // longitude in decimal degrees (wgs84)

     @7 as [FeatureClass], // see http://www.geonames.org/export/codes.html, char(1)

     @8 as [FeatureCode], // see http://www.geonames.org/export/codes.html, varchar(10)

     @9 as [CountryCode], // ISO-3166 2-letter country code, 2 characters

     @10 as [CountryCode2], // alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters

     @11 as [State], // [AdminCode1] fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)

     @12 as [AdminCode2], // code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80) 

     @13 as [AdminCode3], // code for third level administrative division, varchar(20)

     @14 as [AdminCode4], // code for fourth level administrative division, varchar(20)

     @15 as [Population], // Population - bigint (8 byte int)

     @16 as [Elevation], // Elevation - in meters, integer

     @17 as [DigitalElevationModel], // digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.

     @18 as [TimeZoneID], // the timezone id (see file timeZone.txt) varchar(40)

     @19 as [ModificationDate] // date of last modification in yyyy-MM-dd format

FROM

[Data\GeoData\GeoNames-US.txt]

(txt, utf8, no labels, delimiter is '\t', msq);

STORE [GeoNameUS] into QVDs\GeoData\GeoNameUS.qvd(qvd);

DROP Table [GeoNameUS];

Here is my QVD load for the PostalCodesUS data from my QVD generator app (QVW):

[PostalCodesUS]:

LOAD

     @1 as [CountryCode], // iso country code, 2 characters

     @2 as [PostalCode], // varchar(20)

     @3 as [PlaceName],  // varchar(180)

     @4 as [StateName], // 1. (AdminName1) - order subdivision (state) varchar(100)

     @5 as [StateCode], // 1. (AdminCode1) - order subdivision (state) varchar(20)

     @6 as [CountyProviceName], // 2. (AdminName2) - order subdivision (county/province) varchar(100)

     @7 as [CountyProviceCode], // 2. (AdminCode2) - order subdivision (county/province) varchar(20)

     @8 as [CommunityName], // 3. (AdminName3) - order subdivision (community) varchar(100)

     @9 as [CommunityCode], // 3. ([AdminCode3) - order subdivision (community) varchar(20)

     @10 as [Latitude], // estimated latitude (wgs84)

     @11 as [Longitude], // estimated longitude (wgs84)

     @12 as [Accuracy] // accuracy of lat/lng from 1=estimated to 6=centroid

FROM

[Data\GeoData\PostalCodes-US.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

STORE [PostalCodesUS] into QVDs\GeoData\PostalCodesUS.qvd(qvd);

DROP Table [PostalCodesUS];

This is my Linktable from my actual app (QVW) - this is so I have a "link" between my GeoNamesUS and PostalCodesUS tables as they have several fields in common. The "GeoCodeKey" is created the same in the GeoNamesUS and PostalCodesUS QVD loads.

What I am hoping to happen is have a "link table" that has CountryCode, State,City, Latitude, Longitude in it - but the resulting table doesn't link up.

[LinkTable]:

LOAD 

     [CountryCode] & '|' & [State]  & '|' & [Latitude] & '|' & [Longitude]  as [GeoCodeKey],

     [Latitude],

     [Longitude],

     [CountryCode],

     [State] as [StateCode]

FROM

QVDs\GeoData\GeoNameUS.qvd (qvd);

Concatenate ([LinkTable])

LOAD

     //CountryCode,

     //PostalCode,

     [PlaceName] as [City],

     //StateName,

     [StateCode]

     //CountyProviceName,

     //CountyProviceCode,

     //CommunityName,

     //CommunityCode,

     //Latitude,

     //Longitude,

     //Accuracy

FROM

QVDs\GeoData\PostalCodesUS.qvd (qvd);

Does anyone have an idea for getting the city/state/latitude/longitude to link up correctly? I know I'm missing something - jsut not quite seeing it.

TIA

[Josh_Good · Accepted solution]

I think you are over complicating things - I don't think you need a link table.  Lat & Long for a state or admin division will be different from the Lat & Long for a specific postcode.  I would recommend using lat & long from only one of the tables and connecting them on an admin division (such as state).

Nice QVD Generator!

[#3]

Thanks, I ended up just using the PostalCode-US file. Then created the link table to link our accounts table to it. But I have found that the GeoNames data is missing quite a lot of city/state/zip combinations (especially from IA and MO). I took several of them and found the GNIS records for them. I'll have to find a way to somehow automate GNIS lookups to get the data to submit back to GeoNames.

[Josh_Good]

HI Richard,

Here is some code that will take addresses and loop them through google to get lat and long.  It will work if you just enter zips.

-Josh

Table1:

LOAD RowNo() as ContactID,

     Title,

     [First Name],

     [Middle Name],

     [Last Name],

     Suffix,

     Company,

     Department,

     [Job Title],

     [Business Street],

     [Business Street 2],

     [Business Street 3],

     [Business City],

     [Business State],

     [Business Postal Code],

     [Business Country/Region],

     [Business Street] & ' ' & [Business Street 2] & ' ' & [Business Street 3] & ' ' & [Business City] & ', ' & [Business State] & ', ' &  [Business Country/Region] & ', ' & [Business Postal Code] as FullAddress

FROM

contacts.CSV

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//Find out the number of address to be processed.  In this case it is from "Table1" (lines 24-44)

let noRows = NoOfRows('Table1')-1;

//For each row loop through an find the lat and long of the address where i will be the row we are looking at (note in QlikView the first row is 0

for i=0 to $(noRows)

    //Select the ContactID from the current row of Table1 and assign the value to the variable 'd'

    let d=peek('ContactID',$(i),'Table1');

    //Select the FullAddress from the current row of Table1 and assign the value to the variable 'address'

    let address=peek('FullAddress',$(i),'Table1');   

    //Create a table called "Data".  This table will have the ContactId, address from the variable.  It will also have the lat and long returned from the google api.  Note the "FROM" statement is dynamic because it includes the variable 'address'

    //This table will concatenate on to the Data table with each iteration because QlikView concatenates tables that have the exact same fields in them.

    Data:

    LOAD

    '$(d)' as ContactID,

    '$(address)' as Address,

    subfield([Response/Placemark/Point/coordinates], ',' ,1) AS longitude,

    subfield([Response/Placemark/Point/coordinates], ',' ,2) AS latitude

    FROM [http://maps.google.com/maps/geo?q=$(address)&output=xml&oe=utf8&sensor=false&key=XXYYZZ] (XmlSimple, Table is [kml]);

next

[amonjaras]

Yes, I downloaded and used the zip codes for Mexico some time ago, and found several errors in the latitude and longitude figures (something like 5% out of the total).

I consider GeoNames a good, cheap source for coordinates to use in SiBs or simple apps, but for anything else you need to make sure the coordinates are right. Josh's method looks like a good way to "patch up" erroneous or missing coordinates as you find them.