Home SQL - Fit data points in X, Y, Z box
Reply: 1

SQL - Fit data points in X, Y, Z box

james5
1#
james5 Published in 2018-01-11 01:35:41Z

I have a table in SSMS which currently has 3 columns. [x], [y], and [z] (see Table Sample below)

I will be using this data for a 3d model so I'm trying to convert the data points to fit into a 1000x1000x1000 box.

Ideally, my goal is to create a view or new table or at least a select statement that just takes the maximum x, y, and z values, sets those to 1024 and then somehow transforms the remaining data points so that they're still the same relative distance from the surrounding points. (if that makes any sense?) As you can see from the sample, the X's and Y's are well out of the desired range.

Table Sample

X       Y            Z
 191106 438253.98   -1694.4952
189577  436750.98   -1716.5457
191723  439258.98   -1611.1542
192336  435909.98   -1642.1016
194749  437363.98   -1716.1238
194955  439394.98   -1568.7328
196368  438367.98   -1534.8014
196337  437207.98   -1545.7195
197387  437815.98   -1635.2943
196613  439527.98   -1547.7865
197067  438447.98   -1489.4183
196917  436722.98   -1506.2191
198433  437852.98   -1485.5884
200319  438479.98   -1430.4632
199402  436639.98   -1533.8686
199713  436178.98   -1513.1188
200880  440296.98   -1391.5241
200682  439695.98   -1371.639
203323  439027.98   -1421.2597
201380  438782.98   -1445.6019
203340  436885.98   -1448.6549
207446  440284.98   -1491.6267
205145  439002.98   -1479.705
206157  438509.98   -1536.9528
207978  436308.98   -1305.442
209665  437926.98   -1455.1272
207666  436249.98   -1416.6149
208014  436921.98   -1343.94
212075  439817.98   -1374.8002
208679  440630.98   -1323.8376
211192  436402.98   -1279.3944
210452  439760.98   -1386.7478
210866  440111.98   -1446.0699
213576  440283.98   -1438.2461
215415  437210.98   -1343.1045
213434  437566.98   -1350.9467
215575  437170.98   -1323.745
217012  437118.98   -1468.6063
218936  436636.98   -1322.4785
217929  436261.98   -1421.3502
219655  436880.98   -1342.3694
220476  436713.98   -1383.1211
219687  436294.98   -1320.2535
218359  439845.98   -1370.6624
222389  435813.98   -1449.1301
223197  436862.98   -1499.2541
220449  437855.98   -1480.1147
224052  436682.98   -1457.941
225385  439215.98   -1445.2534
224875  439058.98   -1453.1954
224611  438088.98   -1428.7523
224523  438640.98   -1374.0964
225388  439474.98   -1403.2189
228256  439065.98   -1363.5486
229816  437234.98   -1424.3758
230441  438093.98   -1559.9587
228173  436542.98   -1549.3953
229327  438042.98   -1381.3427
230226  436970.98   -1407.8287
233820  437086.98   -1382.2457
232178  436166.98   -1385.9663
234608  439996.98   -1468.6711
232418  438061.98   -1447.3259
235974  436656.98   -1381.7027
237590  436235.98   -1399.9012
235966  438663.98   -1370.2727
234652  436002.98   -1420.8281
235570  439423.98   -1390.8704
235931  438110.98   -1542.1993
238525  440095.98   -1482.6536
241616  435684.98   -1446.2368
240304  436988.98   -1472.9517
239200  436845.98   -1442.8028
239792  436927.98   -1407.7895
243214  440116.98   -1480.0391
241129  436652.98   -1517.4193
244601  438268.98   -1398.0311
246074  437269.98   -1454.5629
243281  440017.98   -1521.1655
245258  435815.98   -1403.9929
247566  438524.98   -1546.8568
246994  440542.98   -1438.6206
246275  440195.98   -1596.2303
249734  440143.98   -1483.694
248584  438937.98   -1423.644
247599  439867.98   -1576.9712
248454  437187.98   -1461.8549
250115  437224.98   -1471.2508
251304  440481.98   -1530.8975
251456  438928.98   -1455.6035
255368  440108.98   -1498.3991
254561  438203.98   -1551.4143
257281  439499.98   -1528.5569
254162  437862.98   -1527.261
255902  438131.98   -1466.7475
258635  439942.98   -1558.8969
259902  436111.98   -1517.8925
256287  437393.98   -1670.2096
259228  436783.98   -1489.4766
259618  437333.98   -1604.9076
260749  438923.98   -1565.3387
263245  438080.98   -1501.8154
261966  440096.98   -1614.651
263158  435987.98   -1605.458
262845  439896.98   -1582.5075
265248  440491.98   -1648.508
266856  440411.98   -1706.7038
267613  437850.98   -1694.7547
267655  436194.98   -1660.7436
265172  437557.98   -1610.8256
268341  437296.98   -1610.4581
270493  437391.98   -1695.3816
271324  439506.98   -1593.1007
269317  436616.98   -1655.3611
268178  440099.98   -1561.5692
272987  438820.98   -1494.7628
271824  439455.98   -1534.2566
270492  436870.98   -1632.1811
275297  438523.98   -1613.9092
275924  439874.98   -1599.9024
273780  439553.98   -1663.6221
277244  440409.98   -1575.6931
276636  438806.98   -1636.9198
276817  439217.98   -1514.7822
278485  436804.98   -1624.9701
278989  440529.98   -1536.7674
279414  437012.98   -1572.2877
278105  436420.98   -1607.4551
279341  437534.98   -1605.5338
281636  437969.98   -1514.9627
282599  440158.98   -1657.814
280493  439286.98   -1534.0811
283804  436881.98   -1738.765
189594  435519.98   -1658.5641
191397  435010.98   -1700.8264
191654  435011.98   -1589.1115
193361  439236.98   -1496.7807
194678  436421.98   -1548.643
194814  435210.98   -1562.5543
195911  439563.98   -1457.9624
197437  437528.98   -1612.1455
194251  439615.98   -1584.9062
196280  437754.98   -1626.5308
196240  435858.98   -1438.134
199846  439333.98   -1433.1658
197226  437268.98   -1626.6341
198871  437307.98   -1458.615
200988  435776.98   -1493.2658
198499  435915.98   -1406.514
199674  438567.98   -1425.0381
204528  436364.98   -1391.2642
204078  438951.98   -1401.7545
204659  436058.98   -1371.4057
206808  435097.98   -1478.2608
204738  437733.98   -1477.4681
207029  438227.98   -1356.5324
205945  438909.98   -1341.7529
206490  436152.98   -1363.2217
208602  436348.98   -1430.9503
209461  435603.98   -1382.5246
209519  436010.98   -1372.4789
208376  439795.98   -1414.4512
212107  437604.98   -1353.8593
210440  435475.98   -1407.1026
209711  435302.98   -1466.2034
uzi
2#
uzi Reply to 2018-01-11 08:35:46Z

From what I understood, you need to find new coordinates for your points. So, you must find ratio between 1024 and difference between max and min value (D). Update your data so that highest value will be (D) and then multiply by ratio.

with cte as (
    select
        min(val) minVal, 1024/(max(val)-min(val)) k
    from (
        select x val from myTable
        union all select y from myTable
        union all select z from myTable
    ) t
)

select
    x, y, z, (x-minVal)*k newX, (y-minVal)*k newY, (z-minVal)*k newZ
from
    myTable
    cross join cte
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.299526 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO