Skip to content

Instantly share code, notes, and snippets.

@dr5hn

dr5hn/go.mod

Forked from caner-cetin/go.mod
Created January 24, 2024 08:43
Show Gist options
  • Save dr5hn/adafd3980cd2b0211b519ddcda67b24d to your computer and use it in GitHub Desktop.
Save dr5hn/adafd3980cd2b0211b519ddcda67b24d to your computer and use it in GitHub Desktop.
Download ZIP
World Data Fix Script on Golang
Raw
go.mod
module cityconv
go 1.20
require github.com/jackc/pgx/v5 v5.4.1
require (
github.com/jackc/pgpassfile v1.0.0 // indirect
github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
golang.org/x/crypto v0.9.0 // indirect
golang.org/x/text v0.9.0 // indirect
)
Raw
main.go
package main
import (
"context"
"encoding/json"
"github.com/jackc/pgx/v5"
"os"
"strconv"
"time"
)
type State struct {
ID int `json:"id"`
Name string `json:"name"`
CountryID int `json:"country_id"`
CountryCode string `json:"country_code"`
CountryName string `json:"country_name"`
StateCode string `json:"state_code"`
Type any `json:"type"`
Latitude string `json:"latitude"`
Longitude string `json:"longitude"`
}
type States[] State
type StateInDB struct {
ID uint32 `json:"id" db:"id"`
Name string `json:"name" db:"name"`
CountryID uint32 `json:"country_id" db:"country_id"`
CountryCode string `json:"country_code" db:"country_code"`
Type string `json:"type" db:"type"`
Latitude float64 `json:"latitude" db:"latitude"`
Longitude float64 `json:"longitude" db:"longitude"`
CreatedAt time.Time `json:"created_at" db:"created_at"`
UpdatedAt time.Time `json:"updated_at" db:"updated_at"`
Flag bool `json:"flag" db:"flag"`
WikiDataID string `json:"wikiDataId" db:"wiki_data_id"`
}
type StatesInDB[] StateInDB
type CountryTimezone struct {
ID int `json:"id" db:"id"`
ZoneName string `json:"zoneName" db:"zone_name"`
GmtOffset int `json:"gmtOffset" db:"gmt_offset"`
GmtOffsetName string `json:"gmtOffsetName" db:"gmt_offset_name"`
Abbreviation string `json:"abbreviation" db:"abbreviation"`
TzName string `json:"tzName" db:"tz_name"`
}
type CountryTimezones[] CountryTimezone
type CountryInDB struct {
ID uint32 `json:"id" db:"id"`
Name string `json:"name" db:"name"`
ISO3 string `json:"iso3,omitempty" db:"iso3"`
NumericCode string `json:"numeric_code,omitempty" db:"numeric_code"`
ISO2 string `json:"iso2,omitempty" db:"iso2"`
PhoneCode string `json:"phonecode,omitempty" db:"phonecode"`
Capital string `json:"capital,omitempty" db:"capital"`
Currency string `json:"currency,omitempty" db:"currency"`
CurrencyName string `json:"currency_name,omitempty" db:"currency_name"`
CurrencySymbol string `json:"currency_symbol,omitempty" db:"currency_symbol"`
TLD string `json:"tld,omitempty" db:"tld"`
Native string `json:"native,omitempty" db:"native"`
Region string `json:"region,omitempty" db:"region"`
Subregion string `json:"subregion,omitempty" db:"subregion"`
TimezoneID[] uint32 `json:"timezone_id,omitempty" db:"timezone_id"`
Translations string `json:"translations,omitempty" db:"translations"`
Latitude float64 `json:"latitude,omitempty" db:"latitude"`
Longitude float64 `json:"longitude,omitempty" db:"longitude"`
Emoji string `json:"emoji,omitempty" db:"emoji"`
EmojiU string `json:"emojiU,omitempty" db:"emojiU"`
CreatedAt time.Time `json:"created_at,omitempty" db:"created_at"`
UpdatedAt time.Time `json:"updated_at" db:"updated_at"`
}
type CountriesInDB[] CountryInDB
type Country struct {
ID int `json:"id"`
Name string `json:"name"`
Iso3 string `json:"iso3"`
Iso2 string `json:"iso2"`
NumericCode string `json:"numeric_code"`
PhoneCode string `json:"phone_code"`
Capital string `json:"capital"`
Currency string `json:"currency"`
CurrencyName string `json:"currency_name"`
CurrencySymbol string `json:"currency_symbol"`
Tld string `json:"tld"`
Native string `json:"native"`
Region string `json:"region"`
Subregion string `json:"subregion"`
Timezones CountryTimezones
Translations struct {
Kr string `json:"kr"`
PtBR string `json:"pt-BR"`
Pt string `json:"pt"`
Nl string `json:"nl"`
Hr string `json:"hr"`
Fa string `json:"fa"`
De string `json:"de"`
Es string `json:"es"`
Fr string `json:"fr"`
Ja string `json:"ja"`
It string `json:"it"`
Cn string `json:"cn"`
Tr string `json:"tr"`
}
`json:"translations,omitempty"`
Latitude string `json:"latitude"`
Longitude string `json:"longitude"`
Emoji string `json:"emoji"`
EmojiU string `json:"emojiU"`
Translations0 struct {
Kr string `json:"kr"`
PtBR string `json:"pt-BR"`
Pt string `json:"pt"`
Fa string `json:"fa"`
De string `json:"de"`
Fr string `json:"fr"`
It string `json:"it"`
Cn string `json:"cn"`
Tr string `json:"tr"`
}
`json:"translations,omitempty"`
Translations1 struct {
Kr string `json:"kr"`
PtBR string `json:"pt-BR"`
Pt string `json:"pt"`
Nl string `json:"nl"`
Fa string `json:"fa"`
De string `json:"de"`
Fr string `json:"fr"`
It string `json:"it"`
Cn string `json:"cn"`
Tr string `json:"tr"`
}
`json:"translations,omitempty"`
Translations2 struct {
Kr string `json:"kr"`
PtBR string `json:"pt-BR"`
Pt string `json:"pt"`
Nl string `json:"nl"`
Hr string `json:"hr"`
Fa string `json:"fa"`
De string `json:"de"`
Es string `json:"es"`
Fr string `json:"fr"`
Ja string `json:"ja"`
Cn string `json:"cn"`
Tr string `json:"tr"`
}
`json:"translations,omitempty"`
Translations3 struct {
Kr string `json:"kr"`
Cn string `json:"cn"`
Tr string `json:"tr"`
}
`json:"translations,omitempty"`
Translations4 struct {
Kr string `json:"kr"`
PtBR string `json:"pt-BR"`
Pt string `json:"pt"`
Nl string `json:"nl"`
Fa string `json:"fa"`
De string `json:"de"`
Fr string `json:"fr"`
It string `json:"it"`
Cn string `json:"cn"`
Tr string `json:"tr"`
}
`json:"translations,omitempty"`
Translations5 struct {
Kr string `json:"kr"`
PtBR string `json:"pt-BR"`
Pt string `json:"pt"`
Nl string `json:"nl"`
Fa string `json:"fa"`
De string `json:"de"`
Es string `json:"es"`
Fr string `json:"fr"`
Ja string `json:"ja"`
It string `json:"it"`
Cn string `json:"cn"`
Tr string `json:"tr"`
}
`json:"translations,omitempty"`
}
type Countries[] Country
type City struct {
ID uint32 `json:"id" db:"id"`
Name string `json:"name" db:"name"`
StateID uint32 `json:"state_id" db:"state_id"`
StateCode string `json:"state_code" db:"state_code"`
CountryID uint32 `json:"country_id" db:"country_id"`
CountryCode string `json:"country_code" db:"country_code"`
Latitude float64 `json:"latitude" db:"latitude"`
Longitude float64 `json:"longitude" db:"longitude"`
CreatedAt time.Time `json:"created_at" db:"created_at"`
UpdatedAt time.Time `json:"updated_at" db:"updated_at"`
WikiDataID string `json:"wikiDataId,omitempty" db:"wiki_data_id"`
}
type Cities[] City
type CitiesToUnmarshal[] struct {
ID int `json:"id"`
Name string `json:"name"`
StateID int `json:"state_id"`
StateCode string `json:"state_code"`
StateName string `json:"state_name"`
CountryID int `json:"country_id"`
CountryCode string `json:"country_code"`
CountryName string `json:"country_name"`
Latitude string `json:"latitude"`
Longitude string `json:"longitude"`
WikiDataID string `json:"wikiDataId"`
}
func main() {
jsonFileName: = "cities.json"
jsonFileData,
err: = os.ReadFile(jsonFileName)
if err != nil {
panic(err)
}
var cityData CitiesToUnmarshal
err = json.Unmarshal(jsonFileData, & cityData)
if err != nil {
panic(err)
}
// Connect to the database
db,
err: = pgx.Connect(context.Background(), "postgres://postgresql:postgresql@localhost:5432/postgresql")
if err != nil {
panic(err)
}
var cityDataFixed Cities
for _,
city: = range cityData {
var dumpCity City
dumpCity.StateCode = city.StateCode
dumpCity.CountryCode = city.CountryCode
lat, err: = strconv.ParseFloat(city.Latitude, 64)
if err != nil {
panic(err)
}
lon, err: = strconv.ParseFloat(city.Longitude, 64)
if err != nil {
panic(err)
}
dumpCity.Latitude = lat
dumpCity.Longitude = lon
dumpCity.ID = uint32(city.ID)
dumpCity.WikiDataID = city.WikiDataID
dumpCity.CountryID = uint32(city.CountryID)
dumpCity.Name = city.Name
dumpCity.CreatedAt = time.Now()
dumpCity.UpdatedAt = time.Now()
dumpCity.StateID = uint32(city.StateID)
cityDataFixed = append(cityDataFixed, dumpCity)
}
jsonFileName = "countries.json"
var countryData Countries
var countryDataFixed CountriesInDB
var timezones CountryTimezones
jsonFileData,
err = os.ReadFile(jsonFileName)
if err != nil {
panic(err)
}
err = json.Unmarshal(jsonFileData, & countryData)
if err != nil {
panic(err)
}
for _,
country: = range countryData {
var dumpCountry CountryInDB
dumpCountry.ID = uint32(country.ID)
dumpCountry.Name = country.Name
dumpCountry.ISO2 = country.Iso2
dumpCountry.ISO3 = country.Iso3
dumpCountry.NumericCode = country.NumericCode
dumpCountry.Capital = country.Capital
dumpCountry.CurrencySymbol = country.CurrencySymbol
dumpCountry.TLD = country.Tld
dumpCountry.CurrencyName = country.CurrencyName
dumpCountry.Native = country.Native
dumpCountry.CreatedAt = time.Now()
dumpCountry.UpdatedAt = time.Now()
dumpCountry.Region = country.Region
dumpCountry.Subregion = country.Subregion
// marshal translations into json and put it into dumpCountry.Translations as a string
translations, err: = json.Marshal(country.Translations)
if err != nil {
panic(err)
}
dumpCountry.Translations = string(translations)
dumpCountry.EmojiU = country.EmojiU
dumpCountry.PhoneCode = country.PhoneCode
dumpCountry.Currency = country.Currency
dumpCountry.Emoji = country.Emoji
for _, tz: = range country.Timezones {
tz.ID = len(timezones) + 1
timezones = append(timezones, tz)
dumpCountry.TimezoneID = append(dumpCountry.TimezoneID, uint32(tz.ID))
}
lat, err: = strconv.ParseFloat(country.Latitude, 64)
if err != nil {
panic(err)
}
lon, err: = strconv.ParseFloat(country.Longitude, 64)
if err != nil {
panic(err)
}
dumpCountry.Latitude = lat
dumpCountry.Longitude = lon
countryDataFixed = append(countryDataFixed, dumpCountry)
}
jsonFileName = "states.json"
var stateData States
var stateDataFixed StatesInDB
jsonFileData,
err = os.ReadFile(jsonFileName)
if err != nil {
panic(err)
}
err = json.Unmarshal(jsonFileData, & stateData)
if err != nil {
panic(err)
}
for _,
state: = range stateData {
var dumpState StateInDB
dumpState.ID = uint32(state.ID)
dumpState.Name = state.Name
dumpState.CountryID = uint32(state.CountryID)
dumpState.CountryCode = state.CountryCode
if state.Type != nil {
dumpState.Type = state.Type.(string)
}
dumpState.CreatedAt = time.Now()
dumpState.UpdatedAt = time.Now()
lat, err: = strconv.ParseFloat(state.Latitude, 64)
if err != nil {
lat = 0
}
lon, err: = strconv.ParseFloat(state.Longitude, 64)
if err != nil {
lon = 0
}
dumpState.Latitude = lat
dumpState.Longitude = lon
stateDataFixed = append(stateDataFixed, dumpState)
}
if err = DropAndInsertTimezones(timezones, db);err != nil {
panic(err)
}
if err = DropAndInsertCountry(countryDataFixed, db);err != nil {
panic(err)
}
if err = DropAndInsertStates(stateDataFixed, db);err != nil {
panic(err)
}
if err = DropAndInsertCities(cityDataFixed, db);err != nil {
panic(err)
}
}
func DropAndInsertStates(states StatesInDB, db * pgx.Conn) error {
to, cancel: = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
_, err: = db.Exec(to, `DROP TABLE IF EXISTS states CASCADE;`)
if err != nil {
panic(err)
}
to, cancel = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
_, err = db.Exec(to, `CREATE TABLE states (
id SMALLSERIAL PRIMARY KEY NOT NULL,
name VARCHAR(255) NOT NULL,
country_id INTEGER NOT NULL,
country_code CHAR(2) NOT NULL,
type VARCHAR(191),
latitude DECIMAL(10, 8),
longitude DECIMAL(11, 8),
created_at TIMESTAMPTZ DEFAULT NULL,
updated_at TIMESTAMPTZ DEFAULT current_timestamp,
FOREIGN KEY (country_id) REFERENCES countries (id)
);`)
if err != nil {
return err
}
var rows[][] interface {}
for _, state: = range states {
rows = append(rows, [] interface {} {
state.ID, state.Name, state.CountryID, state.CountryCode, state.Type, state.Latitude, state.Longitude, state.CreatedAt, state.UpdatedAt
})
}
cols: = [] string {
"id", "name", "country_id", "country_code", "type", "latitude", "longitude", "created_at", "updated_at"
}
_, err = db.CopyFrom(
context.Background(),
pgx.Identifier {
"states"
},
cols,
pgx.CopyFromRows(rows),
)
if err != nil {
return err
}
return nil
}
func DropAndInsertTimezones(timezones CountryTimezones, db * pgx.Conn) error {
to, cancel: = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
_, err: = db.Exec(to, `DROP TABLE IF EXISTS timezones CASCADE;`)
to, cancel = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
_, err = db.Exec(to, `CREATE TABLE timezones (
id SMALLSERIAL PRIMARY KEY,
zone_name VARCHAR(255) NOT NULL,
gmt_offset INTEGER NOT NULL,
gmt_offset_name VARCHAR(255) NOT NULL,
abbreviation VARCHAR(255) NOT NULL,
tz_name VARCHAR(255) NOT NULL
);`)
if err != nil {
return err
}
cols: = [] string {
"zone_name", "gmt_offset", "gmt_offset_name", "abbreviation", "tz_name"
}
var rows[][] interface {}
for _, tz: = range timezones {
rows = append(rows, [] interface {} {
tz.ZoneName, tz.GmtOffset, tz.GmtOffsetName, tz.Abbreviation, tz.TzName
})
}
to, cancel = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
_, err = db.CopyFrom(to, pgx.Identifier {
"timezones"
}, cols, pgx.CopyFromRows(rows))
if err != nil {
return err
}
return nil
}
func DropAndInsertCountry(countries CountriesInDB, db * pgx.Conn) error {
to, cancel: = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
_, err: = db.Exec(to, `DROP TABLE IF EXISTS countries CASCADE;`)
if err != nil {
return err
}
to, cancel = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
_, err = db.Exec(to, `CREATE TABLE countries (
id SMALLSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
iso3 VARCHAR(3),
numeric_code VARCHAR(3),
iso2 VARCHAR(2),
phonecode VARCHAR(255),
capital VARCHAR(255),
currency VARCHAR(255),
currency_name VARCHAR(42),
currency_symbol VARCHAR(255),
tld VARCHAR(255),
native VARCHAR(255),
region VARCHAR(12),
subregion VARCHAR(255),
timezone_id INTEGER[],
translations JSONB,
latitude DOUBLE PRECISION,
longitude DOUBLE PRECISION,
emoji VARCHAR(191),
emojiU VARCHAR(191),
created_at TIMESTAMPTZ DEFAULT current_timestamp,
updated_at TIMESTAMPTZ DEFAULT current_timestamp
);
`)
if err != nil {
return err
}
cols: = [] string {
"name", "iso3", "numeric_code", "iso2", "phonecode", "capital", "currency", "currency_name", "currency_symbol", "tld", "native", "region", "subregion", "timezone_id", "translations", "latitude", "longitude", "emoji", "emojiu", "created_at", "updated_at"
}
var rows[][] interface {}
for _, country: = range countries {
rows = append(rows, [] interface {} {
country.Name, country.ISO3, country.NumericCode, country.ISO2, country.PhoneCode, country.Capital, country.Currency, country.CurrencyName, country.CurrencySymbol, country.TLD, country.Native, country.Region, country.Subregion, country.TimezoneID, country.Translations, country.Latitude, country.Longitude, country.Emoji, country.EmojiU, time.Now(), time.Now()
})
}
to, cancel = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
_, err = db.CopyFrom(to, pgx.Identifier {
"countries"
}, cols, pgx.CopyFromRows(rows))
if err != nil {
return err
}
return nil
}
func DropAndInsertCities(cities[] City, db * pgx.Conn) error {
to, cancel: = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
_, err: = db.Exec(to, `DROP TABLE IF EXISTS cities CASCADE;`)
if err != nil {
panic(err)
}
to, cancel = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
// create Cities table, if it does not exist
_, err = db.Exec(to, `CREATE TABLE cities (
id SERIAL PRIMARY KEY,
name VARCHAR(86) NOT NULL,
state_id SMALLINT NOT NULL,
state_code VARCHAR(255) NOT NULL,
country_id SMALLINT NOT NULL,
country_code CHAR(2) NOT NULL,
latitude DECIMAL(10, 8) NOT NULL,
longitude DECIMAL(11, 8) NOT NULL,
created_at TIMESTAMPTZ DEFAULT '2014-01-01 06:31:01',
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
wiki_data_id VARCHAR(255) DEFAULT NULL,
CONSTRAINT fk_cities_state FOREIGN KEY (state_id) REFERENCES states (id),
CONSTRAINT fk_cities_country FOREIGN KEY (country_id) REFERENCES countries (id)
);
`)
if err != nil {
panic(err)
}
// Prepare the data for bulk insert
cols: = [] string {
"name", "state_id", "state_code", "country_id", "country_code", "latitude", "longitude", "created_at", "updated_at", "wiki_data_id"
}
var rows[][] interface {}
for _, city: = range cities {
rows = append(rows, [] interface {} {
city.Name, city.StateID, city.StateCode, city.CountryID, city.CountryCode, city.Latitude, city.Longitude, city.CreatedAt, city.UpdatedAt, city.WikiDataID
})
}
to, cancel = context.WithTimeout(context.Background(), 5 * time.Second)
defer cancel()
_, err = db.CopyFrom(to, pgx.Identifier {
"cities"
}, cols, pgx.CopyFromRows(rows))
if err != nil {
panic(err)
}
return nil
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment