Android Question Spatialite split string

[sigster]

Hi

How can i split the string so i only have
cordinate x and y

POINT(2550012.664 196252.944)

DBCon.Open(File.DirInternal, "test.db", sqlcostanti.SQLITE_OPEN_READONLY)
DBRec = DBCon.GetTable("SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(350012.664 396252.944)', 3057),8088))")
Log(DBRec.Rows(0,0))

 

[tchart]

ST_Transform returns a geometry which you should be able to just do ST_X and ST_Y

like this


SELECT ST_X(ST_Transform(your_point_column, target_srid)) AS x,
ST_Y(ST_Transform(your_point_column, target_srid)) AS y
FROM your_table;

 

[sigster]

Something like this ?

How do i get x,y to string

DBRec = DBCon.GetTable("Select *, ST_X(ST_Transform(350012.664, 8088)) As x, ST_Y(ST_Transform(396252.944, 8088)) As y from spatial_ref_sys")

 

[DonManfred]

I strongly advice to learn on how sql works in b4x.
Instead of GetTable you can query results from a Database.

B4X Programming Video Tutorials

www.b4x.com

Look for "SQL & Relational Databases"

 

[emexes]

If you just want to extract the coordinates from your example string, this sub might do it:

Sub SplitPoint(P As String) As Double()
 
    Dim Temp() As String = Regex.Split("(\(|\ +|\))", P)    'separators are "(", ")", and one or more contiguous spaces " "
 
    If Temp.Length >= 2 Then
        If IsNumber(Temp(1)) And IsNumber(Temp(2)) Then
            Return Array As Double(Temp(1), Temp(2))
        End If
    End If

    Return Array As Double()
 
End Sub

It returns an array of numeric values (but is easy to strip back to strings) with either:
- two elements, if the string is valid, or
- zero elements (ie empty array), if the string is not valid.

You can test it with this:

Dim TestPoints() As String = Array As String(  _
    "POINT(2550012.664 196252.944)",           _
    "POINT(2550012  196252)",                  _
    "POINX(2550012.664 196252.944)",           _
    "PT(2550012.664 196252.944)",              _
    "P O I N T(123  456)",                     _
    "(2550012.664 196252.944)"                 _
)

For Each Point As String In TestPoints
    Dim RA() As Double = SplitPoint(Point)
    If RA.Length = 2 Then
        Log("coordinates are " & RA(0) & " and " & RA(1))
    Else
        Log("unable to parse point """ & Point & """")
    End If
Next

Waiting for debugger to connect...
Program started.
coordinates are 2550012.664 and 196252.944
coordinates are 2550012 and 196252
coordinates are 2550012.664 and 196252.944
coordinates are 2550012.664 and 196252.944
unable to parse point "P O I N T(123  456)"
coordinates are 2550012.664 and 196252.944

 

[sigster]

Thanks for your replay

 

[tchart]

If you just want to extract the coordinates from your example string, this sub might do it:

Sub SplitPoint(P As String) As Double()
 
    Dim Temp() As String = Regex.Split("(\(|\ +|\))", P)    'separators are "(", ")", and one or more contiguous spaces " "
 
    If Temp.Length >= 2 Then
        If IsNumber(Temp(1)) And IsNumber(Temp(2)) Then
            Return Array As Double(Temp(1), Temp(2))
        End If
    End If

    Return Array As Double()
 
End Sub

It returns an array of numeric values (but is easy to strip back to strings) with either:
- two elements, if the string is valid, or
- zero elements (ie empty array), if the string is not valid.

You can test it with this:

Dim TestPoints() As String = Array As String(  _
    "POINT(2550012.664 196252.944)",           _
    "POINT(2550012  196252)",                  _
    "POINX(2550012.664 196252.944)",           _
    "PT(2550012.664 196252.944)",              _
    "P O I N T(123  456)",                     _
    "(2550012.664 196252.944)"                 _
)

For Each Point As String In TestPoints
    Dim RA() As Double = SplitPoint(Point)
    If RA.Length = 2 Then
        Log("coordinates are " & RA(0) & " and " & RA(1))
    Else
        Log("unable to parse point """ & Point & """")
    End If
Next

Waiting for debugger to connect...
Program started.
coordinates are 2550012.664 and 196252.944
coordinates are 2550012 and 196252
coordinates are 2550012.664 and 196252.944
coordinates are 2550012.664 and 196252.944
unable to parse point "P O I N T(123  456)"
coordinates are 2550012.664 and 196252.944

Why build a complex function in b4x when you can do this within the query? 99% or databases have spatial functions that can do this directly in sql without trying to parse the text. You’re just making things harder for yourself.

 

[emexes]

Why

Because that was the question:

How can i split the string

If you just want to extract the coordinates from your example string

I agree your answer was likely better, if the real question was how to extract the point coordinate values separately from the database.