Saya ingin menanyakan coordinates di kolom terpisah. dengan kata lain, saya ingin kueri yang disebutkan di bawah ini dapat menyertakan coordinates yang ada di geometry' object. i managed to make the query able to return the values of tipe, properti, and the geometri` yang merupakan geojson obyek.

Mengingat geojsonObjectAsJSON yang diposting di bawah ini, beri tahu saya bagaimana saya bisa menanyakan nilai coordinates

kode:

query = """WITH data AS (SELECT '{featuresArray}'::json AS featuresCollection)
        SELECT
            row_number() OVER () AS gid,
            ST_AsText(ST_GeomFromGeoJSON(feature->>'geometry')) AS geom,
            feature->'type' AS type,
            feature->'properties' AS properties
        FROM (
        SELECT 
            json_array_elements(featuresCollection->'features') AS feature
        FROM data) AS f;""".format(featuresArray=featuresArray)

    

isi geojsonObjectAsJSON:

[('{"type": "FeatureCollection", "features": [{"type": "Feature", "properties": {"treeID": "99", "XTOP": "338535.01", "YTOP": "5667141.11", "ZTOP": "19.98", "Zmax": "19.98", "Zmean": "1.8232183908046", "Zmin": "-0.03", "ID": "1"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69223420722764, 51.1329165124371], [6.69222819326484, 51.1329081193748], [6.69221180776184, 51.1329013203209], [6.69219149698336, 51.1329073049609], [6.69217949340067, 51.1329244260894], [6.69217956546811, 51.1329430447612], [6.69218526127669, 51.1329578171713], [6.692200130301, 51.1329606289999], [6.69220511560768, 51.1329581192089], [6.69221372090576, 51.1329518135339], [6.69222707128915, 51.132939215911], [6.69223420722764, 51.1329165124371]]]}}, {"type": "Feature", "properties": {"treeID": "109", "XTOP": "338537.53", "YTOP": "5667129.66", "ZTOP": "18.9", "Zmax": "18.9", "Zmean": "0.56044776119403", "Zmin": "-0.04", "ID": "2"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69226547705025, 51.1328283605685], [6.69226477705279, 51.1328251989051], [6.69225817843627, 51.1328170641171], [6.69225114186537, 51.1328119785883], [6.69223507726445, 51.1328073443943], [6.69221209610711, 51.1328439453134], [6.69221314385783, 51.1328487327339], [6.6922180820046, 51.1328586335107], [6.69223237530096, 51.1328615239076], [6.6922479146764, 51.132859492291], [6.69225680945743, 51.1328559804184], [6.69226797350914, 51.1328413609834], [6.69226547705025, 51.1328283605685]]]}}, {"type": "Feature", "properties": {"treeID": "112", "XTOP": "338537.83", "YTOP": "5667124.38", "ZTOP": "0.03", "Zmax": "0.03", "Zmean": "0.0125", "Zmin": "-0.01", "ID": "3"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69225693096545, 51.1327847516032], [6.69224029730571, 51.1327915283526], [6.69224729022912, 51.1327917563467], [6.69225693096545, 51.1327847516032]]]}}, {"type": "Feature", "properties": {"treeID": "130", "XTOP": "338538.62", "YTOP": "5667117.07", "ZTOP": "0.02", "Zmax": "0.02", "Zmean": "0.005", "Zmin": "0", "ID": "4"}, "geometry": {"type": "Polygon", "coordinates": [[[6.6922737133986, 51.1327262632073], [6.6922618472268, 51.1327262987605], [6.69226115502939, 51.1327287134319], [6.69226892021352, 51.1327277874716], [6.6922737133986, 51.1327262632073]]]}}, {"type": "Feature", "properties": {"treeID": "15", "XTOP": "338540.55", "YTOP": "5667139.9", "ZTOP": "31.6", "Zmax": "31.6", "Zmean": "2.44184466019418", "Zmin": "-0.05", "ID": "5"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69230806364113, 51.1328973746132], [6.69230046595516, 51.1328863420758], [6.69228392304113, 51.1328855657973], [6.6922669902971, 51.1328926064613], [6.69225803434834, 51.1329116764704], [6.69225290879938, 51.1329456619351], [6.6922586959951, 51.1329500032911], [6.69229038460729, 51.132953776729], [6.69230233106635, 51.1329320676033], [6.69230806364113, 51.1328973746132]]]}}, {"type": "Feature", "properties": {"treeID": "10", "XTOP": "338538.93", "YTOP": "5667143.93", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "0.0075", "Zmin": "0", "ID": "6"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69226909932274, 51.1329678366], [6.69226641291812, 51.1329672439347], [6.69225423867087, 51.1329677230975], [6.69225503666569, 51.1329717860795], [6.69226909932274, 51.1329678366]]]}}, {"type": "Feature", "properties": {"treeID": "129", "XTOP": "338541.05", "YTOP": "5667113.92", "ZTOP": "17.82", "Zmax": "17.82", "Zmean": "0.684615384615385", "Zmin": "-0.04", "ID": "7"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69232599612174, 51.1326897011068], [6.69232212480549, 51.1326784723192], [6.69231599378035, 51.1326752933774], [6.69230370484838, 51.1326694745926], [6.69228694245551, 51.1326731009628], [6.69227664987847, 51.1326817117313], [6.69227009923956, 51.1327012789191], [6.69226890245805, 51.132705212582], [6.69228679701497, 51.1327304787925], [6.69230931364978, 51.1327175224883], [6.69232346424543, 51.1326974757647], [6.69232480382597, 51.1326935449204], [6.69232599612174, 51.1326897011068]]]}}, {"type": "Feature", "properties": {"treeID": "4", "XTOP": "338540", "YTOP": "5667147.26", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "0.004", "Zmin": "-0.01", "ID": "8"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69227531534934, 51.1329922426842], [6.69227418635346, 51.1329919505805], [6.69226656451147, 51.1329900013425], [6.69226802666089, 51.1329979447873], [6.6922749888312, 51.1329959237125], [6.69227531534934, 51.1329922426842]]]}}, {"type": "Feature", "properties": {"treeID": "21", "XTOP": "338542.76", "YTOP": "5667130.18", "ZTOP": "30.67", "Zmax": "30.67", "Zmean": "1.13138888888889", "Zmin": "-0.06", "ID": "9"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69234522009268, 51.1328287656302], [6.69234539656757, 51.13281662743], [6.69233976793339, 51.1328005072778], [6.69233510468195, 51.1327994258963], [6.69231594261322, 51.1327967092098], [6.69230144032154, 51.1328123420077], [6.69229418909355, 51.1328316255505], [6.6922839366462, 51.1328623619585], [6.69230554766026, 51.1328704333619], [6.6923202307875, 51.1328655068032], [6.69233852665545, 51.1328483300023], [6.69234522009268, 51.1328287656302]]]}}, {"type": "Feature", "properties": {"treeID": "14", "XTOP": "338541.07", "YTOP": "5667143.99", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "0.00222222222222222", "Zmin": "-0.02", "ID": "10"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69230245173697, 51.1329755102324], [6.69228611158755, 51.1329649346718], [6.69228477199513, 51.1329688655154], [6.6922916326824, 51.1329832112248], [6.69229664819236, 51.1329858285149], [6.69230324973615, 51.1329795732142], [6.69230245173697, 51.1329755102324]]]}}, {"type": "Feature", "properties": {"treeID": "156", "XTOP": "338545.92", "YTOP": "5667098.24", "ZTOP": "0.05", "Zmax": "0.05", "Zmean": "-0.00357142857142857", "Zmin": "-0.03", "ID": "11"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69238288143095, 51.1325551070459], [6.69238261041295, 51.1325490758227], [6.69237336012785, 51.1325453856142], [6.69235828511823, 51.1325467069068], [6.69233294445344, 51.1325560998718], [6.6923250560129, 51.1325709638555], [6.6923352106771, 51.1325823166859], [6.69235802167206, 51.1325921205998], [6.69237487203162, 51.1325723969819], [6.69238288143095, 51.1325551070459]]]}}, {"type": "Feature", "properties": {"treeID": "18", "XTOP": "338545.23", "YTOP": "5667136.37", "ZTOP": "24.88", "Zmax": "24.88", "Zmean": "0.540434782608696", "Zmin": "-0.05", "ID": "12"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69235678858897, 51.1328891627965], [6.69235599058639, 51.132885099815], [6.69235519258393, 51.1328810368335], [6.69231474307826, 51.1328809578104], [6.6923132836687, 51.1328815585682], [6.69231194408122, 51.1328854894122], [6.69232420315314, 51.1329291716723], [6.69232717126271, 51.1329269818072], [6.6923486464278, 51.1329005141752], [6.69235678858897, 51.1328891627965]]]}}, {"type": "Feature", "properties": {"treeID": "63", "XTOP": "338543.07", "YTOP": "5667145.84", "ZTOP": "25.67", "Zmax": "25.67", "Zmean": "2.558", "Zmin": "-0.04", "ID": "13"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69232417872706, 51.1329841235518], [6.69232540687686, 51.132979560939], [6.69232337624525, 51.1329801504201], [6.69231245529376, 51.1329841619284], [6.69230775559946, 51.1329866773625], [6.69231325329394, 51.1329882249101], [6.69231820665403, 51.1329892220812], [6.69232417872706, 51.1329841235518]]]}}, {"type": "Feature", "properties": {"treeID": "61", "XTOP": "338543.4", "YTOP": "5667144.16", "ZTOP": "25.57", "Zmax": "25.57", "Zmean": "2.82666666666667", "Zmin": "-0.03", "ID": "14"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69232597202458, 51.1329653618288], [6.69231418138504, 51.1329667479548], [6.69231283283347, 51.1329708584986], [6.6923220871, 51.13297733688], [6.69232257376358, 51.1329761772883], [6.69232597202458, 51.1329653618288]]]}}, {"type": "Feature", "properties": {"treeID": "29", "XTOP": "338543.72", "YTOP": "5667121.11", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "-0.0025", "Zmin": "-0.02", "ID": "15"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69232658093919, 51.1327553676854], [6.69232524135684, 51.1327592985297], [6.69233193855203, 51.1327654566152], [6.69233286706724, 51.132764035931], [6.69232658093919, 51.1327553676854]]]}}, {"type": "Feature", "properties": {"treeID": "65", "XTOP": "338544.79", "YTOP": "5667143.85", "ZTOP": "20.02", "Zmax": "20.02", "Zmean": "3.63", "Zmin": "-0.03", "ID": "16"}, "geometry": {"type": "Polygon", "coordinates": [[[6.6923428169042, 51.1329514841034], [6.69233755943823, 51.1329537187123], [6.69231988070991, 51.1329642522548], [6.69234076488885, 51.1329697010888], [6.6923428169042, 51.1329514841034]]]}}, {"type": "Feature", "properties": {"treeID": "60", "XTOP": "338546.17", "YTOP": "5667145.3", "ZTOP": "0", "Zmax": "0", "Zmean": "-0.015", "Zmin": "-0.05", "ID": "17"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69236137127294, 51.1329807206157], [6.69232498120898, 51.1329880966835], [6.69234973181995, 51.1329848078818], [6.69236137127294, 51.1329807206157]]]}}, {"type": "Feature", "properties": {"treeID": "31", "XTOP": "338543.99", "YTOP": "5667118.88", "ZTOP": "0.02", "Zmax": "0.02", "Zmean": "0.002", "Zmin": "-0.02", "ID": "18"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69234418226092, 51.1327521176245], [6.69233772196369, 51.13274407551], [6.69234011596091, 51.1327562644551], [6.69234298548423, 51.1327560512882], [6.69234418226092, 51.1327521176245]]]}}, {"type": "Feature", "properties": {"treeID": "26", "XTOP": "338544.98", "YTOP": "5667121.52", "ZTOP": "0", "Zmax": "0", "Zmean": "-0.01", "Zmin": "-0.02", "ID": "19"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69234924805998, 51.1327709248176], [6.69235067667721, 51.1327680749915], [6.69233657766584, 51.1327756212988], [6.69234924805998, 51.1327709248176]]]}}, {"type": "Feature", "properties": {"treeID": "67", "XTOP": "338545.02", "YTOP": "5667137.62", "ZTOP": "25.01", "Zmax": "25.01", "Zmean": "6.24375", "Zmin": "-0.01", "ID": "20"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69235010410836, 51.1329085474692], [6.69234521738939, 51.1329090805678], [6.6923440435884, 51.1329096869642], [6.69233223663389, 51.1329257327289], [6.6923362782374, 51.1329335472182], [6.69234509352783, 51.1329258966033], [6.69235010410836, 51.1329085474692]]]}}, {"type": "Feature", "properties": {"treeID": "36", "XTOP": "338544.43", "YTOP": "5667117.91", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "-0.00375", "Zmin": "-0.01", "ID": "21"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69237562109752, 51.1327322323099], [6.69237482309554, 51.1327281693285], [6.69235193413251, 51.1327285269187], [6.69234222230857, 51.1327340912521], [6.69234443996302, 51.1327354841077], [6.692360871385, 51.1327356286082], [6.69237224251332, 51.1327340543194], [6.69237562109752, 51.1327322323099]]]}}, {"type": "Feature", "properties": {"treeID": "57", "XTOP": "338544.53", "YTOP": "5667145.36", "ZTOP": "20.06", "Zmax": "20.06", "Zmean": "1.81363636363636", "Zmin": "-0.04", "ID": "22"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69236168606955, 51.1329686750848], [6.69234981766078, 51.1329572884265], [6.69233121431728, 51.1329720308224], [6.69233265797694, 51.1329832116837], [6.69233499604409, 51.1329850566084], [6.69235175406655, 51.1329815200828], [6.69236168606955, 51.1329686750848]]]}}, {"type": "Feature", "properties": {"treeID": "38", "XTOP": "338546.2", "YTOP": "5667114.75", "ZTOP": "29.53", "Zmax": "29.53", "Zmean": "0.984", "Zmin": "-0.04", "ID": "23"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69240357446646, 51.1326905130879], [6.69240477123615, 51.1326865794235], [6.69239745859457, 51.1326784305479], [6.69238504534337, 51.132669371535], [6.69236010166315, 51.1326794019119], [6.69234892871975, 51.1326942010569], [6.69234996521659, 51.1327106802463], [6.69235580558459, 51.1327168214155], [6.6923846418739, 51.132723326611], [6.69240357446646, 51.1326905130879]]]}}, {"type": "Feature", "properties": {"treeID": "32", "XTOP": "338546", "YTOP": "5667119.88", "ZTOP": "0.05", "Zmax": "0.05", "Zmean": "0.00909090909090909", "Zmin": "-0.01", "ID": "24"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69237129653497, 51.1327558906762], [6.69236597751466, 51.132753627151], [6.69235446357724, 51.13275519862], [6.69236076200149, 51.1327693533492], [6.69236379167681, 51.1327716616186], [6.69237129653497, 51.1327558906762]]]}}, {"type": "Feature", "properties": {"treeID": "69", "XTOP": "338546.37", "YTOP": "5667131.07", "ZTOP": "24.42", "Zmax": "24.42", "Zmean": "3.1275", "Zmin": "-0.06", "ID": "25"}, "geometry": {"type": "Polygon", "coordinates": [[[6.692367683902, 51.132857000059], [6.69236624747223, 51.1328542735503], [6.69236167325165, 51.1328542731882], [6.69235290047982, 51.132872537375], [6.69235391240058, 51.1328780435939], [6.69236477334893, 51.1328838341828], [6.69237416317368, 51.1328818610268], [6.69237488615718, 51.1328816954225], [6.69237607397357, 51.1328779414585], [6.692367683902, 51.132857000059]]]}}, {"type": "Feature", "properties": {"treeID": "44", "XTOP": "338546.63", "YTOP": "5667107.76", "ZTOP": "0.03", "Zmax": "0.03", "Zmean": "0.008", "Zmin": "-0.02", "ID": "26"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69239099507016, 51.1326417879607], [6.69239043281634, 51.1326415969845], [6.69237157767037, 51.1326441927313], [6.6923787531271, 51.1326464929044], [6.69239099507016, 51.1326417879607]]]}}, {"type": "Feature", "properties": {"treeID": "66", "XTOP": "338546.44", "YTOP": "5667136.28", "ZTOP": "19.19", "Zmax": "19.19", "Zmean": "0.670526315789474", "Zmin": "-0.07", "ID": "27"}, "geometry": {"type": "Polygon", "coordinates": [[[6.6923969524934, 51.132889236167], [6.69240257401973, 51.1328739676745], [6.69240177153279, 51.1328699945432], [6.69239229830187, 51.1328650407981], [6.69236483102158, 51.1328855441509], [6.69236343376573, 51.1328877650275], [6.69235954885797, 51.1328997400803], [6.69235919155878, 51.1329011720412], [6.69235877978974, 51.132929494508], [6.69236737943046, 51.1329290347061], [6.6923969524934, 51.132889236167]]]}}, {"type": "Feature", "properties": {"treeID": "79", "XTOP": "338548.03", "YTOP": "5667123.55", "ZTOP": "23.84", "Zmax": "23.84", "Zmean": "2.05224", "Zmin": "-0.04", "ID": "28"}, "geometry": {"type": "Polygon", "coordinates": [[[6.6924429729393, 51.1327689976185], [6.69244286654913, 51.1327653979823], [6.69243423337915, 51.1327550645196], [6.69241043104876, 51.1327479392038], [6.69238315432753, 51.1327531567944], [6.69237434125933, 51.1327722296329], [6.6923670295201, 51.1328041933], [6.69236989008509, 51.1328041598324], [6.69240829603253, 51.1328022198506], [6.69242937786243, 51.1328008372528], [6.6924313860824, 51.1328006970194], [6.6924429729393, 51.1327689976185]]]}}, {"type": "Feature", "properties": {"treeID": "55", "XTOP": "338548.75", "YTOP": "5667095.03", "ZTOP": "8.01", "Zmax": "8.01", "Zmean": "1.73375", "Zmin": "-0.02", "ID": "29"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69243075985633, 51.1325380645258], [6.69241638266022, 51.1325311252681], [6.6924003354881, 51.1325290097154], [6.69238767860756, 51.1325334366535], [6.69238687049965, 51.1325353094071], [6.69238633903741, 51.1325373674987], [6.69239393668944, 51.1325484000307], [6.69240127343053, 51.1325474656039], [6.69242272587979, 51.1325443814947], [6.69242896108707, 51.1325397378466], [6.69243075985633, 51.1325380645258]]]}}, {"type": "Feature", "properties": {"treeID": "70", "XTOP": "338547.21", "YTOP": "5667133.26", "ZTOP": "19.04", "Zmax": "19.04", "Zmean": "7.587", "Zmin": "-0.01", "ID": "30"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69238145412797, 51.1328646468468], [6.69238065164298, 51.1328606737154], [6.69237659705414, 51.1328731850495], [6.6923767040138, 51.1328739066682], [6.69237726178975, 51.1328741874945], [6.69238145412797, 51.1328646468468]]]}}, {"type": "Feature", "properties": {"treeID": "74", "XTOP": "338547.55", "YTOP": "5667131.01", "ZTOP": "0.04", "Zmax": "0.04", "Zmean": "0.012", "Zmin": "-0.02", "ID": "31"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69239172489848, 51.1328536073019], [6.69238312527199, 51.1328540671058], [6.69237985363871, 51.132856610734], [6.6923819240151, 51.1328580906197], [6.69238918854273, 51.132861471811], [6.69239856825426, 51.1328654343878], [6.69240096904597, 51.1328660214118], [6.69239172489848, 51.1328536073019]]]}}, {"type": "Feature", "properties": {"treeID": "80", "XTOP": "338548.04", "YTOP": "5667124.62", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "-0.0025", "Zmin": "-0.02", "ID": "32"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69239739925307, 51.1327971480654], [6.69239298576452, 51.132796791124], [6.69238795625361, 51.1327973214054], [6.69239805893436, 51.1328011183777], [6.69239739925307, 51.1327971480654]]]}}, {"type": "Feature", "properties": {"treeID": "40", "XTOP": "338547.96", "YTOP": "5667108.5", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "-0.0025", "Zmin": "-0.02", "ID": "33"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69240998291263, 51.1326451307648], [6.69240160229753, 51.1326440659404], [6.69239994632672, 51.1326457420797], [6.69239906595609, 51.1326519304501], [6.69240998291263, 51.1326451307648]]]}}, {"type": "Feature", "properties": {"treeID": "51", "XTOP": "338549.55", "YTOP": "5667099.69", "ZTOP": "28.56", "Zmax": "28.56", "Zmean": "1.40655462184874", "Zmin": "-0.05", "ID": "34"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69247079011754, 51.1325580116217], [6.69245037149921, 51.1325460964348], [6.69243924236255, 51.1325428188342], [6.69243452536925, 51.1325428156564], [6.69242975909111, 51.1325438008279], [6.69241225585337, 51.1325479522214], [6.69241019835906, 51.1325490808041], [6.69240430471269, 51.1325640741102], [6.69240379508097, 51.1325685609698], [6.69240814296053, 51.132584565898], [6.69245845290714, 51.1325932937497], [6.69247496883839, 51.1325946091019], [6.69248554078401, 51.1325947278559], [6.69249969626032, 51.1325946475329], [6.69250137632623, 51.1325838880893], [6.69247079011754, 51.1325580116217]]]}}, {"type": "Feature", "properties": {"treeID": "117", "XTOP": "338548.5", "YTOP": "5667127.43", "ZTOP": "18.49", "Zmax": "18.49", "Zmean": "4.635", "Zmin": "0.01", "ID": "35"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69240202207781, 51.1328219723839], [6.69240191063732, 51.1328213406152], [6.69239829572496, 51.1328221686377], [6.69239207839791, 51.1328264528836], [6.69240202207781, 51.1328219723839]]]}}, {"type": "Feature", "properties": {"treeID": "54", "XTOP": "338550.23", "YTOP": "5667095.79", "ZTOP": "28.28", "Zmax": "28.28", "Zmean": "4.0928", "Zmin": "-0.13", "ID": "36"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69242512731351, 51.1325191562094], [6.69241898619832, 51.1325219130083], [6.69240985012039, 51.1325273987744], [6.69242131748103, 51.1325353598516], [6.69243571765166, 51.1325389718414], [6.69244038535995, 51.1325399633687], [6.69242512731351, 51.1325191562094]]]}}, {"type": "Feature", "properties": {"treeID": "76", "XTOP": "338548.89", "YTOP": "5667125.63", "ZTOP": "18.42", "Zmax": "18.42", "Zmean": "4.615", "Zmin": "0", "ID": "37"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69240467158793, 51.1328061055907], [6.69239965494465, 51.1328092443403], [6.69240045294999, 51.1328133073216], [6.69240576416321, 51.1328099945102], [6.69240467158793, 51.1328061055907]]]}}, {"type": "Feature", "properties": {"treeID": "86", "XTOP": "338549.36", "YTOP": "5667113.32", "ZTOP": "0.02", "Zmax": "0.02", "Zmean": "-0.0025", "Zmin": "-0.02", "ID": "38"}, "geometry": {"type": "Polygon", "coordinates": [[[6.692416898888, 51.1326956327962], [6.69241388265633, 51.1326930549783], [6.69241254308334, 51.1326969858238], [6.69241135079452, 51.1327008296383], [6.692416898888, 51.1326956327962]]]}}, {"type": "Feature", "properties": {"treeID": "91", "XTOP": "338551.02", "YTOP": "5667110.53", "ZTOP": "22.95", "Zmax": "22.95", "Zmean": "2.11378378378378", "Zmin": "-0.05", "ID": "39"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69247728045234, 51.132639983688], [6.69247568891363, 51.1326317678761], [6.69246706806426, 51.1326269209013], [6.69243739235177, 51.1326229174303], [6.6924290351972, 51.1326385816137], [6.69242123132997, 51.1326546164724], [6.69241305614816, 51.132675230859], [6.69241317426148, 51.132687194998], [6.69241545016231, 51.1326874198028], [6.69244513096439, 51.1326884554123], [6.69245527900007, 51.1326884758624], [6.69246693629026, 51.1326840291846], [6.6924694177205, 51.132682998908], [6.69247728045234, 51.132639983688]]]}}, {"type": "Feature", "properties": {"treeID": "53", "XTOP": "338550.25", "YTOP": "5667095.36", "ZTOP": "9.45", "Zmax": "9.45", "Zmean": "4.40666666666667", "Zmin": "-0.04", "ID": "40"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69244896373978, 51.1325112624831], [6.69244803132252, 51.1325098950007], [6.69242991599137, 51.1325177217883], [6.69242797832923, 51.1325221804577], [6.69242845545341, 51.132524078583], [6.69242906642011, 51.1325261592271], [6.69243706169811, 51.1325349511456], [6.69243765531382, 51.1325345131719], [6.69244040051127, 51.1325310597659], [6.69244896373978, 51.1325112624831]]]}}, {"type": "Feature", "properties": {"treeID": "125", "XTOP": "338549.71", "YTOP": "5667126.94", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "0.00142857142857143", "Zmin": "-0.01", "ID": "41"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69242197273061, 51.132808875465], [6.69241648344239, 51.1328100262402], [6.6924151393854, 51.1328140469356], [6.69241394261294, 51.1328179806], [6.6924242284291, 51.1328209717395], [6.69242511213773, 51.1328204495541], [6.69242197273061, 51.132808875465]]]}}, {"type": "Feature", "properties": {"treeID": "90", "XTOP": "338550.76", "YTOP": "5667116.9", "ZTOP": "0.05", "Zmax": "0.05", "Zmean": "0.035", "Zmin": "0.02", "ID": "42"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69242595759879, 51.1327203648049], [6.69242476082993, 51.1327242984695], [6.69243245823253, 51.1327275982651], [6.69243528742957, 51.1327281937456], [6.69242595759879, 51.1327203648049]]]}}, {"type": "Feature", "properties": {"treeID": "85", "XTOP": "338550.06", "YTOP": "5667112.77", "ZTOP": "0.05", "Zmax": "0.05", "Zmean": "0.0166666666666667", "Zmin": "-0.01", "ID": "43"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69245274714546, 51.1326962505233], [6.69242714158622, 51.1326908883821], [6.69242919794447, 51.1326955158344], [6.69244041615532, 51.1326998744549], [6.69244125953742, 51.1327001609189], [6.69245274714546, 51.1326962505233]]]}}, {"type": "Feature", "properties": {"treeID": "56", "XTOP": "338550.6", "YTOP": "5667096.12", "ZTOP": "7.72", "Zmax": "7.72", "Zmean": "2.274", "Zmin": "-0.05", "ID": "44"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69245170658382, 51.1325422554387], [6.69244381712226, 51.1325399411752], [6.69244231292106, 51.1325414404343], [6.69244104504199, 51.1325439336809], [6.69245948289887, 51.132552571986], [6.69246129902032, 51.1325534172825], [6.69246024676916, 51.1325487197138], [6.69245170658382, 51.1325422554387]]]}}, {"type": "Feature", "properties": {"treeID": "52", "XTOP": "338550.51", "YTOP": "5667095.04", "ZTOP": "9.3", "Zmax": "9.3", "Zmean": "5.25333333333333", "Zmin": "0.04", "ID": "45"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69247123885046, 51.1325002800254], [6.69245411866975, 51.1325082163997], [6.69244151157543, 51.1325317112678], [6.69244230958046, 51.132535774249], [6.69244516116804, 51.1325359204792], [6.69244545573565, 51.1325357464173], [6.69247123885046, 51.1325002800254]]]}}, {"type": "Feature", "properties": {"treeID": "93", "XTOP": "338552.69", "YTOP": "5667102.88", "ZTOP": "22.36", "Zmax": "22.36", "Zmean": "2.06105263157895", "Zmin": "-0.03", "ID": "46"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69248510057569, 51.1326092891872], [6.69247257979633, 51.1326023865825], [6.69246913010788, 51.1326027681767], [6.69245338024873, 51.132609022772], [6.69245084839858, 51.1326167974331], [6.6924656193314, 51.1326187079083], [6.69246779722929, 51.1326180313935], [6.69247928033421, 51.132614210845], [6.69248510057569, 51.1326092891872]]]}}, {"type": "Feature", "properties": {"treeID": "87", "XTOP": "338551.77", "YTOP": "5667114.22", "ZTOP": "0.03", "Zmax": "0.03", "Zmean": "0.0225", "Zmin": "0.02", "ID": "47"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69245091138785, 51.1327043986791], [6.69245021080953, 51.1327041150342], [6.69244900956276, 51.1327081385491], [6.69245156659226, 51.1327084588412], [6.69245091138785, 51.1327043986791]]]}}, {"type": "Feature", "properties": {"treeID": "96", "XTOP": "338553.99", "YTOP": "5667103.24", "ZTOP": "0.04", "Zmax": "0.04", "Zmean": "0.012", "Zmin": "-0.03", "ID": "48"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69249581254817, 51.1326008665563], [6.69247751242077, 51.1325951989433], [6.69247377655765, 51.1325984529173], [6.6924818166598, 51.1326063463332], [6.69248753328499, 51.132606369242], [6.69250127884163, 51.1326030430444], [6.69249581254817, 51.1326008665563]]]}}, {"type": "Feature", "properties": {"treeID": "154", "XTOP": "338553.51", "YTOP": "5667108.73", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "-0.00615384615384615", "Zmin": "-0.04", "ID": "49"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69249047153519, 51.1326649771424], [6.69248967352406, 51.1326609141615], [6.69247941127622, 51.132651717743], [6.69247821899422, 51.1326555615584], [6.69247727250064, 51.1326802759345], [6.69248008882216, 51.1326782629461], [6.69249047153519, 51.1326649771424]]]}}, {"type": "Feature", "properties": {"treeID": "159", "XTOP": "338555.71", "YTOP": "5667102.84", "ZTOP": "0.03", "Zmax": "0.03", "Zmean": "-0.007", "Zmin": "-0.04", "ID": "50"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69251108244196, 51.1326071039186], [6.69251675596071, 51.1325993912725], [6.69250984257813, 51.1326033020309], [6.69250113433885, 51.1326116742784], [6.69249979477582, 51.1326156051251], [6.69249725845272, 51.1326234696372], [6.69251108244196, 51.1326071039186]]]}}, {"type": "Feature", "properties": {"treeID": "163", "XTOP": "338559.91", "YTOP": "5667107.55", "ZTOP": "6.83", "Zmax": "6.83", "Zmean": "0.532222222222222", "Zmin": "-0.05", "ID": "51"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69256088448319, 51.1326346188254], [6.69254324067351, 51.1326272553487], [6.69253240159827, 51.1326238935444], [6.69253162146901, 51.1326424054554], [6.69253689511832, 51.1326484454902], [6.69254697033458, 51.1326556595669], [6.69254894718635, 51.1326561482812], [6.69257555741013, 51.13265856226], [6.69257815644448, 51.1326494398466], [6.69256088448319, 51.1326346188254]]]}}, {"type": "Feature", "properties": {"treeID": "164", "XTOP": "338559.72", "YTOP": "5667108.69", "ZTOP": "7.84", "Zmax": "7.84", "Zmean": "0.787", "Zmin": "-0.03", "ID": "52"}, "geometry": {"type": "Polygon", "coordinates": [[[6.692566918062, 51.1326569527099], [6.69256693150258, 51.13265668316], [6.69256457104121, 51.1326552874898], [6.6925562212188, 51.1326564717443], [6.69255040489545, 51.1326641815734], [6.69255105562793, 51.1326683315849], [6.69255506757552, 51.1326681409639], [6.692566918062, 51.1326569527099]]]}}, {"type": "Feature", "properties": {"treeID": "165", "XTOP": "338561.11", "YTOP": "5667108.64", "ZTOP": "6.86", "Zmax": "6.86", "Zmean": "0.759444444444444", "Zmin": "-0.03", "ID": "53"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69258679018917, 51.1326568952812], [6.69256559194368, 51.1326606140074], [6.69256761582851, 51.1326716264425], [6.6925695025151, 51.1326767901734], [6.69257970374905, 51.1326786104307], [6.69258867071403, 51.1326707829149], [6.69258679018917, 51.1326568952812]]]}}, {"type": "Feature", "properties": {"treeID": "167", "XTOP": "338561.94", "YTOP": "5667111.06", "ZTOP": "0.02", "Zmax": "0.02", "Zmean": "0.01", "Zmin": "0", "ID": "54"}, "geometry": {"type": "Polygon", "coordinates": [[[6.6926093492694, 51.1326774867805], [6.69260141945561, 51.132675981177], [6.69259755871106, 51.1326788729362], [6.69259157718697, 51.1326870291983], [6.69260800971343, 51.1326814176284], [6.6926093492694, 51.1326774867805]]]}}]}',)]
1
LetsamrIt 25 Mei 2021, 14:07

1 menjawab

Jawaban Terbaik

Gunakan ST_DumpPoints untuk mengakses setiap titik poligon Anda, lalu ST_X dan ST_Y untuk mendapatkan nilai bujur dan lintang, setelah itu kelompokkan lagi dengan gid yang dibuat dengan row_number(). Sesuatu seperti:

WITH data AS (
  SELECT '{"type": "FeatureCollection", "features": [{"type": "Feature", "properties": {"treeID": "99", "XTOP": "338535.01", "YTOP": "5667141.11", "ZTOP": "19.98", "Zmax": "19.98", "Zmean": "1.8232183908046", "Zmin": "-0.03", "ID": "1"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69223420722764, 51.1329165124371], [6.69222819326484, 51.1329081193748], [6.69221180776184, 51.1329013203209], [6.69219149698336, 51.1329073049609], [6.69217949340067, 51.1329244260894], [6.69217956546811, 51.1329430447612], [6.69218526127669, 51.1329578171713], [6.692200130301, 51.1329606289999], [6.69220511560768, 51.1329581192089], [6.69221372090576, 51.1329518135339], [6.69222707128915, 51.132939215911], [6.69223420722764, 51.1329165124371]]]}}, {"type": "Feature", "properties": {"treeID": "109", "XTOP": "338537.53", "YTOP": "5667129.66", "ZTOP": "18.9", "Zmax": "18.9", "Zmean": "0.56044776119403", "Zmin": "-0.04", "ID": "2"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69226547705025, 51.1328283605685], [6.69226477705279, 51.1328251989051], [6.69225817843627, 51.1328170641171], [6.69225114186537, 51.1328119785883], [6.69223507726445, 51.1328073443943], [6.69221209610711, 51.1328439453134], [6.69221314385783, 51.1328487327339], [6.6922180820046, 51.1328586335107], [6.69223237530096, 51.1328615239076], [6.6922479146764, 51.132859492291], [6.69225680945743, 51.1328559804184], [6.69226797350914, 51.1328413609834], [6.69226547705025, 51.1328283605685]]]}}, {"type": "Feature", "properties": {"treeID": "112", "XTOP": "338537.83", "YTOP": "5667124.38", "ZTOP": "0.03", "Zmax": "0.03", "Zmean": "0.0125", "Zmin": "-0.01", "ID": "3"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69225693096545, 51.1327847516032], [6.69224029730571, 51.1327915283526], [6.69224729022912, 51.1327917563467], [6.69225693096545, 51.1327847516032]]]}}, {"type": "Feature", "properties": {"treeID": "130", "XTOP": "338538.62", "YTOP": "5667117.07", "ZTOP": "0.02", "Zmax": "0.02", "Zmean": "0.005", "Zmin": "0", "ID": "4"}, "geometry": {"type": "Polygon", "coordinates": [[[6.6922737133986, 51.1327262632073], [6.6922618472268, 51.1327262987605], [6.69226115502939, 51.1327287134319], [6.69226892021352, 51.1327277874716], [6.6922737133986, 51.1327262632073]]]}}, {"type": "Feature", "properties": {"treeID": "15", "XTOP": "338540.55", "YTOP": "5667139.9", "ZTOP": "31.6", "Zmax": "31.6", "Zmean": "2.44184466019418", "Zmin": "-0.05", "ID": "5"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69230806364113, 51.1328973746132], [6.69230046595516, 51.1328863420758], [6.69228392304113, 51.1328855657973], [6.6922669902971, 51.1328926064613], [6.69225803434834, 51.1329116764704], [6.69225290879938, 51.1329456619351], [6.6922586959951, 51.1329500032911], [6.69229038460729, 51.132953776729], [6.69230233106635, 51.1329320676033], [6.69230806364113, 51.1328973746132]]]}}, {"type": "Feature", "properties": {"treeID": "10", "XTOP": "338538.93", "YTOP": "5667143.93", "ZTOP": "0.01", "Zmax": "0.01", "Zmea], [6.69230370484838, 51.1326694745926],  [6.69227418635346, 51.1329919505805], [6.69226656451147, 51.1329900013425], [6.69226802666089, 51.1329979447873], [6.6922749888312, 51.1329959237125], [6.69227531534934, 51.1329922426842]]]}}, {"type": "Feature", "properties": {"treeID": "21", "XTOP": "338542.76", "YTOP": "5667130.18", "ZTOP": "30.67", "Zmax": "30.67", "Zmean": "1.13138888888889", "Zmin": "-0.06", "ID": "9"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69234522009268, 51.1328287656302], [6.69234539656757, 51.13281662743], [6.69233976793339, 51.1328005072778], [6.69233510468195, 51.1327994258963], [6.69231594261322, 51.1327967092098], [6.69230144032154, 51.1328123420077], [6.69229418909355, 51.1328316255505], [6.6922839366462, 51.1328623619585], [6.69230554766026, 51.1328704333619], [6.6923202307875, 51.1328655068032], [6.69233852665545, 51.1328483300023], [6.69234522009268, 51.1328287656302]]]}}, {"type": "Feature", "properties": {"treeID": "14", "XTOP": "338541.07", "YTOP": "5667143.99", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "0.00222222222222222", "Zmin": "-0.02", "ID": "10"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69230245173697, 51.1329755102324], [6.69228611158755, 51.1329649346718], [6.69228477199513, 51.1329688655154], [6.6922916326824, 51.1329832112248], [6.69229664819236, 51.1329858285149], [6.69230324973615, 51.1329795732142], [6.69230245173697, 51.1329755102324]]]}}, {"type": "Feature", "properties": {"treeID": "156", "XTOP": "338545.92", "YTOP": "5667098.24", "ZTOP": "0.05", "Zmax": "0.05", "Zmean": "-0.00357142857142857", "Zmin": "-0.03", "ID": "11"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69238288143095, 51.1325551070459], [6.69238261041295, 51.1325490758227], [6.69237336012785, 51.1325453856142], [6.69235828511823, 51.1325467069068], [6.69233294445344, 51.1325560998718], [6.6923250560129, 51.1325709638555], [6.6923352106771, 51.1325823166859], [6.69235802167206, 51.1325921205998], [6.69237487203162, 51.1325723969819], [6.69238288143095, 51.1325551070459]]]}}, {"type": "Feature", "properties": {"treeID": "18", "XTOP": "338545.23", "YTOP": "5667136.37", "ZTOP": "24.88", "Zmax": "24.88", "Zmean": "0.540434782608696", "Zmin": "-0.05", "ID": "12"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69235678858897, 51.1328891627965], [6.69235599058639, 51.132885099815], [6.69235519258393, 51.1328810368335], [6.69231474307826, 51.1328809578104], [6.6923132836687, 51.1328815585682], [6.69231194408122, 51.1328854894122], [6.69232420315314, 51.1329291716723], [6.69232717126271, 51.1329269818072], [6.6923486464278, 51.1329005141752], [6.69235678858897, 51.1328891627965]]]}}, {"type": "Feature", "properties": {"treeID": "63", "XTOP": "338543.07", "YTOP": "5667145.84", "ZTOP": "25.67", "Zmax": "25.67", "Zmean": "2.558", "Zmin": "-0.04", "ID": "13"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69232417872706, 51.1329841235518], [6.69232540687686, 51.132979560939], [6.69232337624525, 51.1329801504201], [6.69231245529376, 51.1329841619284], [6.69230775559946, 51.1329866773625], [6.69231325329394, 51.1329882249101], [6.69231820665403, 51.1329892220812], [6.69232417872706, 51.1329841235518]]]}}, {"type": "Feature", "properties": {"treeID": "61", "XTOP": "338543.4", "YTOP": "5667144.16", "ZTOP": "25.57", "Zmax": "25.57", "Zmean": "2.82666666666667", "Zmin": "-0.03", "ID": "14"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69232597202458, 51.1329653618288], [6.69231418138504, 51.1329667479548], [6.69231283283347, 51.1329708584986], [6.6923220871, 51.13297733688], [6.69232257376358, 51.1329761772883], [6.69232597202458, 51.1329653618288]]]}}, {"type": "Feature", "properties": {"treeID": "29", "XTOP": "338543.72", "YTOP": "5667121.11", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "-0.0025", "Zmin": "-0.02", "ID": "15"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69232658093919, 51.1327553676854], [6.69232524135684, 51.1327592985297], [6.69233193855203, 51.1327654566152], [6.69233286706724, 51.132764035931], [6.69232658093919, 51.1327553676854]]]}}, {"type": "Feature", "properties": {"treeID": "65", "XTOP": "338544.79", "YTOP": "5667143.85", "ZTOP": "20.02", "Zmax": "20.02", "Zmean": "3.63", "Zmin": "-0.03", "ID": "16"}, "geometry": {"type": "Polygon", "coordinates": [[[6.6923428169042, 51.1329514841034], [6.69233755943823, 51.1329537187123], [6.69231988070991, 51.1329642522548], [6.69234076488885, 51.1329697010888], [6.6923428169042, 51.1329514841034]]]}}, {"type": "Feature", "properties": {"treeID": "60", "XTOP": "338546.17", "YTOP": "5667145.3", "ZTOP": "0", "Zmax": "0", "Zmean": "-0.015", "Zmin": "-0.05", "ID": "17"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69236137127294, 51.1329807206157], [6.69232498120898, 51.1329880966835], [6.69234973181995, 51.1329848078818], [6.69236137127294, 51.1329807206157]]]}}, {"type": "Feature", "properties": {"treeID": "31", "XTOP": "338543.99", "YTOP": "5667118.88", "ZTOP": "0.02", "Zmax": "0.02", "Zmean": "0.002", "Zmin": "-0.02", "ID": "18"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69234418226092, 51.1327521176245], [6.69233772196369, 51.13274407551], [6.69234011596091, 51.1327562644551], [6.69234298548423, 51.1327560512882], [6.69234418226092, 51.1327521176245]]]}}, {"type": "Feature", "properties": {"treeID": "26", "XTOP": "338544.98", "YTOP": "5667121.52", "ZTOP": "0", "Zmax": "0", "Zmean": "-0.01", "Zmin": "-0.02", "ID": "19"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69234924805998, 51.1327709248176], [6.69235067667721, 51.1327680749915], [6.69233657766584, 51.1327756212988], [6.69234924805998, 51.1327709248176]]]}}, {"type": "Feature", "properties": {"treeID": "67", "XTOP": "338545.02", "YTOP": "5667137.62", "ZTOP": "25.01", "Zmax": "25.01", "Zmean": "6.24375", "Zmin": "-0.01", "ID": "20"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69235010410836, 51.1329085474692], [6.69234521738939, 51.1329090805678], [6.6923440435884, 51.1329096869642], [6.69233223663389, 51.1329257327289], [6.6923362782374, 51.1329335472182], [6.69234509352783, 51.1329258966033], [6.69235010410836, 51.1329085474692]]]}}, {"type": "Feature", "properties": {"treeID": "36", "XTOP": "338544.43", "YTOP": "5667117.91", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "-0.00375", "Zmin": "-0.01", "ID": "21"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69237562109752, 51.1327322323099], [6.69237482309554, 51.1327281693285], [6.69235193413251, 51.1327285269187], [6.69234222230857, 51.1327340912521], [6.69234443996302, 51.1327354841077], [6.692360871385, 51.1327356286082], [6.69237224251332, 51.1327340543194], [6.69237562109752, 51.1327322323099]]]}}, {"type": "Feature", "properties": {"treeID": "57", "XTOP": "338544.53", "YTOP": "5667145.36", "ZTOP": "20.06", "Zmax": "20.06", "Zmean": "1.81363636363636", "Zmin": "-0.04", "ID": "22"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69236168606955, 51.1329686750848], [6.69234981766078, 51.1329572884265], [6.69233121431728, 51.1329720308224], [6.69233265797694, 51.1329832116837], [6.69233499604409, 51.1329850566084], [6.69235175406655, 51.1329815200828], [6.69236168606955, 51.1329686750848]]]}}, {"type": "Feature", "properties": {"treeID": "38", "XTOP": "338546.2", "YTOP": "5667114.75", "ZTOP": "29.53", "Zmax": "29.53", "Zmean": "0.984", "Zmin": "-0.04", "ID": "23"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69240357446646, 51.1326905130879], [6.69240477123615, 51.1326865794235], [6.69239745859457, 51.1326784305479], [6.69238504534337, 51.132669371535], [6.69236010166315, 51.1326794019119], [6.69234892871975, 51.1326942010569], [6.69234996521659, 51.1327106802463], [6.69235580558459, 51.1327168214155], [6.6923846418739, 51.132723326611], [6.69240357446646, 51.1326905130879]]]}}, {"type": "Feature", "properties": {"treeID": "32", "XTOP": "338546", "YTOP": "5667119.88", "ZTOP": "0.05", "Zmax": "0.05", "Zmean": "0.00909090909090909", "Zmin": "-0.01", "ID": "24"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69237129653497, 51.1327558906762], [6.69236597751466, 51.132753627151], [6.69235446357724, 51.13275519862], [6.69236076200149, 51.1327693533492], [6.69236379167681, 51.1327716616186], [6.69237129653497, 51.1327558906762]]]}}, {"type": "Feature", "properties": {"treeID": "69", "XTOP": "338546.37", "YTOP": "5667131.07", "ZTOP": "24.42", "Zmax": "24.42", "Zmean": "3.1275", "Zmin": "-0.06", "ID": "25"}, "geometry": {"type": "Polygon", "coordinates": [[[6.692367683902, 51.132857000059], [6.69236624747223, 51.1328542735503], [6.69236167325165, 51.1328542731882], [6.69235290047982, 51.132872537375], [6.69235391240058, 51.1328780435939], [6.69236477334893, 51.1328838341828], [6.69237416317368, 51.1328818610268], [6.69237488615718, 51.1328816954225], [6.69237607397357, 51.1328779414585], [6.692367683902, 51.132857000059]]]}}, {"type": "Feature", "properties": {"treeID": "44", "XTOP": "338546.63", "YTOP": "5667107.76", "ZTOP": "0.03", "Zmax": "0.03", "Zmean": "0.008", "Zmin": "-0.02", "ID": "26"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69239099507016, 51.1326417879607], [6.69239043281634, 51.1326415969845], [6.69237157767037, 51.1326441927313], [6.6923787531271, 51.1326464929044], [6.69239099507016, 51.1326417879607]]]}}, {"type": "Feature", "properties": {"treeID": "66", "XTOP": "338546.44", "YTOP": "5667136.28", "ZTOP": "19.19", "Zmax": "19.19", "Zmean": "0.670526315789474", "Zmin": "-0.07", "ID": "27"}, "geometry": {"type": "Polygon", "coordinates": [[[6.6923969524934, 51.132889236167], [6.69240257401973, 51.1328739676745], [6.69240177153279, 51.1328699945432], [6.69239229830187, 51.1328650407981], [6.69236483102158, 51.1328855441509], [6.69236343376573, 51.1328877650275], [6.69235954885797, 51.1328997400803], [6.69235919155878, 51.1329011720412], [6.69235877978974, 51.132929494508], [6.69236737943046, 51.1329290347061], [6.6923969524934, 51.132889236167]]]}}, {"type": "Feature", "properties": {"treeID": "79", "XTOP": "338548.03", "YTOP": "5667123.55", "ZTOP": "23.84", "Zmax": "23.84", "Zmean": "2.05224", "Zmin": "-0.04", "ID": "28"}, "geometry": {"type": "Polygon", "coordinates": [[[6.6924429729393, 51.1327689976185], [6.69244286654913, 51.1327653979823], [6.69243423337915, 51.1327550645196], [6.69241043104876, 51.1327479392038], [6.69238315432753, 51.1327531567944], [6.69237434125933, 51.1327722296329], [6.6923670295201, 51.1328041933], [6.69236989008509, 51.1328041598324], [6.69240829603253, 51.1328022198506], [6.69242937786243, 51.1328008372528], [6.6924313860824, 51.1328006970194], [6.6924429729393, 51.1327689976185]]]}}, {"type": "Feature", "properties": {"treeID": "55", "XTOP": "338548.75", "YTOP": "5667095.03", "ZTOP": "8.01", "Zmax": "8.01", "Zmean": "1.73375", "Zmin": "-0.02", "ID": "29"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69243075985633, 51.1325380645258], [6.69241638266022, 51.1325311252681], [6.6924003354881, 51.1325290097154], [6.69238767860756, 51.1325334366535], [6.69238687049965, 51.1325353094071], [6.69238633903741, 51.1325373674987], [6.69239393668944, 51.1325484000307], [6.69240127343053, 51.1325474656039], [6.69242272587979, 51.1325443814947], [6.69242896108707, 51.1325397378466], [6.69243075985633, 51.1325380645258]]]}}, {"type": "Feature", "properties": {"treeID": "70", "XTOP": "338547.21", "YTOP": "5667133.26", "ZTOP": "19.04", "Zmax": "19.04", "Zmean": "7.587", "Zmin": "-0.01", "ID": "30"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69238145412797, 51.1328646468468], [6.69238065164298, 51.1328606737154], [6.69237659705414, 51.1328731850495], [6.6923767040138, 51.1328739066682], [6.69237726178975, 51.1328741874945], [6.69238145412797, 51.1328646468468]]]}}, {"type": "Feature", "properties": {"treeID": "74", "XTOP": "338547.55", "YTOP": "5667131.01", "ZTOP": "0.04", "Zmax": "0.04", "Zmean": "0.012", "Zmin": "-0.02", "ID": "31"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69239172489848, 51.1328536073019], [6.69238312527199, 51.1328540671058], [6.69237985363871, 51.132856610734], [6.6923819240151, 51.1328580906197], [6.69238918854273, 51.132861471811], [6.69239856825426, 51.1328654343878], [6.69240096904597, 51.1328660214118], [6.69239172489848, 51.1328536073019]]]}}, {"type": "Feature", "properties": {"treeID": "80", "XTOP": "338548.04", "YTOP": "5667124.62", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "-0.0025", "Zmin": "-0.02", "ID": "32"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69239739925307, 51.1327971480654], [6.69239298576452, 51.132796791124], [6.69238795625361, 51.1327973214054], [6.69239805893436, 51.1328011183777], [6.69239739925307, 51.1327971480654]]]}}, {"type": "Feature", "properties": {"treeID": "40", "XTOP": "338547.96", "YTOP": "5667108.5", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "-0.0025", "Zmin": "-0.02", "ID": "33"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69240998291263, 51.1326451307648], [6.69240160229753, 51.1326440659404], [6.69239994632672, 51.1326457420797], [6.69239906595609, 51.1326519304501], [6.69240998291263, 51.1326451307648]]]}}, {"type": "Feature", "properties": {"treeID": "51", "XTOP": "338549.55", "YTOP": "5667099.69", "ZTOP": "28.56", "Zmax": "28.56", "Zmean": "1.40655462184874", "Zmin": "-0.05", "ID": "34"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69247079011754, 51.1325580116217], [6.69245037149921, 51.1325460964348], [6.69243924236255, 51.1325428188342], [6.69243452536925, 51.1325428156564], [6.69242975909111, 51.1325438008279], [6.69241225585337, 51.1325479522214], [6.69241019835906, 51.1325490808041], [6.69240430471269, 51.1325640741102], [6.69240379508097, 51.1325685609698], [6.69240814296053, 51.132584565898], [6.69245845290714, 51.1325932937497], [6.69247496883839, 51.1325946091019], [6.69248554078401, 51.1325947278559], [6.69249969626032, 51.1325946475329], [6.69250137632623, 51.1325838880893], [6.69247079011754, 51.1325580116217]]]}}, {"type": "Feature", "properties": {"treeID": "117", "XTOP": "338548.5", "YTOP": "5667127.43", "ZTOP": "18.49", "Zmax": "18.49", "Zmean": "4.635", "Zmin": "0.01", "ID": "35"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69240202207781, 51.1328219723839], [6.69240191063732, 51.1328213406152], [6.69239829572496, 51.1328221686377], [6.69239207839791, 51.1328264528836], [6.69240202207781, 51.1328219723839]]]}}, {"type": "Feature", "properties": {"treeID": "54", "XTOP": "338550.23", "YTOP": "5667095.79", "ZTOP": "28.28", "Zmax": "28.28", "Zmean": "4.0928", "Zmin": "-0.13", "ID": "36"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69242512731351, 51.1325191562094], [6.69241898619832, 51.1325219130083], [6.69240985012039, 51.1325273987744], [6.69242131748103, 51.1325353598516], [6.69243571765166, 51.1325389718414], [6.69244038535995, 51.1325399633687], [6.69242512731351, 51.1325191562094]]]}}, {"type": "Feature", "properties": {"treeID": "76", "XTOP": "338548.89", "YTOP": "5667125.63", "ZTOP": "18.42", "Zmax": "18.42", "Zmean": "4.615", "Zmin": "0", "ID": "37"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69240467158793, 51.1328061055907], [6.69239965494465, 51.1328092443403], [6.69240045294999, 51.1328133073216], [6.69240576416321, 51.1328099945102], [6.69240467158793, 51.1328061055907]]]}}, {"type": "Feature", "properties": {"treeID": "86", "XTOP": "338549.36", "YTOP": "5667113.32", "ZTOP": "0.02", "Zmax": "0.02", "Zmean": "-0.0025", "Zmin": "-0.02", "ID": "38"}, "geometry": {"type": "Polygon", "coordinates": [[[6.692416898888, 51.1326956327962], [6.69241388265633, 51.1326930549783], [6.69241254308334, 51.1326969858238], [6.69241135079452, 51.1327008296383], [6.692416898888, 51.1326956327962]]]}}, {"type": "Feature", "properties": {"treeID": "91", "XTOP": "338551.02", "YTOP": "5667110.53", "ZTOP": "22.95", "Zmax": "22.95", "Zmean": "2.11378378378378", "Zmin": "-0.05", "ID": "39"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69247728045234, 51.132639983688], [6.69247568891363, 51.1326317678761], [6.69246706806426, 51.1326269209013], [6.69243739235177, 51.1326229174303], [6.6924290351972, 51.1326385816137], [6.69242123132997, 51.1326546164724], [6.69241305614816, 51.132675230859], [6.69241317426148, 51.132687194998], [6.69241545016231, 51.1326874198028], [6.69244513096439, 51.1326884554123], [6.69245527900007, 51.1326884758624], [6.69246693629026, 51.1326840291846], [6.6924694177205, 51.132682998908], [6.69247728045234, 51.132639983688]]]}}, {"type": "Feature", "properties": {"treeID": "53", "XTOP": "338550.25", "YTOP": "5667095.36", "ZTOP": "9.45", "Zmax": "9.45", "Zmean": "4.40666666666667", "Zmin": "-0.04", "ID": "40"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69244896373978, 51.1325112624831], [6.69244803132252, 51.1325098950007], [6.69242991599137, 51.1325177217883], [6.69242797832923, 51.1325221804577], [6.69242845545341, 51.132524078583], [6.69242906642011, 51.1325261592271], [6.69243706169811, 51.1325349511456], [6.69243765531382, 51.1325345131719], [6.69244040051127, 51.1325310597659], [6.69244896373978, 51.1325112624831]]]}}, {"type": "Feature", "properties": {"treeID": "125", "XTOP": "338549.71", "YTOP": "5667126.94", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "0.00142857142857143", "Zmin": "-0.01", "ID": "41"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69242197273061, 51.132808875465], [6.69241648344239, 51.1328100262402], [6.6924151393854, 51.1328140469356], [6.69241394261294, 51.1328179806], [6.6924242284291, 51.1328209717395], [6.69242511213773, 51.1328204495541], [6.69242197273061, 51.132808875465]]]}}, {"type": "Feature", "properties": {"treeID": "90", "XTOP": "338550.76", "YTOP": "5667116.9", "ZTOP": "0.05", "Zmax": "0.05", "Zmean": "0.035", "Zmin": "0.02", "ID": "42"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69242595759879, 51.1327203648049], [6.69242476082993, 51.1327242984695], [6.69243245823253, 51.1327275982651], [6.69243528742957, 51.1327281937456], [6.69242595759879, 51.1327203648049]]]}}, {"type": "Feature", "properties": {"treeID": "85", "XTOP": "338550.06", "YTOP": "5667112.77", "ZTOP": "0.05", "Zmax": "0.05", "Zmean": "0.0166666666666667", "Zmin": "-0.01", "ID": "43"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69245274714546, 51.1326962505233], [6.69242714158622, 51.1326908883821], [6.69242919794447, 51.1326955158344], [6.69244041615532, 51.1326998744549], [6.69244125953742, 51.1327001609189], [6.69245274714546, 51.1326962505233]]]}}, {"type": "Feature", "properties": {"treeID": "56", "XTOP": "338550.6", "YTOP": "5667096.12", "ZTOP": "7.72", "Zmax": "7.72", "Zmean": "2.274", "Zmin": "-0.05", "ID": "44"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69245170658382, 51.1325422554387], [6.69244381712226, 51.1325399411752], [6.69244231292106, 51.1325414404343], [6.69244104504199, 51.1325439336809], [6.69245948289887, 51.132552571986], [6.69246129902032, 51.1325534172825], [6.69246024676916, 51.1325487197138], [6.69245170658382, 51.1325422554387]]]}}, {"type": "Feature", "properties": {"treeID": "52", "XTOP": "338550.51", "YTOP": "5667095.04", "ZTOP": "9.3", "Zmax": "9.3", "Zmean": "5.25333333333333", "Zmin": "0.04", "ID": "45"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69247123885046, 51.1325002800254], [6.69245411866975, 51.1325082163997], [6.69244151157543, 51.1325317112678], [6.69244230958046, 51.132535774249], [6.69244516116804, 51.1325359204792], [6.69244545573565, 51.1325357464173], [6.69247123885046, 51.1325002800254]]]}}, {"type": "Feature", "properties": {"treeID": "93", "XTOP": "338552.69", "YTOP": "5667102.88", "ZTOP": "22.36", "Zmax": "22.36", "Zmean": "2.06105263157895", "Zmin": "-0.03", "ID": "46"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69248510057569, 51.1326092891872], [6.69247257979633, 51.1326023865825], [6.69246913010788, 51.1326027681767], [6.69245338024873, 51.132609022772], [6.69245084839858, 51.1326167974331], [6.6924656193314, 51.1326187079083], [6.69246779722929, 51.1326180313935], [6.69247928033421, 51.132614210845], [6.69248510057569, 51.1326092891872]]]}}, {"type": "Feature", "properties": {"treeID": "87", "XTOP": "338551.77", "YTOP": "5667114.22", "ZTOP": "0.03", "Zmax": "0.03", "Zmean": "0.0225", "Zmin": "0.02", "ID": "47"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69245091138785, 51.1327043986791], [6.69245021080953, 51.1327041150342], [6.69244900956276, 51.1327081385491], [6.69245156659226, 51.1327084588412], [6.69245091138785, 51.1327043986791]]]}}, {"type": "Feature", "properties": {"treeID": "96", "XTOP": "338553.99", "YTOP": "5667103.24", "ZTOP": "0.04", "Zmax": "0.04", "Zmean": "0.012", "Zmin": "-0.03", "ID": "48"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69249581254817, 51.1326008665563], [6.69247751242077, 51.1325951989433], [6.69247377655765, 51.1325984529173], [6.6924818166598, 51.1326063463332], [6.69248753328499, 51.132606369242], [6.69250127884163, 51.1326030430444], [6.69249581254817, 51.1326008665563]]]}}, {"type": "Feature", "properties": {"treeID": "154", "XTOP": "338553.51", "YTOP": "5667108.73", "ZTOP": "0.01", "Zmax": "0.01", "Zmean": "-0.00615384615384615", "Zmin": "-0.04", "ID": "49"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69249047153519, 51.1326649771424], [6.69248967352406, 51.1326609141615], [6.69247941127622, 51.132651717743], [6.69247821899422, 51.1326555615584], [6.69247727250064, 51.1326802759345], [6.69248008882216, 51.1326782629461], [6.69249047153519, 51.1326649771424]]]}}, {"type": "Feature", "properties": {"treeID": "159", "XTOP": "338555.71", "YTOP": "5667102.84", "ZTOP": "0.03", "Zmax": "0.03", "Zmean": "-0.007", "Zmin": "-0.04", "ID": "50"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69251108244196, 51.1326071039186], [6.69251675596071, 51.1325993912725], [6.69250984257813, 51.1326033020309], [6.69250113433885, 51.1326116742784], [6.69249979477582, 51.1326156051251], [6.69249725845272, 51.1326234696372], [6.69251108244196, 51.1326071039186]]]}}, {"type": "Feature", "properties": {"treeID": "163", "XTOP": "338559.91", "YTOP": "5667107.55", "ZTOP": "6.83", "Zmax": "6.83", "Zmean": "0.532222222222222", "Zmin": "-0.05", "ID": "51"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69256088448319, 51.1326346188254], [6.69254324067351, 51.1326272553487], [6.69253240159827, 51.1326238935444], [6.69253162146901, 51.1326424054554], [6.69253689511832, 51.1326484454902], [6.69254697033458, 51.1326556595669], [6.69254894718635, 51.1326561482812], [6.69257555741013, 51.13265856226], [6.69257815644448, 51.1326494398466], [6.69256088448319, 51.1326346188254]]]}}, {"type": "Feature", "properties": {"treeID": "164", "XTOP": "338559.72", "YTOP": "5667108.69", "ZTOP": "7.84", "Zmax": "7.84", "Zmean": "0.787", "Zmin": "-0.03", "ID": "52"}, "geometry": {"type": "Polygon", "coordinates": [[[6.692566918062, 51.1326569527099], [6.69256693150258, 51.13265668316], [6.69256457104121, 51.1326552874898], [6.6925562212188, 51.1326564717443], [6.69255040489545, 51.1326641815734], [6.69255105562793, 51.1326683315849], [6.69255506757552, 51.1326681409639], [6.692566918062, 51.1326569527099]]]}}, {"type": "Feature", "properties": {"treeID": "165", "XTOP": "338561.11", "YTOP": "5667108.64", "ZTOP": "6.86", "Zmax": "6.86", "Zmean": "0.759444444444444", "Zmin": "-0.03", "ID": "53"}, "geometry": {"type": "Polygon", "coordinates": [[[6.69258679018917, 51.1326568952812], [6.69256559194368, 51.1326606140074], [6.69256761582851, 51.1326716264425], [6.6925695025151, 51.1326767901734], [6.69257970374905, 51.1326786104307], [6.69258867071403, 51.1326707829149], [6.69258679018917, 51.1326568952812]]]}}, {"type": "Feature", "properties": {"treeID": "167", "XTOP": "338561.94", "YTOP": "5667111.06", "ZTOP": "0.02", "Zmax": "0.02", "Zmean": "0.01", "Zmin": "0", "ID": "54"}, "geometry": {"type": "Polygon", "coordinates": [[[6.6926093492694, 51.1326774867805], [6.69260141945561, 51.132675981177], [6.69259755871106, 51.1326788729362], [6.69259157718697, 51.1326870291983], [6.69260800971343, 51.1326814176284], [6.6926093492694, 51.1326774867805]]]}}]}'::json AS featuresCollection
)
SELECT gid,type, properties,array_to_string(array_agg(x||' '||y ORDER BY gid),',') FROM (
  SELECT
    ROW_NUMBER() OVER () AS gid,
    ST_X((ST_DumpPoints((ST_GeomFromGeoJSON(feature->>'geometry')))).geom) x,       
    ST_Y((ST_DumpPoints((ST_GeomFromGeoJSON(feature->>'geometry')))).geom) y,
    feature->>'type' AS type,
    feature->>'properties' AS properties
  FROM (SELECT json_array_elements(featuresCollection->'features') AS feature 
        FROM data) AS f) j
GROUP BY gid,type,properties;

perbarui:

query = """  WITH data AS (
        SELECT '{featuresArray}'::json AS featuresCollection
        )
        SELECT gid,type::text, properties::text,array_to_string(array_agg(x||' '||y),','), ST_AsText(ST_GeomFromGeoJSON(feature->>'geometry')) AS geom
         FROM (
        SELECT
        ROW_NUMBER() OVER () AS gid,

        ST_X((ST_DumpPoints((ST_GeomFromGeoJSON(feature->>'geometry')))).geom) x,
        ST_Y((ST_DumpPoints((ST_GeomFromGeoJSON(feature->>'geometry')))).geom) y,
        feature->'type' AS type,
        feature->'properties' AS properties,
        FROM (SELECT json_array_elements(featuresCollection->'features') AS feature FROM data) AS f) j
        GROUP BY gid,type::text,properties::text
        ORDER BY gid;""".format(featuresArray=featuresArray)

Demo: db<>fiddle

1
LetsamrIt 25 Mei 2021, 12:51