Android Question Spatialite split string

sigster · Friday at 8:29 AM

Hi

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

POINT(2550012.664 196252.944)

B4X:

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))

emexes · Friday at 12:25 PM

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

B4X:

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:

B4X:

Dim TestPoints() As String = Array As String(  _...

tchart · Friday at 9:25 AM

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 · Friday at 10:58 AM

Something like this ?

How do i get x,y to string

B4X:

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 · Friday at 11:43 AM

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 · Friday at 12:25 PM

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

B4X:

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:

B4X:

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

Log output:

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 · Friday at 12:50 PM

Thanks for your replay

tchart · Friday at 7:35 PM

emexes said:

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

B4X:

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:

B4X:

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

Log output:

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 · Friday at 10:00 PM

tchart said:
Why

Because that was the question:

sigster said:
How can i split the string

emexes said:
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.

Android Question Spatialite split string

sigster

Active Member

emexes

tchart

Well-Known Member

sigster

Active Member

DonManfred

Expert

B4X Programming Video Tutorials

emexes

Expert

sigster

Active Member

tchart

Well-Known Member

emexes

Expert

Similar Threads

Android Question Spatialite split string

Active Member

Well-Known Member

Active Member

Expert

Expert

Active Member

Well-Known Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency