3

enter image description here

Hi Guys is it possible to use regexp_split_to_table to split my column on 2 different levels. AS in split by ';' and then split by ' '. I have this "-29.617356578819454 30.394328366965055 0.0 0.0;-29.61735976394266 30.39434471167624 0.0 0.0;" a column called "RPT_FTR_TRACE". ; seperates each coordinates and a space seperate long from lat. I want regexp_split_to_table to split it for me so my final query have "-29.617356578819454 30.394328366965055 0.0 0.0; in one column and long=-29.617356578819454 column and lat= 30.394328366965055 column

WITH a AS (
         SELECT DISTINCT "_URI",
            "_SUBMISSION_DATE","INFO_TEAM_NAME","INFO_SUPERVISOR_NAME","INFO_LOCATION","RPT_FTR_WIDTH",
            "RPT_FTR_TRACE" AS t
           FROM odk_duct.duct_monitoring_core
        )/* selection from odk_duct.duct_monitoring_core table,*/ 
    ,b AS (
    SELECT a."_URI",
           a."_SUBMISSION_DATE",initcap(a."INFO_TEAM_NAME"),"RPT_FTR_TRACE",
           b.p,
           b.id

           FROM odk_duct.duct_monitoring_core a,
           LATERAL regexp_split_to_table("RPT_FTR_TRACE", ';'::text) WITH ORDINALITY b(p, id)
           WHERE b.p <> ''::text
            ) select * from b
Improve this question
7
  • 1
    It would be much better to edit the question with actual SQL (and maybe less decimal places :-)). Screen shots are very unpleasant to try and understand.
    – John Powell
    Commented Apr 20, 2018 at 14:39
  • you can use (string to array) for that to insert lat, lon in separate columns. I can add a query for that if you like
    – Moh
    Commented Apr 20, 2018 at 14:46
  • @JohnPowellakaBarça i have included the sql.
    – Senyo Aborgah
    Commented Apr 23, 2018 at 10:05
  • @User2009 please add a query so i see and understand what you mean
    – Senyo Aborgah
    Commented Apr 23, 2018 at 10:05
  • 1
    You mean something like what I did for you in your last question? You should be able to use the same technique for regexp to table also the second round. But I used regexp_to_array in second round. gis.stackexchange.com/a/279872/960
    – Nicklas Avén
    Commented Apr 23, 2018 at 12:03

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.