1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
| from osgeo import ogr
import pyodbc
def main():
src_conn_info = "../shape_eg_data/pline.shp"
dst_conn_info = "DSN=svde;SERVER=127.0.0.1;TCP_PORT=5236;UID=SYSDBA;PWD=SYSDBA;"
ogr_dm_type_mapping = {
'0' : 'integer',
'1' : 'double',
'2' : 'varchar',
'3' : 'varchar',
'4' : 'varchar',
'5' : 'varchar',
}
ogr_geom_type_mapping = {
'0' : 'Point',
'1' : 'LineString',
'2' : 'Polygon',
'3' : 'MultiPoint',
'4' : 'MultiLineString',
'5' : 'MultiPolygon',
}
dm_conn = pyodbc.connect(dst_conn_info)
cursor = dm_conn.cursor()
try:
cursor.execute("""select 1 from svde_metadata""")
except pyodbc.ProgrammingError:
cursor.execute("""create table svde_metadata(
table_name varchar primary key,
geom_column_name varchar not null,
geom_type varchar,
geom_dimension varchar,
geom_srid varchar)""")
cursor.execute("""create table svde_srs(
srid varchar primary key,
auth_name varchar,
auth_srid varchar,
srtext varchar not null,
proj4text varchar)""")
cursor.execute("""create table svde_column_defn(
cid varchar,
table_name varchar not null,
column_name varchar not null,
column_type varchar not null,
primary key (table_name, column_name))""")
cursor.commit()
else:
print "metadata tables already exists in database!"
finally:
pass
ds = ogr.Open(src_conn_info)
layer = ds.GetLayer()
layer_defn = layer.GetLayerDefn()
field_count = layer_defn.GetFieldCount()
fields_type_dict = {}
fields_defn_serial = ""
fields_comma_serial = ""
for i in range(0, field_count-1):
field_name = layer_defn.GetFieldDefn(i).GetName()
field_type = layer_defn.GetFieldDefn(i).GetType()
fields_type_dict[field_name] = ogr_dm_type_mapping[str(field_type)]
fields_defn_serial += field_name + ' ' + ogr_dm_type_mapping[str(field_type)] + ','
fields_comma_serial += field_name + ','
fields_defn_serial = fields_defn_serial.rstrip(', ')
fields_comma_serial = fields_comma_serial.rstrip(', ')
table_name = layer_defn.GetName()
print "fields_comma_serial" + fields_comma_serial
print """create table %s (
%s,
fid varchar primary key,
geom varchar)""" % (table_name, fields_defn_serial)
try:
cursor.execute("""select 1 from %s""" % (table_name) )
except pyodbc.ProgrammingError:
cursor.execute("""create table %s (
%s,
fid varchar primary key,
geom varchar)""" % (table_name, fields_defn_serial) )
cursor.commit()
geom_type = ogr_geom_type_mapping[str(layer_defn.GetGeomType())]
geom_dimension = str(2)
geom_srid = str(900913)
print """insert into svde_metadata(table_name, geom_column_name, geom_type,
geom_dimension, geom_srid) values (%s, 'geom', %s, %s, %s)
""" % (repr(table_name), repr(geom_type), repr(geom_dimension), repr(geom_srid) )
cursor.execute("""insert into svde_metadata(table_name, geom_column_name, geom_type,
geom_dimension, geom_srid) values (%s, 'geom', %s, %s, %s)
""" % (repr(table_name), repr(geom_type), repr(geom_dimension), repr(geom_srid) ) )
print fields_type_dict
for cname, cvalue in fields_type_dict.items():
cursor.execute("""insert into svde_column_defn( table_name, column_name, column_type)
values ('%s', '%s', '%s')""" % (table_name, cname, str(cvalue)) )
cursor.commit()
else:
print "table " + str(table_name) + "already exsit"
finally:
pass
fid = 0
for feat in layer:
fid = fid + 1
values_comma_serial = ""
for i in range(0, field_count-1):
value = feat.GetFieldAsString(i)
values_comma_serial += repr(value) + ','
values_comma_serial = values_comma_serial.rstrip(',')
geom_wkt = feat.GetGeometryRef().ExportToWkt()
print """insert into %s(%s, fid, geom) values (%s, '%s' , '%s')""" % (
table_name, fields_comma_serial, values_comma_serial, str(fid), geom_wkt)
cursor.execute("""insert into %s(%s, fid, geom) values (%s, '%s' , '%s')""" % (
table_name, fields_comma_serial, values_comma_serial, str(fid), geom_wkt) )
cursor.commit()
if __name__ == '__main__':
main()
|
