Re: [GNUe-dev] zipcode table

[alex bodnaru]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


hi reinhart,

thanks for your interest.

i have improved the script and zipcode example a little. here are the
new ones.

i also have a few ideas to enlighten forms development.

looking forward to attent gnue soon.

alex

Reinhard Mueller wrote:
> Hi, Alex!
> 
> Am Donnerstag, den 14.09.2006, 09:17 +0300 schrieb alex bodnaru:
> 
>>after a few googling, i have found a suitable data source, and i provide
>>a script to make the sql source, as long with the url of provenience.
>>
>>the script is attached.
> 
> 
> Thank you for your contribution, I added the script to SVN, it will be
> included in the next release of gnue-forms.
> 
> Thanks,
> Reinhard
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with Debian - http://enigmail.mozdev.org

iQCVAwUBRRneB9pwN1sq38njAQJW4QP9HRqZDcXpGTj3IlXsfaY2rfU0oU+ldkHq
GKJ+fJMmLdym9+bQxdKJFCnhVes6+yYqsN7sRY6fC41B07wG7RtqiY4B0ej7whUc
fQ3IFuyRxZA1gbtmSuWXnVCqCBwFBV+1+qn7NUVR5WM6rKKeCAhe1WHKXTWgbNMs
oXYmJ3fXTsA=
=6Ocq
-----END PGP SIGNATURE-----

# utility to create the sql needed for populating the zipcode table.
# you should first download and unpack the text file from 
# http://www.cfdynamics.com/zipbase/, courtesy to address@hidden
# then just run this script, and the resulting data will populate the
# zipcode table from various gnue examples.
#
# done by alex bodnaru <address@hidden> for the gnue project, but 
# may be used for any purpose, without holding the author responsible 
# for any bad results may happen (but i won't wish that will).

import csv, string

def str2sql(string, capitalize=0):
    if capitalize:
        string = " ".join([word.capitalize() for word in string.split()])
    return "'" + string.replace("'", "''") + "'"

def str2float(string):
    if string:
        return str(float(string))
    else: return 'NULL'

fobj = open("ZIP_CODES.txt")
reader = csv.reader(fobj)

counties = {}

#"00501","+40.922326","-072.637078","HOLTSVILLE","NY","SUFFOLK","UNIQUE"
for zipcode, latitude, longitude, city, state, county, zip_class in reader:
    print "insert into zipcode (zipcode, city, state, longitude, latitude) 
values(%s, %s, %s, %s, %s);" % \
        (str2sql(zipcode), str2sql(city, 1), str2sql(state), 
str2float(longitude), str2float(latitude))
    counties[(county, state)] = 1

fobj.close()

for (county, state) in counties.keys():
    print "insert into county (county, state) values(%s, %s);" % \
        (str2sql(county, 1), str2sql(state))

<?xml version="1.0"?>

<form title="Master/Detail Demo">
  <options>
    <option name="version" value="0.0.3"/>
  </options>
  <datasource name="dsState" cache="15" connection="gnue" order_by="State"
              prequery="Y" table="State" primarykey="state"/>
  <datasource name="dsCities" cache="15" connection="gnue" detaillink="State"
              master="dsState" masterlink="State" order_by="City" 
table="Zipcode" primarykey="zipcode"/>
  <datasource name="dsCounties" cache="15" connection="gnue" detaillink="State"
              master="dsState" masterlink="State" order_by="County" 
table="County" primarykey="county,state"/>
  <logic>
    <block name="blkState" datasource="dsState" rows="5" transparent="Y" 
autoCreate="Y" autoNextRecord="Y" autoCommit="Y">
      <field name="entState" field="State"/>
      <field name="entDesc" field="Description"/>
    </block>
    <block name="blkCities" datasource="dsCities" rows="5">
      <field name="City" field="City"/>
      <field name="State" field="State"/>
      <field name="Zip" field="Zipcode"/>
      <field name="Zop"/>
    </block>
    <block name="blkCounties" datasource="dsCounties" rows="5">
      <field name="County" field="County"/>
      <field name="State" field="State"/>
    </block>
  </logic>
  <layout xmlns:c="GNUe:Layout:Char" c:height="18" c:width="43">
    <page name="Page_1">
      <label name="Label_2" text="Description" c:x="7" c:y="1"/>
      <entry block="blkState" field="entDesc" c:width="30" c:x="7" c:y="2"/>
      <label name="Label_1" text="Code" c:x="2" c:y="1"/>
      <entry block="blkState" field="entState" c:width="4" c:x="2" c:y="2"/>
      <scrollbar block="blkState" c:x="38" c:y="2" c:widht="1" c:height="5" />

      <box name="Box_1" c:height="8" label="State Cities" c:width="40" c:x="1"
           c:y="8"/>
      <label name="Label_3" rows="1" text="City" c:x="2" c:y="9"/>
      <entry block="blkCities" field="City" c:width="20" c:x="2" c:y="10"/>
      <label name="Label_4" rows="1" text="ST" c:x="23" c:y="9"/>
      <entry block="blkCities" field="State" c:width="3" c:x="23" c:y="10"/>
      <label name="Label_5" rows="1" text="Zip" c:x="27" c:y="9"/>
      <entry block="blkCities" field="Zip" c:width="10" c:x="27" c:y="10"/>
      <entry block="blkCities" field="Zop" hidden="Y" c:width="1" c:x="27" 
c:y="1"/>
      <scrollbar block="blkCities" c:x="38" c:y="10" c:widht="1" c:height="5" />

      <box name="Box_2" c:height="8" label="State Counties" c:width="29" 
c:x="41"
           c:y="8"/>
      <label name="Label_6" rows="1" text="County" c:x="42" c:y="9"/>
      <entry block="blkCounties" field="County" c:width="20" c:x="42" c:y="10"/>
      <label name="Label_4" rows="1" text="ST" c:x="63" c:y="9"/>
      <entry block="blkCounties" field="State" c:width="3" c:x="63" c:y="10"/>
      <scrollbar block="blkCounties" c:x="67" c:y="10" c:widht="1" c:height="5" 
/>
    </page>
  </layout>
</form>