· 8 years ago · Jun 13, 2018, 05:30 PM
1this.createDbSchema = function (features,data,operation, tripId ) {
2 var featureCollectionsData = data.featureCollections
3 // Create a database
4 console.log(features);
5 console.log(data);
6 console.log(featureCollectionsData);
7
8 var db = new SQL.Database();
9// NOTE: You can also use new sql.Database(data) where
10// data is an Uint8Array representing an SQLite database file
11
12// Execute some sql
13 db.run("CREATE TABLE if not exists trip" +
14 "(" +
15 " id TEXT primary key," +
16 " name TEXT," +
17 " startLoc TEXT," +
18 " endLoc TEXT," +
19 " desc TEXT," +
20 " longDesc TEXT," +
21 " unicodeDesc TEXT," +
22 " sharedType TEXT," +
23 " sharedLink TEXT," +
24 " ownerId TEXT," +
25 " membershipType TEXT," +
26 " timestamp INTEGER DEFAULT 0," +
27 " folder TEXT," +
28 " image BLOB " +
29 ")");
30
31
32 db.run("CREATE TABLE if not exists tripSegment" +
33 "(" +
34 " id TEXT primary key," +
35 " tripId TEXT," +
36 " startLoc TEXT," +
37 " endLoc TEXT," +
38 " minLevel INTEGER DEFAULT 0," +
39 " maxLevel INTEGER DEFAULT 0," +
40 " name TEXT," +
41 " desc TEXT," +
42 " longDesc TEXT," +
43 " unicodeDesc TEXT," +
44 " timestamp INTEGER DEFAULT 0" +
45 ")");
46
47 db.run("CREATE TABLE if not exists geometry" +
48 "(" +
49 " id TEXT primary key," +
50 " tripId TEXT," +
51 " tripSegmentId INTEGER DEFAULT 0," +
52 " longitude REAL DEFAULT 0," +
53 " latitude REAL DEFAULT 0," +
54 " gTileID TEXT," +
55 " minLevel INTEGER DEFAULT 0," +
56 " maxLevel INTEGER DEFAULT 0," +
57 " type TEXT," +
58 " name TEXT," +
59 " desc TEXT," +
60 " longDesc TEXT," +
61 " unicodeDesc TEXT," +
62 " geoJSON TEXT," +
63 " mediaLink TEXT," +
64 " timestamp INTEGER DEFAULT 0," +
65 " color INTEGER DEFAULT 0," +
66 " imageUrl TEXT," +
67 " width INTEGER DEFAULT 0," +
68 " lineStyle TEXT," +
69 " fillStyle TEXT" +
70 ")");
71
72
73 var trip = db.prepare('INSERT INTO trip VALUES(:id,:name,:startLoc,:endLoc,:desc,:longDesc,:unicodeDesc,:sharedType,:sharedLink,:ownerId,:membershipType,:timestamp,:folder,:image)');
74 if(featureCollectionsData.shareTrek){
75 var sharedType = 'public';
76 if(featureCollectionsData.access == 'visible'){
77 var sharedType = 'visibleWithLink';
78 var sharedLink = '';
79 }
80 }
81 if(!featureCollectionsData.shareTrek){
82 var sharedType = 'private';
83 }
84
85 if(operation == 'update' && tripId != undefined){
86 var trip_id =tripId
87 }else{
88 var trip_id= uuid();
89 }
90
91 trip.run(
92 {
93 ':id': trip_id,
94 ':name': featureCollectionsData.trekName,
95 ':folder': featureCollectionsData.trekFolder,
96 ':sharedType': sharedType,
97 ':sharedLink': sharedLink,
98 ':ownerId': featureCollectionsData.userName,
99
100 });
101
102 //insert tripSegment
103 var tripSegmentId =uuid();
104 var tripSegment = db.prepare('INSERT INTO tripSegment VALUES(:id,:tripId,:startLoc,:endLoc,:minLevel,:maxLevel,:name,:desc,:longDesc,:unicodeDesc,:timestamp)');
105 tripSegment.run(
106 {
107 ':id':tripSegmentId ,
108 ':tripId': trip_id,
109
110 });
111
112
113 //insert feature into geometry table
114 for(var j=0;j<features.length; j++) {
115
116 console.log(features[j])
117 var type = features[j].geometry.type;
118 console.log(type)
119 if(type != 'GeometryCollection'){
120 var arcgis = features[j].geometry.coordinates;
121
122 if (type == 'polyline') {
123 type = 'LineString'
124 }
125
126 if (type == 'multipoint') {
127 type = 'MultiPoint'
128 }
129
130 arcgis = '';
131 arcgis = ArcgisToGeojsonUtils.geojsonToArcGIS({
132 "type": type.charAt(0).toUpperCase() + type.slice(1),
133 "coordinates": features[j].geometry.coordinates
134 });
135
136
137 console.log(arcgis)
138 console.log(features[j].properties.color)
139
140 var geometry = db.prepare('INSERT INTO geometry VALUES(:id,:tripId,:tripSegmentId,:longitude,:latitude,:gTileID,:minLevel,:maxLevel,:type,:name,:desc,:longDesc,:unicodeDesc,:geoJSON,:mediaLink,:timestamp,:color,:imageUrl,:width,:lineStyle,:fillStyle)');
141 var color = '-263168'
142 if (typeof features[j].properties.color != 'undefined') {
143 var colorOj = features[j].properties.color;
144
145 if(typeof colorOj != 'undefined'){
146 //color = this.colourToNumber(colorOj.r || 0,colorOj.g || 0,colorOj.b || 0)
147
148 //color =color.rgbNumber();
149 console.log(color)
150 }
151 }
152
153
154 var geometry_id = uuid();
155 //arcgis.spatialReference.latestWkid= 102100
156 arcgis.spatialReference.wkid = 4326
157 geometry.run(
158 {
159 ':id': geometry_id,
160 ':tripId': trip_id,
161 ':tripSegmentId': tripSegmentId,
162 ':longitude': 0,
163 ':latitude': 0,
164 ':type': features[j].geometry.type,
165 ':geoJSON': JSON.stringify(arcgis),
166 ':width': features[j].properties.width,
167 ':timestamp': 0,
168 ':color': color,
169 ':minLevel': 0,
170 ':maxLevel': 0,
171
172 });
173 }
174 }